Thread: hardware - generic
I'm currently running a postgresql database on the following hardware (I know it's a bit small) Via Epie 533MHz cpu and mobo. 512MB PC 100 RAM. 5400 RPM single EIDE drive. And it's showing some signs of not being able to keep up with the demands of a database. Probably no surprise there. But, I'm not sure what kind of hardware I should focus on. What's the most important thing to get first, second, third? I don't think I have the $$ to get a 8 drive 15K RPM SCSI array with 32GB RAM and quad CPU's - though it might be nice in the winter as a space heater. Most of what I'm seeing right now is actually CPU bound on this machine and another (2GHz AMD) so I'm wondering if I'm better off getting multiple CPU's over one really huge one (or a multi-core). Stuff like that.... What's your experience?
> But, I'm not sure what kind of hardware I should focus on. > What's the most important thing to get first, second, third? > > I don't think I have the $$ to get a 8 drive 15K RPM SCSI array with 32GB RAM > and quad CPU's - though it might be nice in the winter as a space heater. > > Most of what I'm seeing right now is actually CPU bound on this machine and > another (2GHz AMD) so I'm wondering if I'm better off getting multiple CPU's > over one really huge one (or a multi-core). > > Stuff like that.... What's your experience? I've read the first link but not the second link. Your hardware could be undersized, but it is hard to say without knowing the actual number of concurrent connections. Also, what exactly are you experiencing when you say that your server is not able to keep up? What does a vmstat 1 10 show while you are experiencing slow preformance. Do you have sufficently aggressive autovaccum setting? Also, have you adjusted any of your postgresql.conf parameters? http://www.powerpostgresql.com/PerfList/ http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/0.html Regards, Richard Broersma Jr.
> I've read the first link but not the second link. Your hardware could be undersized, but it is > hard to say without knowing the actual number of concurrent connections. Also, what exactly are > you experiencing when you say that your server is not able to keep up? What does a vmstat 1 10 > show while you are experiencing slow preformance. Do you have sufficently aggressive autovaccum > setting? Also, have you adjusted any of your postgresql.conf parameters? I greatly increased the shmmax to approx 448MB (machine has 521K installed) max_connections = 30 I've changed the shared_buffers to 32768. work_mem to 2048. maintenance_work_mem to 32768 max_fsm_page to 40_000 and the last one, that I'm not too sure about, is effective_cache_size to 52_000 (which is the max I can set anything at) So, those are all the settings. It's generally running with about 16K of swap in use, but I can't say what it is that is swapped out. But top typically shows these processes are taking up less than 55% of the available RAM for each process and typically has the CPU load up around 2.10. vmstat is pretty quiet. Nothing out of the ordinary that would say it's running poorly. si/so are 0 99% of the time. So, is this good? bad? dunno?
> I greatly increased the shmmax to approx 448MB (machine has 521K installed) > max_connections = 30 > I've changed the shared_buffers to 32768. > work_mem to 2048. > maintenance_work_mem to 32768 > max_fsm_page to 40_000 These parameter setting seem a bit aggressive due to the limited amount of ram that you have. According to: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf Having shared buffers set too hi can actually hurt preformance. Here is the advice for setting an appropriate value for shared_buffers: There is one way to decide what is best for you. Set a high value of this parameter and run the database for typical usage. Watch usage of shared memory using ipcs or similar tools. A recommended figure would be between 1.2 to 2 times peak shared memory usage > It's generally running with about 16K of swap in use, but I can't say what it is > that is swapped out. I would reduce your memory setting enough to ensure that you are not using swap. Swapping is a sure fire way to kill preformance. On a smaller bugzilla server that I set up at work use a throw-away computer, I reduced query times to 50%-70% by reducing memory usage until swap was no longer needed. This was a 200Mhz PII with 128Mb RAM 2Gb 5Krpm Harddrive. Running both apache and postgresql. I reduced apaches connections to 5 and postgresqls connections to 20 and turned down the shared buffers to about 100. > vmstat is pretty quiet. Nothing out of the ordinary that would say it's running > poorly. si/so are 0 99% of the time. Well if disk writes are not hurting you, I guess the next step would be to determine which queries are running to slow. Speed improvements could be gained by restructuring sql syntax to the planner chose better plans or you may need to add a few indexes. To get started, you should turn on duration_logging in your postgresql conf and turn statement logging. After a day or two you can scan your log files for quieries that you feel are running too slow. The next step would be to run an "EXPLAIN ANALYZE" on these exact queries to see the query plan and estimates used in your query. You can post the explain analyze result here, or the preformance list to get feedback to see what can be done to reduce query time. I hope this helps. Also, be sure to carefully ready the above article as it will really aid you in configuring your postgresql.conf file with reasonable settings. Regards, Richard Broersma Jr.