Re: [PERFORM] Performance
All, > The easiest place to start is by re-using the work already done by the > TPC for benchmarking commercial databases. There are ports of the TPC > workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 > tests; Also EAStress, which I think the project still has a license for. The drawback to these is that they're quite difficult and time-consuming to run, making them unsuitable for doing, say, incremental tuning tests which need to run 100 iterations. At least, now that we don't have access to the OSDL or Sun labs anymore. On the other hand, Greg has made the first steps in a benchmark constructor kit by making it possible for pgBench to run arbitrary workloads. Someone could build on Greg's foundation by: a) building a more complex database model with random data generators, and b) designing a wide series of queries designed to test specific performance problems, i.e, "large object reads", "complex nested subqueries", "mass bulk correllated updates" c) finally creating scripts which generate benchmarks by choosing a database size and a "mix" of the query menu This would give us kit which would be capable of testing performance regressions and improvements for PostgreSQL. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance
Dne 27.4.2011 23:55, Greg Smith napsal(a): > The easiest place to start is by re-using the work already done by the > TPC for benchmarking commercial databases. There are ports of the TPC > workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests; > see http://wiki.postgresql.org/wiki/Category:Benchmarking for initial > information on those (the page on TPC-H is quite relevant too). I'd > like to see all three of those DBT tests running regularly, as well as > two tests it's possible to simulate with pgbench or sysbench: an > in-cache read-only test, and a write as fast as possible test. That's a natural first step, I guess. > The main problem with re-using posts from this list for workload testing > is getting an appropriately sized data set for them that stays > relevant. The nature of this sort of benchmark always includes some > notion of the size of the database, and you get different results based > on how large things are relative to RAM and the database parameters. > That said, some sort of systematic collection of "hard queries" would > also be a very useful project for someone to take on. Yes, I'm aware of that. The examples posted to the lists usually lack the data, but I guess we could get it at least from some of the posters (anonymized etc.). And some of the examples are rather simple so it's possible to generate as much data as you want using a PL/pgSQL or so. Anyway I hesitate to call those examples 'workloads' - it's usually just one query, sometimes two. But it's still a useful test IMHO. I was thinking about several VMs, each with a different configuration (amount of RAM, CPU, ...). The benchmarks might be a bunch of very simple scripts I guess, each one taking care of preparing the data, running the test, uploading the results somewhere. And I guess it'd be useful to make this awailable for download, so that everyone can run the tests locally ... A bit naive question - where to run this? I know there's a build farm but I guess this it's mostly for building and not for such benchmarks. > People show up regularly who want to play with the optimizer in some > way. It's still possible to do that by targeting specific queries you > want to accelerate, where it's obvious (or, more likely, hard but still > straightforward) how to do better. But I don't think any of these > proposed exercises adjusting the caching model or default optimizer > parameters in the database is going anywhere without some sort of > benchmarking framework for evaluating the results. And the TPC tests > are a reasonable place to start. They're a good mixed set of queries, > and improving results on those does turn into a real commercial benefit > to PostgreSQL in the future too. 100% true. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
On 04/27/2011 11:33 PM, HSIEN-WEN CHU wrote: When database files are on a VxFS filesystem, performance can be significantly improved by setting the VX_CONCURRENT cache advisory on the file according to vxfs document, That won't improve performance, and it's not safe either. VX_CONCURRENT switches the filesystem to use direct I/O. That's usually slower for PostgreSQL. And it introduces some requirements for both block alignment and the application avoiding overlapping writes. PostgreSQL doesn't do either, so I wouldn't expect it to be compatible with VX_CONCURRENT. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan
On Wed, Apr 27, 2011 at 5:19 PM, Sok Ann Yap wrote: > > I understand the need to tune PostgreSQL properly for my use case. > What I am curious about is, for the data set I have, under what > circumstances (hardware/workload/cache status/etc) would a sequential > scan really be faster than an index scan for that particular query? The sequential scan on contacts can be terminated as soon as the first matching row is found. If each block of the contacts table contains one example of each salutation, then the inner sequential scan will always be very short, and faster than an index scan. I can engineer this to be the case by populating the table like this: insert into contacts select (generate_series%44+1)::int from generate_series (1,100); Here I get the seq scan being 2.6ms while the index scan is 5.6ms. Predicting how far the inner scan needs to go would be quite difficult, and I don't know how the system will do it. However, when I create and populate simple tables based on your description, I get the index scan being the lower estimated cost. So the tables I built are not sufficient to study the matter in detail. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Order of tables
On 28.04.2011 12:20, Rishabh Kumar Jain wrote: How the tables must be ordered in the list of tables in from statement? There is no difference in performance, if that's what you mean. (If not, then pgsql-novice or pgsql-sql mailing list would've be more appropriate) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Order of tables
On Thu, Apr 28, 2011 at 11:20 AM, Rishabh Kumar Jain wrote: > How the tables must be ordered in the list of tables in from statement? > To achieve what? Generally there is no requirement for a particular ordering of relation names in SQL. Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan
On Wed, Apr 27, 2011 at 5:19 PM, Sok Ann Yap wrote: > On Thu, Apr 28, 2011 at 7:23 AM, Kevin Grittner > > > I understand the need to tune PostgreSQL properly for my use case. > What I am curious about is, for the data set I have, under what > circumstances (hardware/workload/cache status/etc) would a sequential > scan really be faster than an index scan for that particular query? > Possibly none on your hardware - if the index is likely to be in memory along with the actual table rows. In which case, the cost for index scan (random page cost) should be made much closer to the cost for sequential access. It looks like the planner must use the same strategy on each iteration of the loop - it can't do index scan for some values and sequential scan for others, so it must be computing the cost as sequential_cost * (number of entries(44)) versus random_cost * (number of entries). If random page cost is unreasonably high, it's not hard to see how it could wind up looking more expensive to the planner, causing it to choose the sequential scan for each loop iteration. If it were able to change strategy on each iteration, it would be able to accurately assess cost for each iteration and choose the correct strategy for that value. As soon as you set the costs closer to actual cost for your system, postgres does make the correct choice. If there weren't enough memory that postgres could be 'sure' that the index would remain in cache at least for the duration of all 44 iterations due to high workload, it is easy to see how the index scan might become significantly more expensive than the sequential scan, since the index scan must also load the referenced page from the table - postgres cannot get values directly from the index. > To simulate a scenario when nothing is cached, I stopped PostgreSQL, > dropped all system cache (sync; echo 3 > /proc/sys/vm/drop_caches), > restarted PostgreSQL, and ran the query. A sequential scan run took > 13.70 seconds, while an index scan run took 0.34 seconds, which is > still 40 times faster. > > Also, I tried increasing effective_cache_size from 512MB to 3GB (the > database size is 2+GB), and it still favor sequential scan. The > estimated costs did not change at all. > Greg Smith had this to say in a another thread on this same subject: effective_cache_size probably doesn't do as much as you suspect. It is used for one of the computations for whether an index is small enough that it can likely be read into memory efficiently. It has no impact on caching decisions outside of that. This is why the cost for random page access must be fairly accurate.Even if the index is in memory, *it still needs to access the page of data in the table referenced by the index*, which is why the cost of random access must be accurate. That cost is a factor of both the performance of your storage infrastructure and the cache hit rate and can't really be computed by the database on the fly. You seem to be looking at the data which exposes the fact that random page access is fast and wondering why postgres isn't doing the right thing when postgres isn't doing the right thing precisely because it doesn't know that random page access is fast. Since you don't have particularly fast storage infrastructure, this is likely a function of cache hit rate, so you must factor in eventual load on the db when setting this value. While it may be fast in a lightly loaded test environment, those random page accesses will get much more expensive when competing with other concurrent disk access. There's another thread currently active on this list (it started on April 12) with subject "Performance" which contains this explanation of what is going on and why you need to tune these parameters independently of effective_cache_size: When the planner decides what execution plan to use, it computes a 'virtual cost' for different plans and then chooses the cheapest one. Decreasing 'random_page_cost' decreases the expected cost of plans involving index scans, so that at a certain point it seems cheaper than a plan using sequential scans etc. You can see this when using EXPLAIN - do it with the original cost values, then change the values (for that session only) and do the EXPLAIN only. You'll see how the execution plan suddenly changes and starts to use index scans. The problem with random I/O is that it's usually much more expensive than sequential I/O as the drives need to seek etc. The only case when random I/O is just as cheap as sequential I/O is when all the data is cached in memory, because within RAM there's no difference between random and sequential access (right, that's why it's called Random Access Memory). So in the previous post setting both random_page_cost and seq_page_cost to the same value makes sense, because when the whole database fits into the memory, there's no difference and index scans are favorable. In this case (the database is much bigger than the available RAM) this no longer
[PERFORM] Order of tables
How the tables must be ordered in the list of tables in from statement?
Re: [PERFORM] Performance
Just want to share the DBT(2&5) thing http://archives.postgresql.org/pgsql-performance/2011-04/msg00145.php http://sourceforge.net/mailarchive/forum.php?forum_name=osdldbt-general&max_rows=25&style=nested&viewmonth=201104 On Wed, Apr 27, 2011 at 11:55 PM, Greg Smith wrote: > Tomas Vondra wrote: > >> Hmmm, just wondering - what would be needed to build such 'workload >> library'? Building it from scratch is not feasible IMHO, but I guess >> people could provide their own scripts (as simple as 'set up a a bunch >> of tables, fill it with data, run some queries') and there's a pile of >> such examples in the pgsql-performance list. >> >> > > The easiest place to start is by re-using the work already done by the TPC > for benchmarking commercial databases. There are ports of the TPC workloads > to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests; see > http://wiki.postgresql.org/wiki/Category:Benchmarking for initial > information on those (the page on TPC-H is quite relevant too). I'd like to > see all three of those DBT tests running regularly, as well as two tests > it's possible to simulate with pgbench or sysbench: an in-cache read-only > test, and a write as fast as possible test. > > The main problem with re-using posts from this list for workload testing is > getting an appropriately sized data set for them that stays relevant. The > nature of this sort of benchmark always includes some notion of the size of > the database, and you get different results based on how large things are > relative to RAM and the database parameters. That said, some sort of > systematic collection of "hard queries" would also be a very useful project > for someone to take on. > > People show up regularly who want to play with the optimizer in some way. > It's still possible to do that by targeting specific queries you want to > accelerate, where it's obvious (or, more likely, hard but still > straightforward) how to do better. But I don't think any of these proposed > exercises adjusting the caching model or default optimizer parameters in the > database is going anywhere without some sort of benchmarking framework for > evaluating the results. And the TPC tests are a reasonable place to start. > They're a good mixed set of queries, and improving results on those does > turn into a real commercial benefit to PostgreSQL in the future too. > > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
