I decided to take advantage of my ongoing access to a benchmarking server to see how I could get on with a query with an especially large sort. Now, that box has 16GB of ram, and some people have that much memory in their laptops these days, so I was somewhat limited in how far I could push things.
I eventually decided upon much the same benchmark as I'd made in my previous e-mail (the query "SELECT * FROM pgbench_accounts ORDER BY bid;", but primed with random data, while being sure to subsequently vacuum). I stress that I have not made any attempt to artificially remove client overhead. I have, however, used a faster disk (caches were not warmed in a seperate run of pgbench or anything like that for either of my two most recent benchmarks, so there may have been and indeed may still be some small bias there), in addition to connecting pgbench with the -h option. Apparently a known problem with Linux kernels using the Completely Fair Scheduler introduced in 2.6.23 is that it does not schedule the pgbench program very well when it's connecting to the database usingUnix-domain sockets, as I have been until now. I'm not sure that this had all that much potential to spoil my results, but didn't want to take the chance . Anyway, the results of running this latest benchmark, with 20 successive runs of each large query, were: Patch: pghost: localhost pgport: nclients: 1 nxacts: 20 dbName: pgbench transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 1 number of threads: 1 number of transactions per client: 20 number of transactions actually processed: 20/20 tps = 0.030924 (including connections establishing) tps = 0.030924 (excluding connections establishing) statement latencies in milliseconds: 31163.957400 SELECT * FROM pgbench_accounts ORDER BY bid; Master: pghost: localhost pgport: nclients: 1 nxacts: 20 dbName: pgbench pghost: localhost pgport: nclients: 1 nxacts: 20 dbName: pgbench transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 1 number of threads: 1 number of transactions per client: 20 number of transactions actually processed: 20/20 tps = 0.026769 (including connections establishing) tps = 0.026769 (excluding connections establishing) statement latencies in milliseconds: 36179.508750 SELECT * FROM pgbench_accounts ORDER BY bid; That's a larger proportional improvement than reported on Thursday, and at significantly greater scale. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers