Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  jap1968 Jan 4 '13 at 16:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.