Re: [PERFORM] large dataset with write vs read clients
* Greg Smith: > Given the size of your database, I'd advise you consider a migration > to a new version ASAP. 8.4 is a nice stable release at this point, > that's the one to consider moving to. It also offers asynchronous commits, which might be a good tradeoff here (especially if the data gathered is not used for billing purposes 8-). -- 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] large dataset with write vs read clients
I have a logical problem with asynchronous commit. The "commit" command should instruct the database to make the outcome of the transaction permanent. The application should wait to see whether the commit was successful or not. Asynchronous behavior in the commit statement breaks the ACID rules and should not be used in a RDBMS system. If you don't need ACID, you may not need RDBMS at all. You may try with MongoDB. MongoDB is web scale: http://www.youtube.com/watch?v=b2F-DItXtZs Florian Weimer wrote: * Greg Smith: Given the size of your database, I'd advise you consider a migration to a new version ASAP. 8.4 is a nice stable release at this point, that's the one to consider moving to. It also offers asynchronous commits, which might be a good tradeoff here (especially if the data gathered is not used for billing purposes 8-). -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow count(*) again...
I know that there haven been many discussions on the slowness of count(*) even when an index is involved because the visibility of the rows has to be checked. In the past I have seen many suggestions about using triggers and tables to keep track of counts and while this works fine in a situation where you know what the report is going to be ahead of time, this is simply not an option when an unknown WHERE clause is to be used (dynamically generated). I ran into a fine example of this when I was searching this mailing list, "Searching in 856,646 pages took 13.48202 seconds. Site search powered by PostgreSQL 8.3." Obviously at some point count(*) came into play here because the site made a list of pages (1 2 3 4 5 6 > next). I very commonly make a list of pages from search results, and the biggest time killer here is the count(*) portion, even worse yet, I sometimes have to hit the database with two SELECT statements, one with OFFSET and LIMIT to get the page of results I need and another to get the amount of total rows so I can estimate how many pages of results are available. The point I am driving at here is that since building a list of pages of results is such a common thing to do, there need to be some specific high speed ways to do this in one query. Maybe an estimate(*) that works like count but gives an answer from the index without checking visibility? I am sure that this would be good enough to make a page list, it is really no big deal if it errors on the positive side, maybe the list of pages has an extra page off the end. I can live with that. What I can't live with is taking 13 seconds to get a page of results from 850,000 rows in a table. -Neil- -- 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] Slow count(*) again...
On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel wrote: > I know that there haven been many discussions on the slowness of count(*) even > when an index is involved because the visibility of the rows has to be > checked. In the past I have seen many suggestions about using triggers and > tables to keep track of counts and while this works fine in a situation where > you know what the report is going to be ahead of time, this is simply not an > option when an unknown WHERE clause is to be used (dynamically generated). > I ran into a fine example of this when I was searching this mailing list, > "Searching in 856,646 pages took 13.48202 seconds. Site search powered by > PostgreSQL 8.3." Obviously at some point count(*) came into play here because > the site made a list of pages (1 2 3 4 5 6 > next). I very commonly make a > list of pages from search results, and the biggest time killer here is the > count(*) portion, even worse yet, I sometimes have to hit the database with > two SELECT statements, one with OFFSET and LIMIT to get the page of results I > need and another to get the amount of total rows so I can estimate how many > pages of results are available. The point I am driving at here is that since > building a list of pages of results is such a common thing to do, there need > to be some specific high speed ways to do this in one query. Maybe an > estimate(*) that works like count but gives an answer from the index without > checking visibility? I am sure that this would be good enough to make a page > list, it is really no big deal if it errors on the positive side, maybe the > list of pages has an extra page off the end. I can live with that. What I > can't live with is taking 13 seconds to get a page of results from 850,000 > rows in a table. 99% of the time in the situations you don't need an exact measure, and assuming analyze has run recently, select rel_tuples from pg_class for a given table is more than close enough. I'm sure wrapping that in a simple estimated_rows() function would be easy enough to do. -- 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] Slow count(*) again...
Neil Whelchel wrote: I know that there haven been many discussions on the slowness of count(*) even when an index is involved because the visibility of the rows has to be checked. In the past I have seen many suggestions about using triggers and tables to keep track of counts and while this works fine in a situation where you know what the report is going to be ahead of time, this is simply not an option when an unknown WHERE clause is to be used (dynamically generated). I ran into a fine example of this when I was searching this mailing list, "Searching in 856,646 pages took 13.48202 seconds. Site search powered by PostgreSQL 8.3." Obviously at some point count(*) came into play here because the site made a list of pages (1 2 3 4 5 6 > next). I very commonly make a list of pages from search results, and the biggest time killer here is the count(*) portion, even worse yet, I sometimes have to hit the database with two SELECT statements, one with OFFSET and LIMIT to get the page of results I need and another to get the amount of total rows so I can estimate how many pages of results are available. The point I am driving at here is that since building a list of pages of results is such a common thing to do, there need to be some specific high speed ways to do this in one query. Maybe an estimate(*) that works like count but gives an answer from the index without checking visibility? I am sure that this would be good enough to make a page list, it is really no big deal if it errors on the positive side, maybe the list of pages has an extra page off the end. I can live with that. What I can't live with is taking 13 seconds to get a page of results from 850,000 rows in a table. -Neil- Unfortunately, the problem is in the rather primitive way PostgreSQL does I/O. It didn't change in 9.0 so there is nothing you could gain by upgrading. If you execute strace -o /tmp/pg.out -e read sequential scan process> and inspect the file /tmp/pg.out when the query finishes, you will notice a gazillion of read requests, all of them 8192 bytes in size. That means that PostgreSQL is reading the table block by block, without any merging of the requests. You can alleviate the pain by using the OS tricks, like specifying the deadline I/O scheduler in the grub.conf and set prefetch on the FS block devices by using blockdev, but there is nothing special that can be done, short of rewriting the way PostgreSQL does I/O. There were rumors about the version 9.0 and asynchronous I/O, but that didn't materialize. That is really strange to me, because PostgreSQL tables are files or groups of files, if the table size exceeds 1GB. It wouldn't be very hard to try reading 1MB at a time and that would speed up the full table scan significantly. Problem with single block I/O is that there is a context switch for each request, the I/O scheduler has to work hard to merge requests appropriately and there is really no need for that, tables are files navigating through files is not a problem, even with much larger blocks. In another database, whose name I will not mention, there is a parameter db_file_multiblock_read_count which specifies how many blocks will be read by a single read when doing a full table scan. PostgreSQL is in dire need of something similar and it wouldn't even be that hard to implement. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.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] Slow count(*) again...
On 10/09/2010 06:54 PM, Mladen Gogala wrote: > In another database, whose name I will not mention, there is a parameter > db_file_multiblock_read_count which specifies how many blocks will be > read by a single read when doing a full table scan. PostgreSQL is in > dire need of something similar and it wouldn't even be that hard to > implement. You're correct in that it isn't particularly difficult to implement for sequential scans. But I have done some testing with aggressive read ahead, and although it is clearly a big win with a single client, the benefit was less clear as concurrency was increased. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Slow count(*) again...
Joe Conway wrote: On 10/09/2010 06:54 PM, Mladen Gogala wrote: In another database, whose name I will not mention, there is a parameter db_file_multiblock_read_count which specifies how many blocks will be read by a single read when doing a full table scan. PostgreSQL is in dire need of something similar and it wouldn't even be that hard to implement. You're correct in that it isn't particularly difficult to implement for sequential scans. But I have done some testing with aggressive read ahead, and although it is clearly a big win with a single client, the benefit was less clear as concurrency was increased. Joe Well, in my opinion that should be left to the DBA, the same as in the "other database". The mythical DBA, the creature that mighty Larry Ellison himself is on a crusade against, usually can figure out the right value for the database he or she's is in charge of. I humbly confess to being an Oracle DBA for more than 2 decades and now branching into Postgres because my employer is less than enthusiastic about Oracle, with the special accent on their pricing. Modern databases, Postgres included, are quite complex and companies need DBA personnel to help fine tune the applications. I know that good DBA personnel is quite expensive but without a competent DBA who knows the database software well enough, companies can and will suffer from blunders with performance, downtime, lost data and alike. In the world where almost every application is written for the web, performance, uptime and user experience are of the critical importance. The architects of Postgres database would be well advised to operate under the assumption that every production database has a competent DBA keeping an eye on the database. Every application has its own mix of sequential and index scans, you cannot possibly test all possible applications. Aggressive read-ahead or "multi-block reads" can be a performance problem and it will complicate the optimizer, because the optimizer now has a new variable to account for: the block size, potentially making seq_page_cost even cheaper and random_page_cost even more expensive, depending on the blocking. However, slow sequential scan is, in my humble opinion, the single biggest performance problem of the PostgreSQL databases and should be improved, the sooner, the better. You should, however, count on the DBA personnel to help with the tuning. We're the Tinkerbells of the database world. I am 6'4", 240 LBS, no wings. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.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] Slow count(*) again...
On Saturday 09 October 2010 18:47:34 Scott Marlowe wrote: > On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel wrote: > > I know that there haven been many discussions on the slowness of count(*) > > even when an index is involved because the visibility of the rows has to > > be checked. In the past I have seen many suggestions about using > > triggers and tables to keep track of counts and while this works fine in > > a situation where you know what the report is going to be ahead of time, > > this is simply not an option when an unknown WHERE clause is to be used > > (dynamically generated). I ran into a fine example of this when I was > > searching this mailing list, "Searching in 856,646 pages took 13.48202 > > seconds. Site search powered by PostgreSQL 8.3." Obviously at some point > > count(*) came into play here because the site made a list of pages (1 2 > > 3 4 5 6 > next). I very commonly make a list of pages from search > > results, and the biggest time killer here is the count(*) portion, even > > worse yet, I sometimes have to hit the database with two SELECT > > statements, one with OFFSET and LIMIT to get the page of results I need > > and another to get the amount of total rows so I can estimate how many > > pages of results are available. The point I am driving at here is that > > since building a list of pages of results is such a common thing to do, > > there need to be some specific high speed ways to do this in one query. > > Maybe an estimate(*) that works like count but gives an answer from the > > index without checking visibility? I am sure that this would be good > > enough to make a page list, it is really no big deal if it errors on the > > positive side, maybe the list of pages has an extra page off the end. I > > can live with that. What I can't live with is taking 13 seconds to get a > > page of results from 850,000 rows in a table. > > 99% of the time in the situations you don't need an exact measure, and > assuming analyze has run recently, select rel_tuples from pg_class for > a given table is more than close enough. I'm sure wrapping that in a > simple estimated_rows() function would be easy enough to do. This is a very good approach and it works very well when you are counting the entire table, but when you have no control over the WHERE clause, it doesn't help. IE: someone puts in a word to look for in a web form. >From my perspective, this issue is the biggest problem there is when using Postgres to create web pages, and it is so commonly used, I think that there should be a specific way to deal with it so that you don't have to run the same WHERE clause twice. IE: SELECT count(*) FROM WHERE ; to get the total amount of items to make page navigation links, then: SELECT FROM table WHERE LIMIT OFFSET <(page_no-1)*items_per_page>; to get the actual page contents. It's bad enough that count(*) is slow, then you have to do it all over again to get the results you need! I have not dug into this much yet, but would it be possible to return the amount of rows that a WHERE clause would actually return if the LIMIT and OFFSET were not applied. IE: When a normal query is executed, the server returns the number of rows aside from the actual row data. Would it be a big deal to modify this to allow it to return the amount of rows before the LIMIT and OFFSET is applied as well? This would sure cut down on time it takes to do the same WHERE clause twice... I have considered using a cursor to do this, however this requires a transfer of all of the rows to the client to get a total count, then setting the cursor to get the rows that you are interested in. Or is there a way around this that I am not aware of? -Neil- -- 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] Slow count(*) again...
On Sat, Oct 9, 2010 at 7:44 PM, Mladen Gogala wrote: > The architects of Postgres database would be well advised to operate under > the assumption that every production database has a competent DBA keeping an > eye on the database. > I'd actually go so far as to say that they have already made this assumption. The out of the box config needs modification for all but the most low-volume applications and postgres really benefits from having some attention paid to performance. Not only does tuning the db provide enormous gains, but it is often possible to dramatically improve query responsiveness by simply restructuring a query (assuming an aggregating query over a fairly large table with a few joins thrown in). My team does not have a competent DBA (though I've got 15+ years of experience developing on top of various dbs and certainly don't make overly naive assumptions about how things work) and the gains that we made, when I finally just sat down and read everything I could get my hands on about postgres and started reading this list, were really quite impressive. I intend to take some of the courses offered by some of the companies that are active on this list when my schedule allows in order to expand my knowledge even farther, as a DBA is a luxury we cannot really afford at the moment.
Re: [PERFORM] large dataset with write vs read clients
On 10/10/2010 5:35 AM, Mladen Gogala wrote: I have a logical problem with asynchronous commit. The "commit" command should instruct the database to make the outcome of the transaction permanent. The application should wait to see whether the commit was successful or not. Asynchronous behavior in the commit statement breaks the ACID rules and should not be used in a RDBMS system. If you don't need ACID, you may not need RDBMS at all. You may try with MongoDB. MongoDB is web scale: http://www.youtube.com/watch?v=b2F-DItXtZs That argument makes little sense to me. Because you can afford a clearly defined and bounded loosening of the durability guarantee provided by the database, such that you know and accept the possible loss of x seconds of work if your OS crashes or your UPS fails, this means you don't really need durability guarantees at all - let alone all that atomic commit silliness, transaction isolation, or the guarantee of a consistent on-disk state? Some of the other flavours of non-SQL databases, both those that've been around forever (PICK/UniVerse/etc, Berkeley DB, Cache, etc) and those that're new and fashionable Cassandra, CouchDB, etc, provide some ACID properties anyway. If you don't need/want an SQL interface to your database you don't have to throw out all that other database-y goodness if you haven't been drinking too much of the NoSQL kool-aid. There *are* situations in which it's necessary to switch to relying on distributed, eventually-consistent databases with non-traditional approaches to data management. It's awfully nice not to have to, though, and can force you to do a lot more wheel reinvention when it comes to querying, analysing and reporting on your data. FWIW, a common approach in this sort of situation has historically been - accepting that RDBMSs aren't great at continuous fast loading of individual records - to log the records in batches to a flat file, Berkeley DB, etc as a staging point. You periodically rotate that file out and bulk-load its contents into the RDBMS for analysis and reporting. This doesn't have to be every hour - every minute is usually pretty reasonable, and still gives your database a much easier time without forcing you to modify your app to batch inserts into transactions or anything like that. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.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] large dataset with write vs read clients
On 10/10/2010 2:43 AM, Craig Ringer wrote: Some of the other flavours of non-SQL databases, both those that've been around forever (PICK/UniVerse/etc, Berkeley DB, Cache, etc) and those that're new and fashionable Cassandra, CouchDB, etc, provide some ACID properties anyway. If you don't need/want an SQL interface to your database you don't have to throw out all that other database-y goodness if you haven't been drinking too much of the NoSQL kool-aid. This is a terrible misunderstanding. You haven't taken a look at that Youtube clip I sent you, have you? I am an Oracle DBA, first and foremost, disturbing the peace since 1989. I haven't been drinking the NoSQL kool-aid at all. I was simply being facetious. ACID rules are business rules and I am bitterly opposed to relaxing them. BTW, my favorite drink is Sam Adams Ale. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.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] Slow count(*) again...
On 10/10/2010 11:02 AM, Neil Whelchel wrote: On Saturday 09 October 2010 18:47:34 Scott Marlowe wrote: On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel wrote: I know that there haven been many discussions on the slowness of count(*) even when an index is involved because the visibility of the rows has to be checked. In the past I have seen many suggestions about using triggers and tables to keep track of counts and while this works fine in a situation where you know what the report is going to be ahead of time, this is simply not an option when an unknown WHERE clause is to be used (dynamically generated). I ran into a fine example of this when I was searching this mailing list, "Searching in 856,646 pages took 13.48202 seconds. Site search powered by PostgreSQL 8.3." Obviously at some point count(*) came into play here because the site made a list of pages (1 2 3 4 5 6> next). I very commonly make a list of pages from search results, and the biggest time killer here is the count(*) portion, even worse yet, I sometimes have to hit the database with two SELECT statements, one with OFFSET and LIMIT to get the page of results I need and another to get the amount of total rows so I can estimate how many pages of results are available. The point I am driving at here is that since building a list of pages of results is such a common thing to do, there need to be some specific high speed ways to do this in one query. Maybe an estimate(*) that works like count but gives an answer from the index without checking visibility? I am sure that this would be good enough to make a page list, it is really no big deal if it errors on the positive side, maybe the list of pages has an extra page off the end. I can live with that. What I can't live with is taking 13 seconds to get a page of results from 850,000 rows in a table. 99% of the time in the situations you don't need an exact measure, and assuming analyze has run recently, select rel_tuples from pg_class for a given table is more than close enough. I'm sure wrapping that in a simple estimated_rows() function would be easy enough to do. This is a very good approach and it works very well when you are counting the entire table, but when you have no control over the WHERE clause, it doesn't help. IE: someone puts in a word to look for in a web form. For that sort of thing, there isn't much that'll help you except visibility-aware indexes, covering indexes, etc if/when they're implemented. Even then, they'd only help when it was a simple index-driven query with no need to hit the table to recheck any test conditions, etc. I guess there could be *some* way to expose the query planner's cost estimates in a manner useful for result count estimation ... but given how coarse its stats are and how wildly out the estimates can be, I kind of doubt it. It's really intended for query planning decisions and more interested in orders of magnitude, "0, 1, or more than that" measures, etc, and seems to consider 30% here or there to be pretty insignificant most of the time. It's bad enough that count(*) is slow, then you have to do it all over again to get the results you need! I have not dug into this much yet, but would it be possible to return the amount of rows that a WHERE clause would actually return if the LIMIT and OFFSET were not applied. IE: When a normal query is executed, the server returns the number of rows aside from the actual row data. Would it be a big deal to modify this to allow it to return the amount of rows before the LIMIT and OFFSET is applied as well? It'd force the server to fully execute the query. Then again, it sounds like you're doing that anyway. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
