Re: [PERFORM] bad planning with 75% effective_cache_size
How about with par_ as (select * from product_parent where parent_name like 'aa%' ) select distinct product_code from product p_ inner join par_ on p_.parent_id=par_.id limit 2 ? 2012/4/3 Istvan Endredy istvan.endr...@gmail.com Hi, i've ran into a planning problem. select distinct product_code from product p_ inner join product_parent par_ on p_.parent_id=par_.id where par_.parent_name like 'aa%' limit 2 If effective_cache_size is smaller (32MB), planning is ok and query is fast. (10ms) In the worst case (effective_cache_size=6GB) the speed depends on the value of 'limit' (in select): if it is smaller, query is slower. (12ms) -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Random performance hit, unknown cause.
Check your pagecache settings, when doing heavy io writes of a large file you can basically force a linux box to completely stall. At some point once the pagecache has reached it's limit it'll force all IO to go sync basically from my understanding. We are still fighting with this but lots of changes in RH6 seem to address of lot of these issues. grep -i dirty /proc/meminfo cat /proc/sys/vm/ cat /proc/sys/vm/nr_pdflush_threads Once the dirty pages reaches a really large size and the limit of pagecache your system should experience a pretty abrupt drop in performance. You should be able to avoid this by using sync writes, but we haven't had a chance to completely isolate and address this issue. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Claudio Freire Sent: Thursday, April 12, 2012 1:50 PM To: Brian Fehrle Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Random performance hit, unknown cause. On Thu, Apr 12, 2012 at 3:41 PM, Brian Fehrle bri...@consistentstate.com wrote: This morning, during our nightly backup process (where we grab a copy of the data directory), we started having this same issue. The main thing that I see in all of these is a high disk wait on the system. When we are performing 'well', the %wa from top is usually around 30%, and our load is around 12 - 15. This morning we saw a load 21 - 23, and an %wa jumping between 60% and 75%. The top process pretty much at all times is the WAL Sender Process, is this normal? Sounds like vacuum to me. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/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] scale up (postgresql vs mssql)
hi all, i ran vmstat during the test : [yb@centos08 ~]$ vmstat 1 15 procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si sobibo in cs us sy id wa st 0 0 0 6131400 160556 111579200 112 22 17 0 0 100 0 0 0 0 0 6131124 160556 111580000 0 532 540 360 1 0 99 0 0 5 1 0 6127852 160556 111604800 0 3352 1613 1162 18 1 80 1 0 7 0 0 6122984 160556 111731200 0 14608 5408 3703 86 7 6 1 0 8 0 0 6121372 160556 111796800 0 13424 5434 3741 86 7 5 2 0 7 1 0 6120504 160556 111895200 0 13616 5296 3546 86 7 5 2 0 7 0 0 6119528 160572 111972800 0 13836 5494 3597 86 7 4 2 0 6 1 0 6118744 160572 112040800 0 15296 5552 3869 89 8 3 1 0 2 0 0 6118620 160572 112028800 0 13792 4548 3054 63 6 25 6 0 0 0 0 6118620 160572 112039200 0 3552 1090 716 8 1 88 3 0 0 0 0 6118736 160572 112039200 0 1136 787 498 1 0 98 1 0 0 0 0 6118868 160580 112040000 028 348 324 1 0 99 0 0 0 0 0 6118992 160580 112044000 0 380 405 347 1 0 99 1 0 0 0 0 6118868 160580 112044000 0 1544 468 320 1 0 100 0 0 0 0 0 6118720 160580 112044000 0 0 382 335 0 0 99 0 0 the temp-tables normally don't populate more then 10 rows. they are being created in advanced. we don't drop them, we use ON COMMIT DELETE ROWS. i believe temp-tables are in the RAM, so no disk-i/o, right? and also: no writing to the system catalogs, right? about returning multiple refcursors, we checked this issue in the past, and we concluded that returning many small refcursors (all have the same structure), is faster than returning 1 big refcursor. dose it sound wired (maybe it worth more tests)? that's why we took that path. about having multiple procedures each returning one resultset: it's too much code rewrite at the web-server's code. the disk system is a built-in intel fake-raid, configured as raid0. i do a dual-boot, so both windows and centos are on the same hardware. Thanks again for any more help.
Re: [PERFORM] scale up (postgresql vs mssql)
On 4/18/2012 2:32 AM, Eyal Wilde wrote: hi all, i ran vmstat during the test : [yb@centos08 ~]$ vmstat 1 15 procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si sobibo in cs us sy id wa st 2 0 0 6118620 160572 112028800 0 13792 4548 3054 63 6 25 6 0 the temp-tables normally don't populate more then 10 rows. they are being created in advanced. we don't drop them, we use ON COMMIT DELETE ROWS. i believe temp-tables are in the RAM, so no disk-i/o, right? and also: no writing to the system catalogs, right? Temp tables are not 100% ram, they might spill to disk. The vmstat shows there is disk io. The BO column (blocks out) shows you are writing to disk. And you have wait time (which means one or more of the cpus is stopped waiting for disk). I don't know if the disk io is because of the temp tables (I've never used them myself), or something else (stats, vacuum, logs, other sql, etc). I'd bet, though, that a derived table would be faster than create temp table...; insert into temp ; select .. from temp; Of course it may not be that much faster... and it might require a lot of code change. Might be worth a quick benchmark though. about returning multiple refcursors, we checked this issue in the past, and we concluded that returning many small refcursors (all have the same structure), is faster than returning 1 big refcursor. dose it sound wired (maybe it worth more tests)? that's why we took that path. No, if you tried it out, I'd stick with what you have. I've never used them myself, so I was just wondering aloud. -Andy -- 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] scale up (postgresql vs mssql)
On Wed, Apr 18, 2012 at 2:32 AM, Eyal Wilde e...@impactsoft.co.il wrote: hi all, i ran vmstat during the test : [yb@centos08 ~]$ vmstat 1 15 procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 0 6131400 160556 1115792 0 0 1 12 22 17 0 0 100 0 0 0 0 0 6131124 160556 1115800 0 0 0 532 540 360 1 0 99 0 0 5 1 0 6127852 160556 1116048 0 0 0 3352 1613 1162 18 1 80 1 0 7 0 0 6122984 160556 1117312 0 0 0 14608 5408 3703 86 7 6 1 0 8 0 0 6121372 160556 1117968 0 0 0 13424 5434 3741 86 7 5 2 0 7 1 0 6120504 160556 1118952 0 0 0 13616 5296 3546 86 7 5 2 0 7 0 0 6119528 160572 1119728 0 0 0 13836 5494 3597 86 7 4 2 0 6 1 0 6118744 160572 1120408 0 0 0 15296 5552 3869 89 8 3 1 0 2 0 0 6118620 160572 1120288 0 0 0 13792 4548 3054 63 6 25 6 0 0 0 0 6118620 160572 1120392 0 0 0 3552 1090 716 8 1 88 3 0 0 0 0 6118736 160572 1120392 0 0 0 1136 787 498 1 0 98 1 0 0 0 0 6118868 160580 1120400 0 0 0 28 348 324 1 0 99 0 0 0 0 0 6118992 160580 1120440 0 0 0 380 405 347 1 0 99 1 0 0 0 0 6118868 160580 1120440 0 0 0 1544 468 320 1 0 100 0 0 0 0 0 6118720 160580 1120440 0 0 0 0 382 335 0 0 99 0 0 the temp-tables normally don't populate more then 10 rows. they are being created in advanced. we don't drop them, we use ON COMMIT DELETE ROWS. i believe temp-tables are in the RAM, so no disk-i/o, right? and also: no writing to the system catalogs, right? about returning multiple refcursors, we checked this issue in the past, and we concluded that returning many small refcursors (all have the same structure), is faster than returning 1 big refcursor. dose it sound wired (maybe it worth more tests)? that's why we took that path. no chance of seeing the code or a reasonable reproduction? merlin -- 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] Linux machine aggressively clearing cache
On 4/12/12 8:47 AM, Steve Crawford wrote: On 03/30/2012 05:51 PM, Josh Berkus wrote: So this turned out to be a Linux kernel issue. Will document it on www.databasesoup.com. Anytime soon? About to build two PostgreSQL servers and wondering if you have uncovered a kernel version or similar issue to avoid. Yeah, I'll blog it. -- 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] bad planning with 75% effective_cache_size
On 4/17/12 2:49 AM, Istvan Endredy wrote: Hi, thanks for the suggestion, but it didn't help. We have tried it earlier. 7500ms http://explain.depesz.com/s/ctn This plan seems very odd -- doing individual index lookups on 2.8m rows is not standard planner behavior. Can you confirm that all of your other query cost parameters are the defaults? -- 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
