Thanks a lot Kevin. This is what i did to improve query performance. i recreated all the indexes on work_unit table and have been running vacuum analyze through cron job 3 times a day on two tables that are in the query. The query performance is between 2 to 3 seconds now. The strange thing i noticed is that just today at one time query performance came down to under 1 second and started using this query plan
http://explain.depesz.com/s/h5q8 But than cronjob started to vacuum analyze the work_unit and run table and after vacuum analyze got completed. it started using another query plan which made query slow and it went back to running between 2 to 3 seconds. This is query plan that is used now and is slower http://explain.depesz.com/s/AiG Why is that happening and what should i do to put it back to this query plan http://explain.depesz.com/s/h5q8 No changes have been made to the table structure or indexes between those two query plans My other questions is once the manual vaccum runs on the table like vacuum analyze work_unit and vacuum analyze run does that also cleans up the indexes or not. If not does that do any damages to the indexes or something We do have i/o problem during heavy loads because we share mount point from netapp across different machines which we are getting rid in few weeks by moving stuff to Amazon aws and than IO issues will go away with fast iops i have copied some more information. *Can you please recomend what values i should set for temp_buffer and work_memory and also what query should i run to check for bloat*. caesius=# SELECT version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) caesius=# show max_connections ; max_connections ----------------- 600 caesius=# select count(*) from pg_stat_activity; count ------- 165 #autovacuum = on log_autovacuum_min_duration = 10000 autovacuum_max_workers = 1 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 autovacuum_vacuum_scale_factor = 0.1 #autovacuum_analyze_scale_factor = 0.05 #autovacuum_freeze_max_age = 200000000 #autovacuum_vacuum_cost_delay = 20 #autovacuum_vacuum_cost_limit = -1 # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 1-10000 credits [caesius@clientdb01 tmp]$ cat /proc/meminfo MemTotal: 12582912 kB MemFree: 204748 kB Buffers: 4540 kB Cached: 9541024 kB SwapCached: 5324 kB Active: 5218556 kB Inactive: 6554684 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 12582912 kB LowFree: 204748 kB SwapTotal: 16777208 kB SwapFree: 16755516 kB Dirty: 36584 kB Writeback: 20 kB AnonPages: 2227364 kB Mapped: 1093452 kB Slab: 101396 kB PageTables: 206692 kB NFS_Unstable: 0 kB Bounce: 0 kB CommitLimit: 23068664 kB Committed_AS: 3796932 kB VmallocTotal: 34359738367 kB VmallocUsed: 9196 kB VmallocChunk: 34359729063 kB iostar -d -s 5 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 7.40 0.20 1.20 4.80 68.80 52.57 0.01 4.57 2.29 0.32 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 7.40 0.20 1.20 4.80 68.80 52.57 0.01 4.57 2.29 0.32 dm-0 0.00 0.00 0.20 8.60 4.80 68.80 8.36 0.04 4.09 0.36 0.32 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 3.80 0.00 0.60 0.00 35.20 58.67 0.00 6.67 5.33 0.32 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 3.80 0.00 0.60 0.00 35.20 58.67 0.00 6.67 5.33 0.32 dm-0 0.00 0.00 0.00 4.40 0.00 35.20 8.00 0.02 4.36 0.73 0.32 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 9.40 0.00 1.80 0.00 89.60 49.78 0.00 2.22 0.44 0.08 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 9.40 0.00 1.80 0.00 89.60 49.78 0.00 2.22 0.44 0.08 dm-0 0.00 0.00 0.00 11.20 0.00 89.60 8.00 0.03 3.00 0.07 0.08 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 12.38 5.79 13.17 106.99 204.39 16.42 0.20 10.78 1.85 3.51 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 12.38 5.79 13.17 106.99 204.39 16.42 0.20 10.78 1.85 3.51 dm-0 0.00 0.00 5.79 25.55 106.99 204.39 9.94 0.31 9.83 1.12 3.51 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvda 0.00 0.00 0.00 0.20 0.00 1.60 8.00 0.00 4.00 4.00 0.08 xvda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 xvda2 0.00 0.00 0.00 0.20 0.00 1.60 8.00 0.00 4.00 4.00 0.08 dm-0 0.00 0.00 0.00 0.20 0.00 1.60 8.00 0.00 4.00 4.00 0.08 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Thanks a lot On Mon, Jul 21, 2014 at 2:04 PM, John R Pierce <pie...@hogranch.com> wrote: > On 7/21/2014 1:51 PM, Kevin Grittner wrote: > >> The above might help, but I think the biggest problem may be your >> VM. You show very low disk I/O numbers, but a pretty significant >> fraction of the time waiting for I/O. The previously-suggested >> iostat output may help nail it down more specifically, but >> basically you seem to have a big problem with bandwidth for storage >> access. It's pretty disturbing to see lines in vmstat output which >> show zero disk in or out, but over 10% of CPU time waiting for >> storage?!? >> > > that would suggest to me a large number of VMs sharing a single SATA > drive, or similar. > > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
