We have a Php application running on Postgres version 8.2 We recently purchased a new web server which should be at least three times as fast as our old server, but we are seeing virtually no performance improvement. In some cases the new server is slower than the old. Old server: =============== Dual AMD Athlon(tm) MP 2800+ SATA (4 sata disks, RAID 1+0) 4GB memory New server: =============== Quad Xeon L5420 Mtron Pro 7000 solid state disk 4GB memory We are looking for a Postgres database tuning expert to analyze our system and application to determine where the bottlenecks are and eliminate those bottlenecks so that the server performs to its maximum capability. The performance improvement must be achieved in Postgres 8.2, not by installing a later version of Postgres. If you wish to be considered for this project, you must answer the following 4 questions along with providing your bid: 1. How many months or years experience do you have working with tuning Postgres database systems? 2. If a database server performs badly, what are the top things you would check to diagnose the problem? List at least 6, in order of importance (most important first). 3. If running a db query from php takes 40 seconds on a fast Intel Quad Core CPU, how would you go about determining where the bottleneck is? 4. How is tuning Postgres different than other databases you have worked on?
STEP 1. Identify HARDWARE bottlenecks Here's what we want you to do first. Install or create a tool that enables us to exactly document the usage level of hardware resources to complete queries. For example, we will look at a query that takes 90 seconds. Your tool or method should give us a report that says, of the total time required to finish the query, 60 seconds (67%) was I/O time 20 seconds (22%) was disk swapping time 10 seconds (11%) was CPU time In your report should tell us that: 30% of the data needed to complete the query was in RAM, 70% needed to be retrieved from the disk Your method or tool must be able to exactly nail down those numbers. We don't actually know if getting these exact numbers are possible, so don't tell us you can do it unless you have done something like this before and know exactly how you will do it. We will provide a list of 10 slow queries. You should provide this breakdown on each of these 10 queries. We can then look at these numbers and determine what hardware to upgrade to reduce bottlenecks. ============ STEP 2. After we know what HARDWARE resources are slowing down queries, we want you to optimize the settings and conf files for things like: Postgres PHP and maybe even Apache, bios, and disks/IO to improve performance and reduce bottlenecks with our existing hardware. You will provide a report describing: a) which files you changes b)the list of changes c) the performance improvement as a result of your changes. i.e. you will re-run the test you did in step 1 to document the improvement in performance. ============ We DO NOT want someone looking at our application, our database design, or our queries and telling us that query X or query Y is inefficient or telling us we should redesign our database structure. It would be useful to do that at some point, but that is not the task we are asking for. We have thousands of queries and SQL statements, it would take a year to optimize all of them. And we are NOT going to make fundamental changes to the design of our database. We have a case where ALL our queries are running almost the same speed or slower on faster hardware. Your approach to solve this problem must focus with Step 1 and Step 2 above.
Debian Etch, Apache, Php4, Postgres 8.2