[PERFORM] Limit I/O bandwidth of a certain backend
Is there a way to limit I/O bandwidth/CPU usage of a certain backend? It seems that ionice/renice makes no sense because of bgwriter/WAL writer processes are not a part of a backend. I have a periodic query (every hour) that make huge I/O load and should run in background. When this query runs all other fast queries slow down dramatically. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [HACKERS] high shared buffer and swap
An octocore server with 32GB of ram, running postgresql 8.3.6 Running only postgresql, slony-I and pgbouncer. Just for testing purpose, i tried a setting with 26GB of shared_buffer. I quickly noticed that the performances wasn't very good and the server started to swap slowly but surely. (but still up to 2000query/second as reported by pgfouine) It used all the 2GB of swap. I removed the server from production, added 10GB of swap and left it for the weekend with only slony and postgresql up to keep it in sync with the master database. This morning i found that the whole 12GB of swap were used : Hm, do you really need swap with 32Gb of RAM ? One could argue yes but swap is useful to avoid out of memory errors. But if a loaded server starts to swap a lot, it is as good as dead anyway... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [HACKERS] high shared buffer and swap
On Tue, May 5, 2009 at 11:15 AM, PFC li...@peufeu.com wrote: An octocore server with 32GB of ram, running postgresql 8.3.6 Running only postgresql, slony-I and pgbouncer. Just for testing purpose, i tried a setting with 26GB of shared_buffer. I quickly noticed that the performances wasn't very good and the server started to swap slowly but surely. (but still up to 2000query/second as reported by pgfouine) It used all the 2GB of swap. I removed the server from production, added 10GB of swap and left it for the weekend with only slony and postgresql up to keep it in sync with the master database. This morning i found that the whole 12GB of swap were used : Hm, do you really need swap with 32Gb of RAM ? One could argue yes but swap is useful to avoid out of memory errors. But if a loaded server starts to swap a lot, it is as good as dead anyway... Not really, but we have it. I tried with swappinness set to 0 and ... it swaps ! I'm back to 4GB of shared_buffer :) I'll try various setting, maybe 16GB, etc ... But my goal was to avoid OS filesystem cache and usage of shared_buffer instead : FAIL. -- F4FQM Kerunix Flan Laurent Laborde -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [HACKERS] high shared buffer and swap
On Tue, 5 May 2009, Laurent Laborde wrote: I tried with swappinness set to 0 and ... it swaps ! While I wouldn't presume to try and teach you to suck eggs, once you set swappiness to zero the system will take a while to settle down. The swappiness setting will stop it swapping out, but if there is stuff out in swap, then it won't force it to swap it in early. So the machine will still thrash when it tries to access something that is out on swap. Easy way to solve this is to swapoff the various swap partitions, which will force it all into memory. You can swapon them again afterwards. Matthew -- Surely the value of C++ is zero, but C's value is now 1? -- map36, commenting on the No, C++ isn't equal to D. 'C' is undeclared [...] C++ should really be called 1 response to C++ -- shouldn't it be called D? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Limit I/O bandwidth of a certain backend
On Tue, May 5, 2009 at 2:31 AM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: Is there a way to limit I/O bandwidth/CPU usage of a certain backend? It seems that ionice/renice makes no sense because of bgwriter/WAL writer processes are not a part of a backend. I have a periodic query (every hour) that make huge I/O load and should run in background. When this query runs all other fast queries slow down dramatically. Could you use something like slony to replicate the needed data to a secondary database and run the query there? Bryan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Limit I/O bandwidth of a certain backend
On Tue, 5 May 2009, Vlad Arkhipov wrote: Is there a way to limit I/O bandwidth/CPU usage of a certain backend? It seems that ionice/renice makes no sense because of bgwriter/WAL writer processes are not a part of a backend. The background writer and WAL writer are pretty low users of CPU and I/O relative to how much an expensive query uses in most cases. You should try out one of the nice approaches and see if it works for you rather than presuming the background processes will thwart you here. There isn't anything better available in the database yet, so it's the closest things to a good solution available right now anyway. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
