Re: [PERFORM] Response time increases over time
Yes, ext3 is the global file system, and you are right, PG xlog and data are on this one. Is this really what happens Aidan at fsync? What is be the best I can do? Mount xlog directory to a separate file system? If so, which file system fits the best for this purpose? Should I also mount the data separately, or is that not so important? The strange thing is that InnoDb data and xlog are also on the same filesystem, but on a separate one (ext4) from the global one. Thanks, Otto 2011/12/8 Aidan Van Dyk ai...@highrise.ca On Wed, Dec 7, 2011 at 5:13 PM, Havasvölgyi Ottó havasvolgyi.o...@gmail.com wrote: So there seems to be something on this Debian machine that hinders PostgreSQL to perform better. With 8.4 I logged slow queries (with 9.1 not yet), and almost all were COMMIT, taking 10-20-30 or even more ms. But at the same time the fsync rate can be quite high based on pg_test_fsync, so probably not fsync is what makes it slow. Performance seems to degrade drastically as I increase the concurrency, mainly concurrent commit has problems as I can see. Do anybody have any idea based on this info about what can cause such behaviour, or what I could check or try? Let me guess, debian squeeze, with data and xlog on both on a single ext3 filesystem, and the fsync done by your commit (xlog) is flushing all the dirty data of the entire filesystem (including PG data writes) out before it can return... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Re: [PERFORM] Response time increases over time
On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote: Let me guess, debian squeeze, with data and xlog on both on a single ext3 filesystem, and the fsync done by your commit (xlog) is flushing all the dirty data of the entire filesystem (including PG data writes) out before it can return... This is fixed with the data=writeback mount option, right? (If it's the root file system, you need to add rootfsflags=data=writeback to your kernel boot flags) While this setting is safe and recommended for PostgreSQL and other transactional databases, it can cause garbage to appear in recently written files after a crash/power loss -- for applications that don't correctly fsync data to disk. Regards, Marti -- 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] Partitions and joins lead to index lookups on all partitions
Hi Ondrej, Your solution has occurred to me, and wil even work in some cases. But in more advanced queries, where for example, I would need the group ID again to do some window function magic, this will sadly not work, without again doing a reverse lookup to the ref_table to find it again. This scheme might still be faster though even though it would take more queries. Im now testing some of queries against a non-paritioned version of our dataset to see what the difference is. I'm wondering how much the insert performance wil be impacted when not-paritioning our data. We do have a few indexes and constriants on these tables, but not whole lot. I'll so some measurements to see how this wil work out. The general dilemma would be as follows: What if the suggested max of 100 partions would mean that a partition table will also not fit into memory efficiently, and/or that the access pattern is such that because of the query planner, it needs to work it's way though all the partitions for virtually most of the serious queries being done on the data set. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Partitions-and-joins-lead-to-index-lookups-on-all-partitions-tp5055965p5058853.html Sent from the PostgreSQL - performance mailing list archive at Nabble.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] Response time increases over time
I have moved the data directory (xlog, base, global, and everything) to an ext4 file system. The result hasn't changed unfortuately. With the same load test the average response time: 80ms; from 40ms to 120 ms everything occurs. This ext4 has default settings in fstab. Have you got any other idea what is going on here? Thanks, Otto 2011/12/8 Marti Raudsepp ma...@juffo.org On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote: Let me guess, debian squeeze, with data and xlog on both on a single ext3 filesystem, and the fsync done by your commit (xlog) is flushing all the dirty data of the entire filesystem (including PG data writes) out before it can return... This is fixed with the data=writeback mount option, right? (If it's the root file system, you need to add rootfsflags=data=writeback to your kernel boot flags) While this setting is safe and recommended for PostgreSQL and other transactional databases, it can cause garbage to appear in recently written files after a crash/power loss -- for applications that don't correctly fsync data to disk. Regards, Marti
Re: [PERFORM] Response time increases over time
Otto, Separate the pg_xlog directory onto its own filesystem and retry your tests. Bob Lunney From: Havasvölgyi Ottó havasvolgyi.o...@gmail.com To: Marti Raudsepp ma...@juffo.org Cc: Aidan Van Dyk ai...@highrise.ca; pgsql-performance@postgresql.org Sent: Thursday, December 8, 2011 9:48 AM Subject: Re: [PERFORM] Response time increases over time I have moved the data directory (xlog, base, global, and everything) to an ext4 file system. The result hasn't changed unfortuately. With the same load test the average response time: 80ms; from 40ms to 120 ms everything occurs. This ext4 has default settings in fstab. Have you got any other idea what is going on here? Thanks, Otto 2011/12/8 Marti Raudsepp ma...@juffo.org On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote: Let me guess, debian squeeze, with data and xlog on both on a single ext3 filesystem, and the fsync done by your commit (xlog) is flushing all the dirty data of the entire filesystem (including PG data writes) out before it can return... This is fixed with the data=writeback mount option, right? (If it's the root file system, you need to add rootfsflags=data=writeback to your kernel boot flags) While this setting is safe and recommended for PostgreSQL and other transactional databases, it can cause garbage to appear in recently written files after a crash/power loss -- for applications that don't correctly fsync data to disk. Regards, Marti
Re: [PERFORM] Response time increases over time
I have put pg_xlog back to the ext3 partition, but nothing changed. I have also switched off sync_commit, but nothing. This is quite interesting... Here is a graph about the transaction time (sync_commit off, pg_xlog on separate file system): Graph http://uploadpic.org/v.php?img=qIjfWBkHyE On the graph the red line up there is the tranaction/sec, it is about 110, and does not get lower as the transaction time gets higher. Based on this, am I right that it is not the commit, that causes these high transaction times? Kernel version is 2.6.32. Any idea is appreciated. Thanks, Otto 2011/12/8 Bob Lunney bob_lun...@yahoo.com Otto, Separate the pg_xlog directory onto its own filesystem and retry your tests. Bob Lunney -- *From:* Havasvölgyi Ottó havasvolgyi.o...@gmail.com *To:* Marti Raudsepp ma...@juffo.org *Cc:* Aidan Van Dyk ai...@highrise.ca; pgsql-performance@postgresql.org *Sent:* Thursday, December 8, 2011 9:48 AM *Subject:* Re: [PERFORM] Response time increases over time I have moved the data directory (xlog, base, global, and everything) to an ext4 file system. The result hasn't changed unfortuately. With the same load test the average response time: 80ms; from 40ms to 120 ms everything occurs. This ext4 has default settings in fstab. Have you got any other idea what is going on here? Thanks, Otto 2011/12/8 Marti Raudsepp ma...@juffo.org On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk ai...@highrise.ca wrote: Let me guess, debian squeeze, with data and xlog on both on a single ext3 filesystem, and the fsync done by your commit (xlog) is flushing all the dirty data of the entire filesystem (including PG data writes) out before it can return... This is fixed with the data=writeback mount option, right? (If it's the root file system, you need to add rootfsflags=data=writeback to your kernel boot flags) While this setting is safe and recommended for PostgreSQL and other transactional databases, it can cause garbage to appear in recently written files after a crash/power loss -- for applications that don't correctly fsync data to disk. Regards, Marti
[PERFORM] Slow query after upgrade from 8.2 to 8.4
Hello All. We recently upgrade our server from PG8.2 to 8.4. Our current version is: database= SELECT version(); version PostgreSQL 8.4.8 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.2 20070831 prerelease [FreeBSD], 64-bit (1 row) I read and setup most of the tuning advices. Since the upgrade we have some very complicated reports that start working too slow. Form tens of seconds to 1 hours. I execute vacuum analyze before start the query. Here I will post explain analyze. If you think it is necessary I will post the exact query: http://explain.depesz.com/s/J0O I think the planner didn't choose the best plan. I will try to I rewrite the query and set join_collapse_limit to 1 and see what will happen. Meanwhile any suggestions are welcome. Best regards and thanks in advance for the help. Kaloyan Iliev -- 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 query after upgrade from 8.2 to 8.4
On 12/08/2011 11:29 AM, Kaloyan Iliev Iliev wrote: I think the planner didn't choose the best plan. I will try to I rewrite the query and set join_collapse_limit to 1 and see what will happen. Meanwhile any suggestions are welcome. Try rewriting the query, definitely. But make sure your statistical targets are high enough for an analyze to make a difference. I see way too many nested loops with wrong row estimates. Like these: Nested Loop (cost=0.00..8675.62 rows=2263 width=4) (actual time=0.456..5991.749 rows=68752 loops=167) Join Filter: (dd.debtid = ad.debtid) Nested Loop (cost=0.00..7864.54 rows=1160 width=4) (actual time=0.374..2781.762 rows=34384 loops=167) Index Scan using config_office_idx on config cf (cost=0.00..7762.56 rows=50 width=8) (actual time=0.199..1623.366 rows=2460 loops=167) Index Cond: (office = 6) Filter: (id = (SubPlan 6)) There are several spots where the row estimates are off by one or two orders of magnitude. Instead of doing a sequence scan for such large tables, it's nest looping over an index scan, sometimes millions of times. And then you have these: Index Scan using config_confid_idx on config (cost=0.00..0.66 rows=6 width=12) (actual time=0.023..0.094 rows=10 loops=1655853) Index Cond: (confid = $3) Index Scan using debts_desc_refid_idx on debts_desc dd (cost=0.00..1.66 rows=30 width=8) (actual time=0.061..0.381 rows=14 loops=410867) Index Cond: (dd.refid = cf.confid) Index Scan using acc_debts_debtid_idx on acc_debts ad (cost=0.00..0.39 rows=2 width=8) (actual time=0.034..0.053 rows=2 loops=5742191) Index Cond: (ad.debtid = dd.debtid) Index Scan using acc_debtscl_debtid_idx on acc_debts_cleared ad (cost=0.00..0.27 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=5742183) Index Cond: (ad.debtid = dd.debtid) Having index scans that big embedded in nested loops is going to murder your CPU even if every table involved is cached in memory. I'm not surprised this takes an hour or more to run. Increase the statistics on these tables, and pay special attention to the debtid and refid columns, and then analyze them again. What's your default_statistics_target? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] pg_upgrade
Tory M Blue wrote: From my last report I had success but it was successful due to lots of manual steps. I figured it may be safer to just create a new rpm, installing to pgsql9 specific directories and a new data directory. This allows pg_upgrade to complete successfully (so it says). However my new data directory is empty and the old data directory now has what appears to be 8.4 data and the 9.1 data. /data is olddatadir original data dir [root@devqdb03 queue]# ll /data/queue total 12 drwx-- 2 postgres dba 4096 2011-12-07 09:44 16384 drwx-- 3 postgres dba 4096 2011-12-07 11:34 PG_9.1_201105231 -rw--- 1 postgres dba4 2011-12-07 09:44 PG_VERSION That sure looks like a tablespace to me, not a data directory. /data1 is the new 9.1 installed location. [root@devqdb03 queue]# ll /data1/queue/ total 0 Do I have to manually move the new PG_9.1. data to /data1 or. I'm just confused at what I'm looking at here. If I don't move anything and start up the DB , I get this psql (8.4.4, server 9.1.1) WARNING: psql version 8.4, server version 9.1. Some psql features might not work. Type help for help. Sorry my upgrade process has been an ugly mess :) You are using an 8.4.4 psql to connect to a 9.1.1 server. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 query after upgrade from 8.2 to 8.4
Kaloyan Iliev Iliev kalo...@digsys.bg writes: We recently upgrade our server from PG8.2 to 8.4. ... Here I will post explain analyze. If you think it is necessary I will post the exact query: http://explain.depesz.com/s/J0O Yeah, you need to show the query. It looks like the performance problem is stemming from a lot of subselects, but it's not clear why 8.4 would be handling those worse than 8.2. 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
