Hello all, My apologies if this is not the right mailing list to ask this question, but we are wondering about general performance tuning principles for our main db server.
We have a web app with a postgres backend. Most queries have subsecond response times through the web even with high usage. Every once in awhile someone will run either an ad-hoc query or some other long running db process. For some reason, it seems that a small number 3-4 of these jobs running in parallel absolutely floors our server. In monitoring the jobs, linux (Kernel 2.4) drops the long running jobs priority, but even so they seem to hog the system resources making subsequent requests for everyone else very slow. Our database at this point is almost entirely processor and memory bound because it isn't too large to fit most of the working data into memory yet. There is generally little disk activity when this occurs. These long running processes are almost always complex select statements, not generally inserts or updates. We continue to monitor and rework the bottlenecks, but what is a little scary to us is how easily the database becomes almost completely unresponsive with several large jobs running, especially since we have a large number of users. And it only takes one user trying to view a page with one of these selects clicking multiple times because it doesn't come back quickly to bring our system to it's knees for hours. We are looking to move to Kernel 2.6 and possibly a dedicated multiprocessor machine for postgres towards the end of this year. But, I am wondering if there is anything we can do now to increase the interactive performance while there are long running selects running as well. Are there ways to adjust the priority of backend processes, or things to tweak to maximize interactive throughput for the quick jobs while the long running ones run in the background? Or if worse comes to worse to actually kill long running processes without taking down the whole db as we have had to do on occasion. Our server is a modest 2.4Ghz P4 with mirrored UW SCSI drives and 1G of memory. The db on disk is around 800M and this machine also hosts our web app, so there is some contention for the processor. Does anyone have any suggestions or thoughts on things we could look at? Is a multiprocessor box the only answer, or are there other things we should be looking at hardware wise. Thank you for your time. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
