Re: [PERFORM] Function scan/Index scan to nested loop
On Mon, May 10, 2010 at 11:32 PM, Carlo Stonebanks wrote: > Hello all, > > A query ran twice in succession performs VERY poorly the first time as it > iterates through the nested loop. The second time, it rips. Please see SQL, > SLOW PLAN and FAST PLAN below. This is almost always due to caching. First time the data aren't in the cache, second time they are. > I don't know why these nested loops are taking so long to execute. > " -> Nested Loop (cost=0.00..42866.98 rows=77 width=18) (actual > time=126.354..26301.027 rows=9613 loops=1)" > " -> Nested Loop (cost=0.00..42150.37 rows=122 width=18) (actual > time=117.369..15349.533 rows=13247 loops=1)" Your row estimates are WAY off. A nested loop might now be the best choice. Also note that some platforms add a lot of time to some parts of an explain analyze due to slow time function response. Compare the run time of the first run with and without explain analyze. -- 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] Function scan/Index scan to nested loop
On 11/05/10 13:32, Carlo Stonebanks wrote: > Hello all, > > A query ran twice in succession performs VERY poorly the first time as > it iterates through the nested loop. The second time, it rips. Please > see SQL, SLOW PLAN and FAST PLAN below. I haven't looked at the details, but the comment you made about it being fast on the live server which hits this query frequently tends to suggest that this is a caching issue. Most likely, the first time Pg has to read the data from disk. The second time, it's in memory-based disk cache or even in Pg's shared_buffers, so it can be accessed vastly quicker. -- Craig Ringer Tech-related writing: 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] Function scan/Index scan to nested loop
Thanks Scott, This is almost always due to caching. First time the data aren't in the cache, second time they are. << I had assumed that it was caching, but I don't know from where because of the inexplicable delay. Hardware? O/S (Linux)? DB? From the function, which is IMMUTABLE? I am concerned that there is such a lag between all the index and function scans start/complete times and and the nested loops starting. I have reformatted the SLOW PLAN results below to make them easier to read. Can you tell me if this makes any sense to you? I can understand that EXPLAIN might inject some waste, but the delay being shown here is equivalent to the delay in real query times - I don't think EXPLAIN components would inject 15 second waits... would they? Your row estimates are WAY off. A nested loop might now be the best choice. << I tried to run this with set enable_nestloop to off and it built this truly impressively complex plan! However, the cache had already spun up. The thing that makes testing so difficult is that once the caches are loaded, you have to flail around trying to find query parameters that DON'T hit the cache, making debugging difficult. The row estimates being off is a chronic problem with our DB. I don't think the 3000 row ANALYZE is getting a proper sample set and would love to change the strategy, even if at the expense of speed of execution of ANALYZE. I don't know what it is about our setup that makes our PG servers so hard to tune, but I think its time to call the cavalry (gotta find serious PG server tuning experts in NJ). Carlo SLOW PLAN Sort (cost=42869.40..42869.59 rows=77 width=18) (actual time=26316.495..26322.102 rows=9613 loops=1) Sort Key: p.provider_id, zips_in_mile_range.distance Sort Method: quicksort Memory: 1136kB -> Nested Loop (cost=0.00..42866.98 rows=77 width=18) (actual time=126.354..26301.027 rows=9613 loops=1) -> Nested Loop (cost=0.00..42150.37 rows=122 width=18) (actual time=117.369..15349.533 rows=13247 loops=1) -> Function Scan on zips_in_mile_range (cost=0.00..52.50 rows=67 width=40) (actual time=104.196..104.417 rows=155 loops=1) Filter: (zip > ''::text) -> Index Scan using provider_practice_default_base_zip_country_idx on provider_practice pp (cost=0.00..628.30 rows=2 width=19) (actual time=1.205..98.231 rows=85 loops=155) Index Cond: ((pp.default_country_code = 'US'::bpchar) AND (substr((pp.default_postal_code)::text, 1, 5) = zips_in_mile_range.zip) AND (pp.is_principal = 'Y'::bpchar)) Filter: (COALESCE(pp.record_status, 'A'::bpchar) = 'A'::bpchar) -> Index Scan using provider_provider_id_provider_status_code_idx on provider p (cost=0.00..5.86 rows=1 width=4) (actual time=0.823..0.824 rows=1 loops=13247) Index Cond: ((p.provider_id = pp.provider_id) AND (p.provider_status_code = 'A'::bpchar)) Filter: (p.is_visible = 'Y'::bpchar) -- 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 Bulk Delete
Hi all! We moved from MySQL to Postgresql for some of our projects. So far we're very impressed with the performance (especially INSERTs and UPDATEs), except for a strange problem with the following bulk delete query: DELETE FROM table1 WHERE table2_id = ? I went through these Wiki pages, trying to solve the problem: http://wiki.postgresql.org/wiki/SlowQueryQuestions and http://wiki.postgresql.org/wiki/Performance_Optimization but unfortunately without much luck. Our application is doing batch jobs. On every batch run, we must delete approx. 1M rows in table1 and recreate these entries. The inserts are very fast, but deletes are not. We cannot make updates, because there's no identifying property in the objects of table1. This is what EXPLAIN is telling me: EXPLAIN ANALYZE DELETE FROM table1 WHERE table2_id = 11242939 QUERY PLAN Index Scan using sr_index on table1 (cost=0.00..8.56 rows=4 width=6) (actual time=0.111..0.154 rows=4 loops=1) Index Cond: (table2_id = 11242939) Total runtime: 0.421 ms (3 rows) This seems to be very fast (using the index), but running this query from JDBC takes up to 20ms each. For 1M rows this sum up to several hours. When I have a look at pg_top psql uses most of the time for the deletes. CPU usage is 100% (for the core used by postgresql). So it seems that postgresql is doing some sequential scanning or constraint checks. This is the table structure: id bigint (primary key) table2_id bigint (foreign key constraint to table 2, *indexed*) table3_id bigint (foreign key constraint to table 3, *indexed*) some non-referenced text and boolean fields My server settings (Potgresql 8.4.2): shared_buffers = 1024MB effective_cache_size = 2048MB work_mem = 128MB wal_buffers = 64MB checkpoint_segments = 32 checkpoint_timeout = 15min checkpoint_completion_target = 0.9 It would be very nice to give me a hint to solve the problem. It drives me crazy ;-) If you need more details please feel free to ask! Thanks in advance for your help! Kind regards Thilo -- 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] 8K recordsize bad on ZFS?
On Fri, May 7, 2010 at 8:09 PM, Josh Berkus wrote: > Jignesh, All: > > Most of our Solaris users have been, I think, following Jignesh's advice > from his benchmark tests to set ZFS page size to 8K for the data zpool. > However, I've discovered that this is sometimes a serious problem for > some hardware. > > For example, having the recordsize set to 8K on a Sun 4170 with 8 drives > recently gave me these appalling Bonnie++ results: > > Version 1.96 --Sequential Output-- --Sequential Input- > --Random- > Concurrency 4 -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- > --Seeks-- > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP > /sec %CP > db111 24G 260044 33 62110 17 89914 15 > 1167 25 > Latency 6549ms 4882ms 3395ms > 107ms > > I know that's hard to read. What it's saying is: > > Seq Writes: 260mb/s combined > Seq Reads: 89mb/s combined > Read Latency: 3.3s > > Best guess is that this is a result of overloading the array/drives with > commands for all those small blocks; certainly the behavior observed > (stuttering I/O, latency) is in line with that issue. > > Anyway, since this is a DW-like workload, we just bumped the recordsize > up to 128K and the performance issues went away ... reads up over 300mb/s. > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > Hi Josh, The 8K recommendation is for OLTP Applications.. So if you seen somewhere to use it for DSS/DW workload then I need to change it. DW Workloads require throughput and if they use 8K then they are limited by 8K x max IOPS which with 8 disk is about 120 (typical) x 8 SAS drives which is roughly about 8MB/sec.. (Prefetching with read drives and other optimizations can help it to push to about 24-30MB/sec with 8K on 12 disk arrays).. So yes that advice is typically bad for DSS.. And I believe I generally recommend them to use 128KB for DSS.So if you have seen the 8K for DSS let me know and hopefully if I still have access to it I can change it. However for OLTP you are generally want more IOPS with low latency which is what 8K provides (The smallest blocksize in ZFS). Hope this clarifies. -Jignesh -- 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 Bulk Delete
Hi Andy! Thanks a lot for your hints! Indeed the problem was on my side. Some Hibernate tuning solved the problem (and I was able to speedup the query using IN). The real problem was that Hibernate using unprepared queries if you create a native query, but prepares the query if you use JP-QL (very odd behavior). Thanks anyway for your help! Kind regards Thilo > I am going to guess the slow part is sending 1M different queries back and > forth from client to server. You could try batching them together: > > DELETE FROM table1 WHERE table2_id in (11242939, 1,2,3,4,5, 42); > > Also are you preparing the query? > > -Andy > > -- > Sent via pgsql-performance mailing list (pgsql-performa...@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
[PERFORM] Performance issues when the number of records are around 10 Million
Hi all, In my database application, I've a table whose records can reach 10M and insertions can happen at a faster rate like 100 insertions per second in the peak times. I configured postgres to do auto vacuum on hourly basis. I have frontend GUI application in CGI which displays the data from the database. When I try to get the last twenty records from the database, it takes around 10-15 mins to complete the operation.This is the query which is used: * select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780; * Can any one suggest me a better solution to improve the performance. Please let me know if you've any further queries. Thank you, Venu
Re: [PERFORM] Performance issues when the number of records are around 10 Million
venu madhav wrote: > When I try to get the last twenty records from the database, it > takes around 10-15 mins to complete the operation. Making this a little easier to read (for me, at least) I get this: select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780 ; Why the timestamp range, the order by, the limit, *and* the offset? On the face of it, that seems a bit confused. Not to mention that your ORDER BY has the same column twice. Perhaps that OFFSET is not needed? It is telling PostgreSQL that whatever results are generated based on the rest of the query, read through and ignore the first ten and a half million. Since you said you had about ten million rows, you wanted the last 20, and the ORDER by is DESCending, you're probably not going to get what you want. What, exactly, *is* it you want again? -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] Performance issues when the number of records are around 10 Million
First, are you sure you are getting autovacuum to run hourly? Autovacuum will only vacuum when certain configuration thresholds are reached. You can set it to only check for those thresholds every so often, but no vacuuming or analyzing will be done unless they are hit, regardless of how often autovacuum checks the tables. Whenever you are dealing with time series, the default thresholds are often insufficient, especially when you are especially interested in the last few records on a large table. What are your autovacuum configuration parameters? When were the two tables last autovacuum and analyzed, according to pg_stat_user_tables? Could you post the output of explain analyze of your query? Which default statistic collection parameters do you use? Have you changed them specifically for the tables you are using? Which version of Postgres are you running? Which OS? >>> venu madhav 05/11/10 3:47 AM >>> Hi all, In my database application, I've a table whose records can reach 10M and insertions can happen at a faster rate like 100 insertions per second in the peak times. I configured postgres to do auto vacuum on hourly basis. I have frontend GUI application in CGI which displays the data from the database. When I try to get the last twenty records from the database, it takes around 10-15 mins to complete the operation.This is the query which is used: select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780; Can any one suggest me a better solution to improve the performance. Please let me know if you've any further queries. Thank you, Venu
Re: [PERFORM] Performance issues when the number of records are around 10 Million
Venu, For starters, 1) You have used the e.cid twice in ORDER BY clause. 2) If you want last twenty records in the table matching the criteria of timestamp, why do you need the offset? 3) Do you have indexes on sig_id, signature and timestamp fields? If you do not get a good response after that, please post the EXPLAIN ANALYZE for the query. Thanks, Shrirang Chitnis Sr. Manager, Applications Development HOV Services Office: (866) 808-0935 Ext: 39210 shrirang.chit...@hovservices.com www.hovservices.com The information contained in this message, including any attachments, is attorney privileged and/or confidential information intended only for the use of the individual or entity named as addressee. The review, dissemination, distribution or copying of this communication by or to anyone other than the intended addressee is strictly prohibited. If you have received this communication in error, please immediately notify the sender by replying to the message and destroy all copies of the original message. From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of venu madhav Sent: Tuesday, May 11, 2010 2:18 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Performance issues when the number of records are around 10 Million Hi all, In my database application, I've a table whose records can reach 10M and insertions can happen at a faster rate like 100 insertions per second in the peak times. I configured postgres to do auto vacuum on hourly basis. I have frontend GUI application in CGI which displays the data from the database. When I try to get the last twenty records from the database, it takes around 10-15 mins to complete the operation.This is the query which is used: select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780; Can any one suggest me a better solution to improve the performance. Please let me know if you've any further queries. Thank you, Venu -- 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] 8K recordsize bad on ZFS?
> Sure, but bulk load + reandom selects is going to *guarentee* > fragmentatioon on a COW system (like ZFS, BTRFS, etc) as the selects > start to write out all the hint-bit-dirtied blocks in random orders... > > i.e. it doesn't take long to make an originally nicely continuous block > random I'm testing with DD and Bonnie++, though, which create their own files. For that matter, running an ETL procedure with a newly created database on both recordsizes was notably (2.5x) faster on the 128K system. So I don't think fragmentation is the difference. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] Performance issues when the number of records are around 10 Million
> * select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, > e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1, > e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE > s.sig_id = e.signature AND e.timestamp >= '1270449180' AND e.timestamp > < '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780; Anything with an offset that high is going to result in a sequential scan of most of the table. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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
