Re: [PERFORM] Query about index usage
Jayadevan M wrote: It is mentioned that table data blocks have data about tuple visibility and hence table scans are always necessary. So how does PostgreSQL reduce the number of blocks to be read by using indexes? To be useful, a query utilizing an index must be selective: it must only return a fraction of the possible rows in the table. Scanning the index will produce a list of blocks that contain the potentially visible data, then only those data blocks will be retrieved and tested for visibility. Let's say you have a table that's 100 pages (pages are 8KB) and an index that's 50 pages against it. You run a query that only selects 5% of the rows in the table, from a continuous section. Very rough estimate, it will look at 5% * 50 = 3 index pages. Those will point to a matching set of 5% * 100 = 5 data pages. Now you've just found the right subset of the data by only retrieving 8 random pages of data instead of 100. With random_page_cost=4.0, that would give this plan a cost of around 32, while the sequential scan one would cost 100 * 1.0 (sequential accesses) for a cost of around 100 (Both of them would also have some smaller row processing cost added in there too). It's actually a bit more complicated than that--the way indexes are built means you can't just linearly estimate their usage, and scans of non-contiguous sections are harder to model simply--but that should give you an idea. Only when using the index significantly narrows the number of data pages expected will it be an improvement over ignoring the index and just scanning the whole table. If the expected use of the index was only 20% selective for another query, you'd be getting 20% * 50 = 10 index pages, 20% * 100 = 20 data pages, for a potential total of 30 random page lookups. That could end up costing 30 * 4.0 = 120, higher than the sequential scan.Usually the breakpoint for how much of a table has to be scanned before just scanning the whole thing sequentially is considered cheaper happens near 20% of it, and you can shift it around by adjusting random_page_cost. Make it lower, and you can end up preferring index scans even for 30 or 40% of a table. Do index data get updated as and when data is committed and made 'visible' or is it that index data get updated as soon as data is changed, before commit is issued and rollback of transaction results in a rollback of the index data Index changes happen when the data goes into the table, including situations where it might not be committed. The index change doesn't ever get deferred to commit time, like you can things like foreign key checks. When a transaction is rolled back, the aborted row eventually gets marked as dead by vacuum, at which point any index records pointing to it can also be cleaned up. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com 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] Query about index usage
Thank you for the detailed explanation. Regards, Jayadevan DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect. -- 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] raid10 write performance
On 06/22/10 16:40, Greg Smith wrote: Grzegorz Jaśkiewicz wrote: raid: serveRAID M5014 SAS/SATA controller Do the performant servers have a different RAID card? This one has terrible performance, and could alone be the source of your issue. The ServeRAID cards are slow in general, and certainly slow running RAID10. What are some good RAID10 cards nowadays? On the other hand, RAID10 is simple enough that soft-RAID implementations should be more than adequate - any ideas why a dedicated card has it slow? -- 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] Low perfomance SUM and Group by large databse
Craig, Russel, I appreciate your help. Thanks. 2010/6/22 Russell Smith mr-r...@pws.com.au On 22/06/10 00:42, Sergio Charpinel Jr. wrote: Hi, [snip] = explain analyze SELECT ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto,SUM(bytes),SUM(packets),SUM(flows) FROM acct_2010_25 WHERE stamp_inserted='2010-06-20 10:10' AND stamp_inserted'2010-06-21 10:10' GROUP BY ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto order by SUM(bytes) desc LIMIT 50 OFFSET 0; QUERY PLAN -- Limit (cost=3998662.81..3998662.94 rows=50 width=50) (actual time=276981.107..276981.133 rows=50 loops=1) - Sort (cost=3998662.81..4001046.07 rows=953305 width=50) (actual time=276981.105..276981.107 rows=50 loops=1) Sort Key: sum(bytes) - GroupAggregate (cost=3499863.27..3754872.33 rows=953305 width=50) (actual time=165468.257..182677.580 rows=8182616 loops=1) - Sort (cost=3499863.27..3523695.89 rows=9533049 width=50) (actual time=165468.022..168908.828 rows=9494165 loops=1) Sort Key: ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto You are having to sort and aggregate a large number of rows before you can get the top 50. That's 9 million rows in this case, width 50 = 400MB+ sort. That's going to be slow as you are going to have to sort it on disk unless you bump up sort mem to 500Mb (bad idea). So unless you have really fast storage for temporary tables it's going to take a while. About 2.5 minutes you are experiencing at the moment is probably not too bad. I'm sure improvements have been made in the area since 8.1 and if you are able to upgrade to 8.4 which is also offered by Centos5 now, you might get benefit there. I can't remember the specific benefits, but I believe sorting speed has improved, your explain analyze will also give you more information about what's going on with disk/memory sorting. - Seq Scan on acct_2010_25 (cost=0.00..352648.10 rows=9533049 width=50) (actual time=0.038..50860.391 rows=9494165 loops=1) Filter: ((stamp_inserted = '2010-06-20 10:10:00'::timestamp without time zone) AND (stamp_inserted '2010-06-21 10:10:00'::timestamp without time zone)) Total runtime: 278791.661 ms (9 registros) Another one just summing bytes (still low): = explain analyze SELECT ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto,SUM(bytes) FROM acct_2010_25 WHERE stamp_inserted='2010-06-20 10:10' AND stamp_inserted'2010-06-21 10:10' GROUP BY ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto LIMIT 50 OFFSET 0; QUERY PLAN Limit (cost=3395202.50..3395213.12 rows=50 width=42) (actual time=106261.359..106261.451 rows=50 loops=1) - GroupAggregate (cost=3395202.50..3602225.48 rows=974226 width=42) (actual time=106261.357..106261.435 rows=50 loops=1) - Sort (cost=3395202.50..3419558.14 rows=9742258 width=42) (actual time=106261.107..106261.169 rows=176 loops=1) Sort Key: ip_src, port_src, ip_dst, port_dst, tcp_flags, ip_proto - Seq Scan on acct_2010_25 (cost=0.00..367529.72 rows=9742258 width=42) (actual time=0.073..8058.598 rows=9494165 loops=1) Filter: ((stamp_inserted = '2010-06-20 10:10:00'::timestamp without time zone) AND (stamp_inserted '2010-06-21 10:10:00'::timestamp without time zone)) Total runtime: 109911.882 ms (7 registros) The server has 2 Intel(R) Xeon(R) CPU E5430 @ 2.66GHz and 16GB RAM. I'm using PostgreSQL 8.1.18 default config from Centos 5.5 (just increased checkpoint_segments to 50). Checkpoint segments won't help you as the number of segments is about writing to the database and how fast that can happen. What can I change to increase performance? Increasing sort-memory (work_mem) will give you speed benefits even though you are going to disk. I don't know how much spare memory you have, but trying other values between 8MB and 128MB may be useful just for the specific query runs. If you can afford 512Mb for each of the two sorts, go for that, but it's dangerous as mentioned due to the risk of using more RAM than you have. work_mem allocates that amount of memory per sort. If you are running these queries all the time, a summary table the produces there reports on a regular basis, maybe daily or even hourly would be useful. Basically the large amount of information that needs to be processed and sorted is what's taking all the time here. Regards Russell
Re: [PERFORM] raid10 write performance
* Ivan Voras: On the other hand, RAID10 is simple enough that soft-RAID implementations should be more than adequate - any ideas why a dedicated card has it slow? Barrier support on RAID10 seems to require some smallish amount of non-volatile storage which supports a high number of write operations per second, so a software-only solution might not be available. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] raid10 write performance
On 06/23/10 14:00, Florian Weimer wrote: * Ivan Voras: On the other hand, RAID10 is simple enough that soft-RAID implementations should be more than adequate - any ideas why a dedicated card has it slow? Barrier support on RAID10 seems to require some smallish amount of non-volatile storage which supports a high number of write operations per second, so a software-only solution might not be available. If I understand you correctly, this can be said in general for all spinning-disk usage and is not specific to RAID10. (And in the case of high, constant TPS, no amount of NVRAM will help you). -- 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] raid10 write performance
On Wed, 23 Jun 2010, Ivan Voras wrote: On 06/23/10 14:00, Florian Weimer wrote: Barrier support on RAID10 seems to require some smallish amount of non-volatile storage which supports a high number of write operations per second, so a software-only solution might not be available. If I understand you correctly, this can be said in general for all spinning-disk usage and is not specific to RAID10. (And in the case of high, constant TPS, no amount of NVRAM will help you). No. Write barriers work fine with a single disc, assuming it is set up correctly. The barrier is a command telling the disc to make sure that one piece of data is safe before starting to write another piece of data. However, as soon as you have multiple discs, the individual discs do not have a way of communicating with each other to make sure that the first piece of data is written before the other. That's why you need a little bit of non-volatile storage to mediate that to properly support barriers. Of course, from a performance point of view, yes, you need some NVRAM on any kind of spinning storage to maintain high commit rates. Matthew -- I wouldn't be so paranoid if you weren't all out to get me!! -- 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] raid10 write performance
On Wed, Jun 23, 2010 at 8:25 AM, Ivan Voras ivo...@freebsd.org wrote: On 06/23/10 14:00, Florian Weimer wrote: * Ivan Voras: On the other hand, RAID10 is simple enough that soft-RAID implementations should be more than adequate - any ideas why a dedicated card has it slow? Barrier support on RAID10 seems to require some smallish amount of non-volatile storage which supports a high number of write operations per second, so a software-only solution might not be available. If I understand you correctly, this can be said in general for all spinning-disk usage and is not specific to RAID10. (And in the case of high, constant TPS, no amount of NVRAM will help you). Not entirely true. Let's say you have enough battery backed cache to hold 10,000 transaction writes in memory at once. The RAID controller can now re-order those writes so that they go from one side of the disk to the other, instead of randomly all over the place. That will most certainly help improve your throughput. -- 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] raid10 write performance
On Wed, Jun 23, 2010 at 6:06 AM, Ivan Voras ivo...@freebsd.org wrote: On 06/22/10 16:40, Greg Smith wrote: Grzegorz Jaśkiewicz wrote: raid: serveRAID M5014 SAS/SATA controller Do the performant servers have a different RAID card? This one has terrible performance, and could alone be the source of your issue. The ServeRAID cards are slow in general, and certainly slow running RAID10. What are some good RAID10 cards nowadays? LSI, Areca, 3Ware (now LSI I believe) On the other hand, RAID10 is simple enough that soft-RAID implementations should be more than adequate - any ideas why a dedicated card has it slow? This is mostly a problem with some older cards that focused on RAID-5 performance, and RAID-10 was an afterthought. On many of these cards (older PERCs for instance) it was faster to either use a bunch of RAID-1 pairs in hardware with RAID-0 in software on top, or put the thing into JBOD mode and do it all in software. -- 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] cpu bound postgresql setup.
Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: PasteBin for the vmstat output http://pastebin.com/mpHCW9gt On Wed, Jun 23, 2010 at 8:22 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: Dear List , I observe that my postgresql (ver 8.4.2) dedicated server has turned cpu bound and there is a high load average in the server 50 usually. The server has 2 Quad Core CPUs already and there are 6 or 8 drives in raid 10 , there is negligable i/o wait. There is 32GB ram and no swapping. When i strace processes at random i see lot of lseek (XXX,0,SEEK_END) calls which i feel were not that frequent before. can any pointers be got for investigating the high cpu usage by postgresql processes. I'm not clear on what problem you are experiencing. Using a lot of your hardware's capacity isn't a problem in itself -- are you getting poor response time? Poor throughput? Some other problem? Is it continuous, or only when certain queries run? One thing that is apparent is that you might want to use a connection pool, or if you're already using one you might want to configure it to reduce the maximum number of active queries. With eight cores and eight drives, your best throughput is going to be at somewhere around 24 active connections, and you appear to be going to at least twice that. If you can provide a copy of your postgresql.conf settings (without comments) and an EXPLAIN ANALYZE of a slow query, along with the schema information for the tables used by the query, you'll probably get useful advice on how to adjust your configuration, indexing, or query code to improve performance. -Kevin -- 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 index lookup
The combination index works great. Would adding the combination index guarantee that the optimizer will choose that index for these kind of queries involving the columns in the combination. I verified a couple of times and it picked the right index. Just wanted to make sure it does that consistently. On Tue, Jun 22, 2010 at 7:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400 2010: This query seems unreasonable slow on a well-indexed table (13 million rows). Separate indexes are present on guardid_id , from_num and targetprt columns. Maybe you need to vacuum or reindex? Rethinking the set of indexes is probably a more appropriate suggestion. Separate indexes aren't usefully combinable for a case like this --- in principle the thing could do a BitmapAnd, but the startup time would be pretty horrid, and the LIMIT 1 is discouraging it from trying that. If this is an important case to optimize then you need a 3-column index. regards, tom lane -- 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 index lookup
Anj Adu fotogra...@gmail.com wrote: The combination index works great. Would adding the combination index guarantee that the optimizer will choose that index for these kind of queries involving the columns in the combination. I verified a couple of times and it picked the right index. Just wanted to make sure it does that consistently. It's cost based -- as long as it thinks that approach will be faster, it will use it. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: cpu bound postgresql setup. Firstly many thanks for responding. I am concerned because the load averages have increased and users complaining of slowness. I do not change settings freq
On 6/23/10, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: PasteBin for the vmstat output http://pastebin.com/mpHCW9gt On Wed, Jun 23, 2010 at 8:22 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: Dear List , I observe that my postgresql (ver 8.4.2) dedicated server has turned cpu bound and there is a high load average in the server 50 usually. The server has 2 Quad Core CPUs already and there are 6 or 8 drives in raid 10 , there is negligable i/o wait. There is 32GB ram and no swapping. When i strace processes at random i see lot of lseek (XXX,0,SEEK_END) calls which i feel were not that frequent before. can any pointers be got for investigating the high cpu usage by postgresql processes. I'm not clear on what problem you are experiencing. Using a lot of your hardware's capacity isn't a problem in itself -- are you getting poor response time? Poor throughput? Some other problem? Is it continuous, or only when certain queries run? One thing that is apparent is that you might want to use a connection pool, or if you're already using one you might want to configure it to reduce the maximum number of active queries. With eight cores and eight drives, your best throughput is going to be at somewhere around 24 active connections, and you appear to be going to at least twice that. If you can provide a copy of your postgresql.conf settings (without comments) and an EXPLAIN ANALYZE of a slow query, along with the schema information for the tables used by the query, you'll probably get useful advice on how to adjust your configuration, indexing, or query code to improve performance. -Kevin -- Sent from Gmail for mobile | mobile.google.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] cpu bound postgresql setup.
Your response somehow landed in the subject line, apparently truncated. I'll extract that to the message body and reply to what made it through. Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: Firstly many thanks for responding. I am concerned because the load averages have increased and users complaining of slowness. If performance has gotten worse, then something has changed. It would be helpful to know what. More users? New software? Database growth? Database bloat? (etc.) I do not change settings frequenly. That doesn't mean your current settings can't be changed to make things better. I was curious if there is any half dead component in th Have you reviewed what shows up if you run (as a database superuser)?: select * from pg_stat_activity; You might want to review this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- 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] Aggressive autovacuuming ?
On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Aggressive autovacuuming ?
On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. My point was that modern pgsql doesn't need db wide vacuum to prevent wrap around anymore, but can vacuum individual relations to prevent wraparound. -- 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] Aggressive autovacuuming ?
On Wed, Jun 23, 2010 at 2:20 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Jun 23, 2010 at 1:58 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 20, 2010 at 4:13 PM, Scott Marlowe scott.marl...@gmail.com wrote: The largest consequence I can see at the moment is that when I get a full vacuum (for preventing transaction-id wraparound) it would be I assume you mean the automatic database wide vacuum. I don't think 8.4 and above need that anymore. I thnk 8.3 does that too, but I'm not 100% sure. 8.4 (and 9.0) do still need to do vacuums to freeze tuples before transaction ID wraparound occurs. This is not to be confused with VACUUM FULL, which is something else altogether. My point was that modern pgsql doesn't need db wide vacuum to prevent wrap around anymore, but can vacuum individual relations to prevent wraparound. Oh, I see. I didn't realize we used to do that. Looks like that change was committed 11/5/2006. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] WAL+Os on a single disk
I have a situation where we are limited by the chassis on the box (and cost). We have a 12 x 600G hot swappable disk system (raid 10) and 2 internal disk ( 2x 146G) We would like to maximize storage on the large disks . Does it make sense to put the WAL and OS on the internal disks and use the 12 large disks only for data or should we put the WAL along with data and leave the OS on the internal disks. On our current systems..everything is on a single RAID 10 volume (and performance is good) We are just considering options now that we have the 2 extra disks to spare. -- 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] WAL+Os on a single disk
On Wed, Jun 23, 2010 at 3:01 PM, Anj Adu fotogra...@gmail.com wrote: I have a situation where we are limited by the chassis on the box (and cost). We have a 12 x 600G hot swappable disk system (raid 10) and 2 internal disk ( 2x 146G) We would like to maximize storage on the large disks . Does it make sense to put the WAL and OS on the internal disks and use the 12 large disks only for data or should we put the WAL along with data and leave the OS on the internal disks. On our current systems..everything is on a single RAID 10 volume (and performance is good) We are just considering options now that we have the 2 extra disks to spare. I have 16 disks in a server, 2 hot spares, 2 for OS and WAL and 12 for RAID-10. The RAID-10 array hits 100% utilization long before the 2 in a RAID-1 for OS and WAL do. And we log all modifying SQL statements onto the same disk set. So for us, the WAL and OS and logging on the same data set works well. -- 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] PostgreSQL as a local in-memory cache
Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: Consider a non-crash-safe wal_level that eliminates WAL activity * http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] PostgreSQL as a local in-memory cache
Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely b) Eliminate checkpointing c) Turn off the background writer d) Have PostgreSQL refuse to restart after a crash and instead call an exteral script (for reprovisioning) Well I guess I'd prefer a per-transaction setting, allowing to bypass WAL logging and checkpointing. Not going to happen; this is all or nothing. Forcing the backend to care itself for writing the data I'm not sure is a good thing, but if you say so. Yeah, I think proposal (c) is likely to be a net loss. (a) and (d) are probably simple, if by reprovisioning you mean rm -rf $PGDATA; initdb. Point (b) will be a bit trickier because there are various housekeeping activities tied into checkpoints. I think you can't actually remove checkpoints altogether, just skip the flush-dirty-pages part. Based on this thread, I have developed the following documentation patch that outlines the performance enhancements possible if durability is not required. The patch also documents that synchronous_commit = false has potential committed transaction loss from a database crash (as well as an OS crash). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.282 diff -c -c -r1.282 config.sgml *** doc/src/sgml/config.sgml 22 Jun 2010 02:57:49 - 1.282 --- doc/src/sgml/config.sgml 23 Jun 2010 18:53:26 - *** *** 1463,1469 really guaranteed to be safe against a server crash. (The maximum delay is three times xref linkend=guc-wal-writer-delay.) Unlike xref linkend=guc-fsync, setting this parameter to literaloff/ ! does not create any risk of database inconsistency: a crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning varnamesynchronous_commit/ off --- 1463,1470 really guaranteed to be safe against a server crash. (The maximum delay is three times xref linkend=guc-wal-writer-delay.) Unlike xref linkend=guc-fsync, setting this parameter to literaloff/ ! does not create any risk of database inconsistency: an operating ! system or database crash crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning varnamesynchronous_commit/ off Index: doc/src/sgml/perform.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v retrieving revision 1.80 diff -c -c -r1.80 perform.sgml *** doc/src/sgml/perform.sgml 29 May 2010 21:08:04 - 1.80 --- doc/src/sgml/perform.sgml 23 Jun 2010 18:53:26 - *** *** 1104,1107 --- 1104,1169 /sect2 /sect1 + sect1 id=non-durability +titleNon-Durable Settings/title + +indexterm zone=non-durability + primarynon-durable/primary +/indexterm + +para + Durability is a database feature that guarantees the recording of + committed transactions even if if the server crashes or loses + power. However, durability adds significant database overhead, + so if your site does not require such a guarantee, + productnamePostgreSQL/productname can be configured to run + much faster. The following are configuration changes you can make + to improve performance in such cases; they do not invalidate + commit guarantees related to database crashes, only abrupt operating + system stoppage, except as mentioned below: + + itemizedlist + listitem + para +Place the database cluster's data directory in a memory-backed +file system (i.e. acronymRAM/ disk). This eliminates all +database disk I/O, but limits data storage to the amount of +available memory (and perhaps swap). + /para + /listitem + + listitem + para +Turn off xref linkend=guc-fsync; there is no need to flush +data to disk. + /para + /listitem + + listitem + para +Turn off xref linkend=guc-full-page-writes; there is no need +to guard against partial page writes. + /para + /listitem + + listitem + para +Increase xref linkend=guc-checkpoint-segments and xref +linkend=guc-checkpoint-timeout ; this reduces the frequency +of checkpoints, but increases the storage requirements of +
Re: [PERFORM] PostgreSQL as a local in-memory cache
2010/6/23 Bruce Momjian br...@momjian.us: Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: Consider a non-crash-safe wal_level that eliminates WAL activity * http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php -- isn't fsync to off enought? Regards Pavel Bruce Momjian br...@momjian.us http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] PostgreSQL as a local in-memory cache
Pavel Stehule wrote: 2010/6/23 Bruce Momjian br...@momjian.us: Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. ?Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: ? ? ? ?Consider a non-crash-safe wal_level that eliminates WAL activity ? ? ? ? ? ?* http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php -- isn't fsync to off enought? Well, testing reported in the thread showed other settings also help, though the checkpoint lengthening was not tested. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] PostgreSQL as a local in-memory cache
On Wed, Jun 23, 2010 at 3:37 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: Consider a non-crash-safe wal_level that eliminates WAL activity * http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php I don't think we need a system-wide setting for that. I believe that the unlogged tables I'm working on will handle that case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] PostgreSQL as a local in-memory cache
On Wed, Jun 23, 2010 at 9:25 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 23, 2010 at 3:37 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: Consider a non-crash-safe wal_level that eliminates WAL activity * http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php I don't think we need a system-wide setting for that. I believe that the unlogged tables I'm working on will handle that case. Aren't they going to be truncated at startup? If the entire system is running without WAL, we would only need to do that in case of an unclean shutdown wouldn't we? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] PostgreSQL as a local in-memory cache
Robert Haas wrote: On Wed, Jun 23, 2010 at 3:37 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Josh Berkus j...@agliodbs.com writes: a) Eliminate WAL logging entirely If we elimiate WAL logging, that means a reinstall is required for even a postmaster crash, which is a new non-durable behavior. Also, we just added wal_level = minimal, which might end up being a poor name choice of we want wal_level = off in PG 9.1. ?Perhaps we should have used wal_level = crash_safe in 9.0. I have added the following TODO: ? ? ? ?Consider a non-crash-safe wal_level that eliminates WAL activity ? ? ? ? ? ?* http://archives.postgresql.org/pgsql-performance/2010-06/msg00300.php I don't think we need a system-wide setting for that. I believe that the unlogged tables I'm working on will handle that case. Uh, will we have some global unlogged setting, like for the system tables and stuff? It seems like an heavy burden to tell people they have to create ever object as unlogged, and we would still generate log for things like transaction commits. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] PostgreSQL as a local in-memory cache
Dave Page dp...@pgadmin.org writes: On Wed, Jun 23, 2010 at 9:25 PM, Robert Haas robertmh...@gmail.com wrote: I don't think we need a system-wide setting for that. I believe that the unlogged tables I'm working on will handle that case. Aren't they going to be truncated at startup? If the entire system is running without WAL, we would only need to do that in case of an unclean shutdown wouldn't we? The problem with a system-wide no-WAL setting is it means you can't trust the system catalogs after a crash. Which means you are forced to use initdb to recover from any crash, in return for not a lot of savings (for typical usages where there's not really much churn in the catalogs). I tend to agree with Robert that a way to not log content updates for individual user tables is likely to be much more useful in practice. regards, tom lane -- 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] PostgreSQL as a local in-memory cache
Tom Lane wrote: Dave Page dp...@pgadmin.org writes: On Wed, Jun 23, 2010 at 9:25 PM, Robert Haas robertmh...@gmail.com wrote: I don't think we need a system-wide setting for that. ?I believe that the unlogged tables I'm working on will handle that case. Aren't they going to be truncated at startup? If the entire system is running without WAL, we would only need to do that in case of an unclean shutdown wouldn't we? The problem with a system-wide no-WAL setting is it means you can't trust the system catalogs after a crash. Which means you are forced to True, and in fact any postmaster crash could lead to curruption. use initdb to recover from any crash, in return for not a lot of savings (for typical usages where there's not really much churn in the catalogs). I tend to agree with Robert that a way to not log content updates for individual user tables is likely to be much more useful in practice. OK, TODO removed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
