* Dimitri <dimitrik.fr@xxxxxxxxx> [090511 11:18]: > Folks, it's completely crazy, but here is what I found: > > - if HISTORY table is analyzed with target 1000 my SELECT response > time is jumping to 3ms, and the max throughput is limited to 6.000 TPS > (it's what happenned to 8.3.7) > > -if HISTORY table is analyzed with target 5 - my SELECT response time > is decreasing to 1.2ms (!) and then my max TPS level is ~12.000 ! > and CPU is used up to 95% even by 8.3.7 :-) and 8.4 performed better > just because I left its analyze target to default 100 value. > > Anyone may explain me why analyze target may have so huge negative > secondary effect?.. It's actually pretty straight forward. The PostgreSQL query planner is a "smart planner". It takes into consideration all the statistics available on the columns/tables, expected outputs based on inputs, etc, to choose what it thinks will be the best plan. The more data you have in statistics (the larger statistics target you have), the more CPU time and longer it's going to take to "plan" your queries. The tradeoff is hopefully better plans. But, in your scenario, where you are hitting the database with the absolute worst possible way to use PostgreSQL, with small, repeated, simple queries, you're not getting the advantage of "better" plans. In your case, you're throwing absolutely simple queries at PG as fast as you can, and for each query, PostgreSQL has to: 1) Parse the given "query string" 2) Given the statistics available, plan the query and pick the best one 3) Actually run the query. Part 2 is going to dominate the CPU time in your tests, more so the more statistics it has to evaluate, and unless the data has to come from the disks (i.e. not in shared buffers or cache) is thus going to dominate the time before you get your results. More statistics means more time needed to do the planning/picking of the query. If you were to use prepared statements, the cost of #1 and #2 is done once, and then every time you throw a new execution of the query to PostgreSQL, you get to just do #3, the easy quick part, especially for small simple queries where all the data is in shared buffers or the cache. a. -- Aidan Van Dyk Create like a god, aidan@xxxxxxxxxxx command like a king, http://www.highrise.ca/ work like a slave.
Attachment:
signature.asc
Description: Digital signature
