On Mon, Nov 17, 2008 at 8:42 AM, Howard Cole <howardnews@xxxxxxxxxxxxx> wrote: > Scott Marlowe wrote: >> >> Your entire disk io subsystem is a pair of hard drives. I'm assuming >> software RAID. > > Correct. >>> >>> The time that this query takes is not the issue, rather it is the impact >>> that it has on the server - effectively killing it for the 40 seconds due >>> to >>> the heavy disk access. >>> >> >> You either need to invest more into your drive subsystem so it can >> handle parallel load better, or you need to create a slave db with >> slony or londiste so that the ugly queries hit the slave. >> >> ____ > > I take your point Scott. But short of the hardware upgrade, is there a way > to effectively throttle this query? If I reduce maintenance_work_mem then > the database dump/restore is slower but there is less overall impact on the > server. Is there some equivalent parameter on the server to throttle general > queries? It would be unfortunate if all queries slowed down a bit, but a > better outcome than having the entire server hang for 40 seconds. The problem is most likely you're I/O bound. If one query is hitting a table it can pull in data (sequentially) at 40 to 80 megabytes per second. Since most of your queries are small, they don't run into each other a lot, so to speak. As soon as your big reporting query hits it's likely hitting the drives much longer and getting in the way of all the other queries. You could add more ram, that should help since the kernel could then fit much more of your data into ram and not be as dependent on your drive subsystem. Memory is cheap, even FBDIMMS are pretty inexpensive nowadays for 4 to 8 gigs of ram in a server. While you can nice the backend process with some kind of script it doesn't generally affect io priority. Some oses do allow process priority to dictate io priority, but I'm pretty sure linux doesn't. It still might help a little, but right now you need to either add enough ram for the kernel to cache the majority of your data set or get more drives. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
