10

I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.

I am looking for help about the proper memory values for the configuration file.

Currently, I have a modest dedicated server (2GB of RAM) running Apache, MySQL and PostgreSQL under Linux. The main MySQL database are about 3 times the size of the main one in PostgreSQL, and the same could be said for the data traffic.

I still have the default configuration file for PostgreSQL, having the parameter shared_buffers = 32MB. I assume that I should increase this value, but I do not want to penalize the other databases. I have read something about dedicating a 25% of physical available memory to PostgreSQL, but I am not sure about that.

The new database contains seven tables, having the biggest one about 3 MM rows. The machine has currently a low traffic (serving about 10000 ~ 20000 selects a day), but these values are expected to grow.

I was thinking about increasing shared_buffers to 256MB, but I would like to hear some opinions from users having more experience with this RDBMS.

Improve this question

1 Answer 1

Reset to default
4

I would avoid placing MySQL and PostgreSQL on the same server. They compete for the same resources. If you can, port everything to one RDBMS. My obvious choice would be PostgreSQL.

Then you can set shared_buffers to something like 500 MB and effective_cache_size to something like 1.5 GB. Be sure to read hints in the manual.

But I would also recommend to add more physical RAM. 2 GB is not much. Hardly enough for good performance with millions of rows. A few more GB of RAM shouldn't cost much.

If you have to stick with your setup 250 MB for Postgres seems reasonable. If MySQL has three times as much traffic, less might be better overall. Like 128 MB.

Basics for performance optimization in the Postgres Wiki.

Improve this answer
1
  • The idea is to have finally everything migrated to PostgreSQL, so MySQL won't be running on this server in the future. On the other side, this is a project to be done at cost = zero, so it is difficult (impossible) to have a second server or to obtain additional RAM. I have increased shared_buffers to 128 MB and everything seems to work properly. Commented Jan 4, 2013 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.