Re: [PERFORM] Improving performance on system catalog
2007/3/28, chris smith [EMAIL PROTECTED]: Total runtime: 13.844 ms Why bother? Because faster could be better in a very busy system. -- Daniel Cristian Cruz Analista de Sistemas
Re: [PERFORM] Improving performance on system catalog
Daniel Cristian Cruz wrote: Hi all. I would like to speed up this query: EXPLAIN ANALYZE SELECT relid,schemaname,relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del FROM pg_stat_user_tables; Although optimizing for 13ms is a little silly imo, you could probably gain from calling the specific query underneath instead of calling the view pg_stat_user_tables. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Improving performance on system catalog
Daniel Cristian Cruz [EMAIL PROTECTED] writes: 2007/3/28, chris smith [EMAIL PROTECTED]: Total runtime: 13.844 ms Why bother? Because faster could be better in a very busy system. If you are concerned about global performance improvement, quit worrying about this micro-detail and get yourself onto a more modern Postgres. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Improving performance on system catalog
2007/3/28, Tom Lane [EMAIL PROTECTED]: Daniel Cristian Cruz [EMAIL PROTECTED] writes: 2007/3/28, chris smith [EMAIL PROTECTED]: Total runtime: 13.844 ms Why bother? Because faster could be better in a very busy system. If you are concerned about global performance improvement, quit worrying about this micro-detail and get yourself onto a more modern Postgres. Got it. We just planned move to 8.2.3 in about two weeks. -- Daniel Cristian Cruz Analista de Sistemas
[PERFORM] Shared buffers, db transactions commited, and write IO on Solaris
Greetings, We've recently made a couple changes to our system that have resulted in a drastic increase in performance as well as some very confusing changes to the database statistics, specifically pg_stat_database.xact_commit. Here's the details: OS: Solaris10 x86 Server: Sunfire X4100, 8GB Memory, 2 Dual Core Opterons Postgres 8.2.3 Disk array: Sun STK 6130 + CSM100 SATA tray, dual channel MPXIO, 15k drives, RAID5 across 14 disks WAL logs on SATA RAID10 SAN architecture, 2 brocade FABRIC switches The changes we made were: Increase shared buffers from 15 to 20 Set the disk mount for the data directory to use forcedirectio (added that mount option that to the /etc/vfstab entry (ufs fs)) So, the reason we did this was that for months now we'd been experiencing extremely high IO load from both the perspective of the OS and the database, specifically where writes were concerned. During peak hourse it wasn't unheard of for pg_stat_database to report anywhere from 50 to 100 transactions committed in an hour. iostat's %b (disk busy) sat at 100% for longer than we'd care to think about with the wait percentage going from a few percent on up to 50% at times and the cpu load almost never rising from around a 2 avg., i.e. we were extremely IO bound in all cases. As soon as we restarted postgres after making those changes the IO load was gone. While we the number and amount of disk reads have stayed pretty much the same and the number of disk writes have stayed the same, the amount of data being written has dropped by about a factor of 10, which is huge. The cpu load shot way up to around a 20 avg. and stayed that way up and stayed that way for about two days (we're thinking that was autovacuum catching up). In addition, and this is the truly confusing part, the xact_commit and xact_rollback stats from pg_stat_database both dropped by an order of magnitude (another factor of 10). So, we are now doing 5 to 10 commits per hour during peak hours. So, where were all of those extra transactions coming from? Are transactions reported on in pg_stat_database anything but SQL statements? What was causing all of the excess(?!) data being written to the disk (it seems that there's a 1:1 correspondence between the xacts and volume of data being written)? Given that we have the bgwriter on, could it have been the culprit and one of the changes allowed it to now operate more efficiently and/or correctly? erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r)
[PERFORM] Planner doing seqscan before indexed join
8.0.3 - Linux 2.6.18.. Freshly vacuumed and analyzed This database has been humming along fine for a while now, but I've got one of those sticky queries that is taking much too long to finish. After some digging, I've found that the planner is choosing to apply a necessary seq scan to the table. Unfortunately, it's scanning the whole table, when it seems that it could have joined it to a smaller table first and reduce the amount of rows it would have to scan dramatically ( 70 million to about 5,000 ). The table eventactivity has about 70million rows in it, index on incidentid The table keyword_incidents is a temporary table and has incidentid as its primary key. It contains 5125 rows. eventmain and eventgeo both have 2.1 million. My hope is that I can convince the planner to do the join to keyword_incidents *first* and then do the seq scan for the LIKE condition. Instead, it seems that it's seqscanning the whole 70 million rows first and then doing the join, which takes a lot longer than I'd like to wait for it. Or, maybe I'm misreading the explain output? Thanks again -Dan - Here's the query: explain analyze select * from keyword_incidents, eventactivity, eventmain, eventgeo where eventmain.incidentid = keyword_incidents.incidentid and eventgeo.incidentid = keyword_incidents.incidentid and eventactivity.incidentid = keyword_incidents.incidentid and ( recordtext like '%JOSE CHAVEZ%' ) order by eventmain.entrydate limit 1; --- Limit (cost=2388918.07..2388918.08 rows=1 width=455) (actual time=81771.186..81771.292 rows=26 loops=1) - Sort (cost=2388918.07..2388918.08 rows=1 width=455) (actual time=81771.180..81771.215 rows=26 loops=1) Sort Key: eventmain.entrydate - Nested Loop (cost=0.00..2388918.06 rows=1 width=455) (actual time=357.389..81770.982 rows=26 loops=1) - Nested Loop (cost=0.00..2388913.27 rows=1 width=230) (actual time=357.292..81767.385 rows=26 loops=1) - Nested Loop (cost=0.00..2388909.33 rows=1 width=122) (actual time=357.226..81764.501 rows=26 loops=1) - Seq Scan on eventactivity (cost=0.00..2388874.46 rows=7 width=84) (actual time=357.147..81762.582 rows=27 loops=1) Filter: ((recordtext)::text ~~ '%JOSE CHAVEZ%'::text) - Index Scan using keyword_incidentid_pkey on keyword_incidents (cost=0.00..4.97 rows=1 width=38) (actual time=0.034..0.036 rows=1 loops=27) Index Cond: ((outer.incidentid)::text = (keyword_incidents.incidentid)::text) - Index Scan using eventgeo_incidentid_idx on eventgeo (cost=0.00..3.93 rows=1 width=108) (actual time=0.076..0.081 rows=1 loops=26) Index Cond: ((outer.incidentid)::text = (eventgeo.incidentid)::text) - Index Scan using eventmain_incidentid_idx on eventmain (cost=0.00..4.78 rows=1 width=225) (actual time=0.069..0.075 rows=1 loops=26) Index Cond: ((outer.incidentid)::text = (eventmain.incidentid)::text) Total runtime: 81771.529 ms (15 rows) ---(end of broadcast)--- TIP 6: explain analyze is your friend
