Re: [PERFORM] Response time increases over time
Thanks, Josh. The only reason I tried 8.4 first is that it was available for Debian as compiled package, so it was simpler for me to do it. Anyway I am going to test 9.1 too. I will post about the results. Best reagrds, Otto 2011/12/7 Josh Berkus j...@agliodbs.com On 12/6/11 4:30 PM, Havasvölgyi Ottó wrote: Is there so much difference between 8.4 and 9.1, or is this something else? Please tell me if any other info is needed. It is fairly likely that the difference you're seeing here is due to improvements made in checkpointing and other operations made between 8.4 and 9.1. Is there some reason you didn't test 9.1 on Linux to compare the two? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Different query plans on same servers
On 12/06/2011 09:00 PM, Tom Lane wrote: Mario Splivalo mario.spliv...@megafon.hr writes: I have 8.4.8 on producion and 8.4.9 on test, could that explain the difference in plans chosen? I'd wonder first if you have the same statistics settings on both. The big problem here is that the estimation of the join size is bad (8588 versus 0). Just an update here. I did downgrade postgres on testbox to 8.4.8 and now it's choosing bad plan there too. So we upgraded postgres on production server and the bad plan went away. We're preparing for upgrade to 9.1 now, we hope to offload some of the SELECTs to the slave server, we'll see how that will work. Thank you for your inputs! Mario -- 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
On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote: Thanks, Josh. The only reason I tried 8.4 first is that it was available for Debian as compiled package, so it was simpler for me to do it. Anyway I am going to test 9.1 too. I will post about the results. If you're using squeeze, you can get 9.1 from the debian backports. Mario -- 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] Intersect/Union X AND/OR
On Mon, Dec 5, 2011 at 14:14, Thiago Godoi thiagogodo...@gmail.com wrote: My original query : select table1.id from table1, (select function(12345) id) table2 where table1.kind = 1234 and table1.id = table2.id Nested Loop (cost=0.00..6.68 rows=1 width=12) Join Filter: () - Seq Scan on recorte (cost=0.00..6.39 rows=1 width=159) Filter: (id = 616) - Result (cost=0.00..0.26 rows=1 width=0) Note that this EXPLAIN output is quite different from your query. Intead of a kind=1234 clause there's id=616. Also, please post EXPLAIN ANALYZE results instead whenever possible. When I changed the query to use intersect : [...] The second plan is about 10 times faster than the first one. Judging by these plans, the 1st one should not be slower. Note that just running the query once and comparing times is often misleading, especially for short queries, since noise often dominates the query time -- depending on how busy the server was at the moment, what kind of data was cached, CPU power management/frequency scaling, etc. ESPECIALLY don't compare pgAdmin timings since those also include network variance, the time taken to render results on your screen and who knows what else. A simple way to benchmark is with pgbench. Just write the query to a text file (it needs to be a single line and not more than ~4000 characters). Then run 'pgbench -n -f pgbench_script -T 5' to run it for 5 seconds. These results are still not entirely reliable, but much better than pgAdmin timings. 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] Question about VACUUM
Josh Berkus j...@agliodbs.com wrote: On 12/5/11 1:36 PM, Kevin Grittner wrote: I understand the impulse to run autovacuum less frequently or less aggressively. When we first started running PostgreSQL the default configuration was very cautious. The default settings are deliberately cautious, as default settings should be. I was talking historically, about the defaults in 8.1: http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html Those defaults were *over*-cautious to the point that we experienced serious problems. My point was that many people's first instinct in that case is to make the setting less aggressive, as I initially did and the OP has done. The problem is actually solved by making them *more* aggressive. Current defaults are pretty close to what we found, through experimentation, worked well for us for most databases. But yes, anyone with a really large/high-traffic database will often want to make autovac more aggressive. I think we're in agreement: current defaults are good for a typical environment; high-end setups still need to tune to more aggressive settings. This is an area where incremental changes with monitoring works well. -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] Partitions and joins lead to index lookups on all partitions
Hi there, Currently, we are running into serious performance problems with our paritioning setup, because index lookups are mostly done on allpartions, in stead of the one partition it should know that it can find the needed row. Simple example, were we have a partitioned tables named part_table. So here it goes: select * from part_table where id = 12123231 Will do an index lookup only in the partition that it knows it can find the id there. However: select * from part_table where id = (select 12123231) Will do an index lookup in ALL partitions, meaning it is significantly slower, even more since the database will not fit into memory. So okay, we could just not use parameterized queries... Well.. not so fast. Consider a second table referencing to the first: ref_table: group_id bigint part_table_id bigint Now when I join the two: select part_table.* from part_table join ref_table on (ref_table.part_table_id = part_table.id and group_id = 12321) It will also do index loopups on ALL partitions. How do we handle this? Above queries are simplified versions of the things gooing on but the idea is clear. I tried dooing this in 9.1 (we are currently using 9.0), but this does not matter. So what is actually the practicial use of partitioning if you can't even use it effectively for simple joins? constraint_exclusion is enabled correctly, and as far as I can see, this behaviour is according to the book. Are there any progresses in maybe 9.2 to make this any better? If not, how schould we handle this? We can also not choose to parition, but how will that perform on a 100 GB table? Kind regards, Christiaan Willemsen
[PERFORM] autovacuum, any log?
Hello, I have a postgres 9.0.2 installation. Every works fine, but in some hours of day I got several timeout in my application (my application wait X seconds before throw a timeout). Normally hours are not of intensive use, so I think that the autovacuum could be the problem. Is threre any log where autovacuum write information about it self like duration for each table or any other relevante information. Another inline question, should I exclude bigger tables from autovacuum or there are some mechanism to tell autovacuum to not run often on bigger tables (tables with more than 400 millions of rows) Thanks!
Re: [PERFORM] pg_upgrade
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 /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 :) Tory -- 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] autovacuum, any log?
On Wed, Dec 7, 2011 at 8:34 AM, Anibal David Acosta a...@devshock.com wrote: Hello, I have a postgres 9.0.2 installation. Every works fine, but in some hours of day I got several timeout in my application (my application wait X seconds before throw a timeout). Normally hours are not of intensive use, so I think that the autovacuum could be the problem. Is threre any log where autovacuum write information about it self like “duration for each table” or any other relevante information. Another inline question, should I exclude bigger tables from autovacuum or there are some mechanism to tell autovacuum to not run often on bigger tables (tables with more than 400 millions of rows) More often than not not the problem will be checkpoint segments not autovacuum. log vacuum and checkpoints, and then run something like iostat in the background and keep an eye on %util to see if one or the other is slamming your IO subsystem. Default tuning for autovac is pretty conservative, to the point that it won't usually hurt your IO, but may not keep up with vaccuming, leading to table bloating. -- 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, On 8 December 2011 02:15, Christiaan Willemsen cwillem...@technocon.com wrote: Currently, we are running into serious performance problems with our paritioning setup, because index lookups are mostly done on allpartions, in stead of the one partition it should know that it can find the needed row. Planner is not very smart about partitions. If expression can't be evaluated to constant (or you use stable/volatile function) during planning time then you get index/seq scan across all partitions. Now when I join the two: select part_table.* from part_table join ref_table on (ref_table.part_table_id = part_table.id and group_id = 12321) I had to add extra where conditions which help to decide the right partitions i.e. where part_col between X and Y. It would be quite hard to this in your case. You can execute another query like - select part_table_id from ref_table where group_id = 12321 - or select min(part_table_id), max(part_table_id) from ref_table where group_id = 12321 and the use in() or between X and Y in second query (so have to execute two queries). -- Ondrej Ivanic (ondrej.iva...@gmail.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
Thanks for that Mario, I will check it out. @All: Anyway, I have compiled 9.1.2 from source, and unfortunately the performance haven't got better at the same load, it is consistently quite low (~70 ms average transaction time with 100 clients) on this Debian. I am quite surprised about this, it is unrealistically high. I have run pg_test_fsync, and showed about 2600 fsync/sec, which means HDD has write caching on (it is a 7200 rpm drive, there is no HW RAID controller). However my other machine, the simple Win7 one, on which performance was so good and consistent, fsync/sec was a lot lower, only about 100 as I can remember, so it probably really flushed each transaction to disk. I have also run load simulation on this Debian machine with InnoDb, and it performed quite well, so the machine itself is good enough to handle this. On the other hand it is quite poor on Win7, but that's another story... 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. I also checked that connection pooling works well, and clients don't close/open connections. I also have a graph about outstanding transaction count over time, and it is quite strange: it shows that low performce (20-30 xacts at a time) and high-performace (5 xact at a time) parts are alternating quite frequently instead of being more even. Do anybody have any idea based on this info about what can cause such behaviour, or what I could check or try? Thanks in advance, Otto 2011/12/7 Mario Splivalo mario.spliv...@megafon.hr On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote: Thanks, Josh. The only reason I tried 8.4 first is that it was available for Debian as compiled package, so it was simpler for me to do it. Anyway I am going to test 9.1 too. I will post about the results. If you're using squeeze, you can get 9.1 from the debian backports. Mario -- 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 failure contrib issue?
Well thought it was maybe just going from 8.4.4 to 9.1.1 so upgraded to 8.4.9 and tried pg_upgrade again (this is 64bit) and it's failing -bash-4.0$ /tmp/pg_upgrade --check --old-datadir /data/db --new-datadir /data1/db --old-bindir /ipix/pgsql/bin --new-bindir /ipix/pgsql9/bin Performing Consistency Checks - Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data typesok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects ok There were problems executing /ipix/pgsql/bin/pg_ctl -w -l /dev/null -D /data/db stop /dev/null 21 Failure, exiting I've read some re pg_migrator and issues with contribs, but wondered if there is something Else I need to know here Thanks again Tory -- 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
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
