[PERFORM] Investigating IO Saturation
I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1. During periods of high activity (reads, writes, and vacuums), we are seeing iostat reporting 100% disk usage. I have a feeling that the iostat numbers are misleading. I can make iostat usage jump from less than 10% to greater than 95% by running a single vacuum against a moderate sized table (no noticeable change in the other activity). Do I actually have a problem with IO? Whether I do or not, at what point should I start to be concerned about IO problems? If my understanding is correct, it should be based on the wait time. Here's the output of vmstat during heavy load (reads, writes, several daily vacuums and a nightly pg_dump). Wait times appear to be alright, but my understanding of when to start being concerned about IO starvation is foggy at best. vmstat 5 kthr memory page faultscpu - --- --- r b avm fre re pi po fr sr cy in sy cs us sy id wa 2 2 1548418 67130 0 0 0 141 99 0 1295 22784 13128 11 4 71 14 3 3 1548422 66754 0 0 0 2127 2965 0 2836 29981 25091 26 4 39 31 2 3 1548423 66908 0 0 0 2369 3221 0 3130 34725 28424 25 7 38 30 3 5 1548423 67029 0 0 0 2223 3097 0 2722 31885 25929 26 9 33 32 3 3 1548423 67066 0 0 0 2366 3194 0 2824 43546 36226 30 5 35 31 2 4 1548423 67004 0 0 0 2123 3236 0 2662 25756 21841 22 4 39 35 2 4 1548957 66277 0 0 0 1928 10322 0 2941 36340 29906 28 6 34 33 3 5 1549245 66024 0 0 0 2324 14291 0 2872 39413 25615 25 4 34 37 2 6 1549282 66107 0 0 0 1930 11189 0 2832 72062 32311 26 5 32 38 2 4 1549526 65855 0 0 0 2375 9278 0 2822 40368 32156 29 5 37 29 2 3 1548984 66227 0 0 0 1732 5065 0 2825 39240 30788 26 5 40 30 3 4 1549341 66027 0 0 0 2325 6453 0 2790 37567 30509 28 5 37 30 2 4 1549377 65789 0 0 0 1633 2731 0 2648 35533 27395 20 5 39 36 1 5 1549765 65666 0 0 0 2272 3340 0 2792 43002 34090 26 5 29 40 2 3 1549787 65646 0 0 0 1779 2679 0 2596 37446 29184 22 5 37 36 2 5 1548985 66263 0 0 0 2077 3086 0 2778 49579 39940 26 9 35 30 2 4 1548985 66473 0 0 0 2078 3093 0 2682 23274 18460 22 3 41 34 4 3 1548985 66263 0 0 0 2177 3344 0 2734 43029 35536 29 5 38 28 1 4 1548985 66491 0 0 0 1978 3215 0 2739 28291 22672 23 4 41 32 3 3 1548985 66422 0 0 0 1732 2469 0 2852 71865 30850 28 5 38 29 -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Investigating IO Saturation
Brad Nicholson wrote: I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1. During periods of high activity (reads, writes, and vacuums), we are seeing iostat reporting 100% disk usage. I have a feeling that the iostat numbers are misleading. I can make iostat usage jump from less than 10% to greater than 95% by running a single vacuum against a moderate sized table (no noticeable change in the other activity). Well that isn't surprising. Vacuum is brutal especially on 7.4 as that is pre background writer. What type of IO do you have available (RAID, SCSI?) Do I actually have a problem with IO? Whether I do or not, at what point should I start to be concerned about IO problems? If my understanding is correct, it should be based on the wait time. Here's the output of vmstat during heavy load (reads, writes, several daily vacuums and a nightly pg_dump). Wait times appear to be alright, but my understanding of when to start being concerned about IO starvation is foggy at best. vmstat 5 kthr memory page faultscpu - --- --- r b avm fre re pi po fr sr cy in sy cs us sy id wa 2 2 1548418 67130 0 0 0 141 99 0 1295 22784 13128 11 4 71 14 3 3 1548422 66754 0 0 0 2127 2965 0 2836 29981 25091 26 4 39 31 2 3 1548423 66908 0 0 0 2369 3221 0 3130 34725 28424 25 7 38 30 3 5 1548423 67029 0 0 0 2223 3097 0 2722 31885 25929 26 9 33 32 3 3 1548423 67066 0 0 0 2366 3194 0 2824 43546 36226 30 5 35 31 2 4 1548423 67004 0 0 0 2123 3236 0 2662 25756 21841 22 4 39 35 2 4 1548957 66277 0 0 0 1928 10322 0 2941 36340 29906 28 6 34 33 3 5 1549245 66024 0 0 0 2324 14291 0 2872 39413 25615 25 4 34 37 2 6 1549282 66107 0 0 0 1930 11189 0 2832 72062 32311 26 5 32 38 2 4 1549526 65855 0 0 0 2375 9278 0 2822 40368 32156 29 5 37 29 2 3 1548984 66227 0 0 0 1732 5065 0 2825 39240 30788 26 5 40 30 3 4 1549341 66027 0 0 0 2325 6453 0 2790 37567 30509 28 5 37 30 2 4 1549377 65789 0 0 0 1633 2731 0 2648 35533 27395 20 5 39 36 1 5 1549765 65666 0 0 0 2272 3340 0 2792 43002 34090 26 5 29 40 2 3 1549787 65646 0 0 0 1779 2679 0 2596 37446 29184 22 5 37 36 2 5 1548985 66263 0 0 0 2077 3086 0 2778 49579 39940 26 9 35 30 2 4 1548985 66473 0 0 0 2078 3093 0 2682 23274 18460 22 3 41 34 4 3 1548985 66263 0 0 0 2177 3344 0 2734 43029 35536 29 5 38 28 1 4 1548985 66491 0 0 0 1978 3215 0 2739 28291 22672 23 4 41 32 3 3 1548985 66422 0 0 0 1732 2469 0 2852 71865 30850 28 5 38 29 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Investigating IO Saturation
Joshua D. Drake wrote: Brad Nicholson wrote: I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1. During periods of high activity (reads, writes, and vacuums), we are seeing iostat reporting 100% disk usage. I have a feeling that the iostat numbers are misleading. I can make iostat usage jump from less than 10% to greater than 95% by running a single vacuum against a moderate sized table (no noticeable change in the other activity). Well that isn't surprising. Vacuum is brutal especially on 7.4 as that is pre background writer. What type of IO do you have available (RAID, SCSI?) Data LUN is RAID 10, wal LUN is RAID 1. -- Brad Nicholson 416-673-4106[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Investigating IO Saturation
Brad Nicholson [EMAIL PROTECTED] writes: I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1. During periods of high activity (reads, writes, and vacuums), we are seeing iostat reporting 100% disk usage. I have a feeling that the iostat numbers are misleading. I can make iostat usage jump from less than 10% to greater than 95% by running a single vacuum against a moderate sized table (no noticeable change in the other activity). That's not particularly surprising, and I see no reason to think that iostat is lying to you. More recent versions of PG include parameters that you can use to throttle vacuum's I/O demand ... but unthrottled, it's definitely an I/O hog. The vmstat numbers suggest that vacuum is not completely killing you, but you could probably get some improvement in foreground query performance by throttling it back. There are other good reasons to consider an update, anyway. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Investigating IO Saturation
[EMAIL PROTECTED] (Tom Lane) writes: Brad Nicholson [EMAIL PROTECTED] writes: I'm investigating a potential IO issue. We're running 7.4 on AIX 5.1. During periods of high activity (reads, writes, and vacuums), we are seeing iostat reporting 100% disk usage. I have a feeling that the iostat numbers are misleading. I can make iostat usage jump from less than 10% to greater than 95% by running a single vacuum against a moderate sized table (no noticeable change in the other activity). That's not particularly surprising, and I see no reason to think that iostat is lying to you. More recent versions of PG include parameters that you can use to throttle vacuum's I/O demand ... but unthrottled, it's definitely an I/O hog. I believe it's 7.4 where the cost-based vacuum parameters entered in, so that would, in principle, already be an option. [rummaging around...] Hmm There was a patch for 7.4, but it's only standard as of 8.0... The vmstat numbers suggest that vacuum is not completely killing you, but you could probably get some improvement in foreground query performance by throttling it back. There are other good reasons to consider an update, anyway. I'd have reservations about throttling it back because that would lead to VACUUMs running, and holding transactions open, for 6 hours instead of 2. That is consistent with benchmarking; there was a report of the default policy cutting I/O load by ~80% at the cost of vacuums taking 3x as long to complete. The real answer is to move to 8.x, where VACUUM doesn't chew up shared memory cache as it does in 7.4 and earlier. But in the interim, we need to make sure we tilt over the right windmills, or something of the sort :-). -- output = reverse(gro.gultn @ enworbbc) http://www3.sympatico.ca/cbbrowne/linuxxian.html Women and cats will do as they please, and men and dogs should relax and get used to the idea. -- Robert A. Heinlein ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Investigating IO Saturation
On Tue, Jan 24, 2006 at 02:43:59PM -0500, Chris Browne wrote: I believe it's 7.4 where the cost-based vacuum parameters entered in, so that would, in principle, already be an option. [rummaging around...] Hmm There was a patch for 7.4, but it's only standard as of 8.0... And it doesn't work very well without changes to buffering. You need both pieces to get it to work. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Inconsistant query plan
Hi, We are running Postgresql 8.1, and getting dramatically inconsistant results after running VACUUM ANALYZE. Sometimes after analyzing the database, the query planner chooses a very efficient plan (15 rows, 4.744 ms), and sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated query: SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid = t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ... In the efficient plan, t2 is joined to t3 t4 before being joined to t1. The inefficient plan joins t1 to t2 before joining to the other tables. We've experimented with different settings, such as shared_buffers max_fsm_pages, to no avail. Anybody have a suggestion for getting the efficient plan to execute consistantly? If you'd like to see the actual query query plans let me know. Best Regards, Dan ---(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] Inconsistant query plan
On Tue, 2006-01-24 at 17:15, Daniel Gish wrote: Hi, We are running Postgresql 8.1, and getting dramatically inconsistant results after running VACUUM ANALYZE. Sometimes after analyzing the database, the query planner chooses a very efficient plan (15 rows, 4.744 ms), and sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated query: SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid = t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ... In the efficient plan, t2 is joined to t3 t4 before being joined to t1. The inefficient plan joins t1 to t2 before joining to the other tables. We've experimented with different settings, such as shared_buffers max_fsm_pages, to no avail. Anybody have a suggestion for getting the efficient plan to execute consistantly? If you'd like to see the actual query query plans let me know. Have you adjusted the stats target for that column? See \h alter table in psql for the syntax for that. Then run analyze again. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Inconsistant query plan
On Tue, Jan 24, 2006 at 04:15:57PM -0700, Daniel Gish wrote: We are running Postgresql 8.1, and getting dramatically inconsistant results after running VACUUM ANALYZE. Sometimes after analyzing the database, the query planner chooses a very efficient plan (15 rows, 4.744 ms), and sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated query: SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid = t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ... How abbreviated is that example? Are you actually joining more tables than that? In another recent thread varying plans were attributed to exceeding geqo_threshold: http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php Does your situation look similar? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Inconsistant query plan
Hi, Thanks for your response. The actual query is below; the joins are only 4 deep. Adjusting the stats target did help, but not dramatically. EFFICIENT PLAN: # explain analyze SELECT ev.eid FROM events ev INNER JOIN (events_join ej INNER JOIN (groups_join gj INNER JOIN groups g ON gj.gid = g.gid) ON ej.gid = gj.gid) ON ev.eid = ej.eid WHERE ev.status 0 AND ej.type_id = 1 AND g.deleted = 'f' AND g.deactivated != 't' AND ev.type_id = 0 AND gj.uid=3 AND ev.timestart BETWEEN '01/23/2006'::timestamp AND '02/23/2006'::timestamp + '1 day - 1 minute'; QUERY PLAN --- Nested Loop (cost=0.00..8370.41 rows=25 width=4) (actual time=4.510..4.510 rows=0 loops=1) - Nested Loop (cost=0.00..6124.63 rows=673 width=4) (actual time=0.132..3.116 rows=92 loops=1) - Nested Loop (cost=0.00..70.95 rows=8 width=8) (actual time=0.080..2.226 rows=19 loops=1) - Index Scan using groups_join_uid_idx on groups_join gj (cost=0.00..16.27 rows=11 width=4) (actual time=0.019..0.471 rows=196 loops=1) Index Cond: (uid = 3) - Index Scan using groups_pkey on groups g (cost=0.00..4.96 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=196) Index Cond: (outer.gid = g.gid) Filter: ((NOT deleted) AND (deactivated true)) - Index Scan using events_join_gid_idx on events_join ej (cost=0.00..752.45 rows=341 width=8) (actual time=0.010..0.027 rows=5 loops=19) Index Cond: (ej.gid = outer.gid) Filter: (type_id = 1) - Index Scan using events_pkey on events ev (cost=0.00..3.32 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=92) Index Cond: (ev.eid = outer.eid) Filter: ((status 0) AND (type_id = 0) AND (timestart = '2006-01-23 00:00:00'::timestamp without time zone) AND (timestart = '2006-02-23 23:59:00'::timestamp without time zone)) Total runtime: 4.744 ms (15 rows) INEFFICIENT PLAN: # explain analyze SELECT ev.eid FROM events ev INNER JOIN (events_join ej INNER JOIN (groups_join gj INNER JOIN groups g ON gj.gid = g.gid) ON ej.gid = g.gid) ON ev.eid = ej.eid WHERE ev.status 0 AND ej.type_id = 1 AND g.deleted = 'f' AND g.deactivated != 't' AND ev.type_id = 0 AND gj.uid=3 AND ev.timestart BETWEEN '01/23/2006'::timestamp AND '02/23/2006'::timestamp + '1 day - 1 minute'; QUERY PLAN --- Nested Loop (cost=978.19..37161.81 rows=133 width=4) (actual time=2511.676..2511.676 rows=0 loops=1) - Merge Join (cost=978.19..22854.00 rows=4244 width=4) (actual time=1718.420..2510.128 rows=92 loops=1) Merge Cond: (outer.gid = inner.gid) - Index Scan using events_join_gid_idx on events_join ej (cost=0.00..23452.59 rows=740598 width=8) (actual time=0.014..1532.447 rows=626651 loops=1) Filter: (type_id = 1) - Sort (cost=978.19..978.47 rows=113 width=8) (actual time=2.371..2.540 rows=101 loops=1) Sort Key: g.gid - Nested Loop (cost=0.00..974.33 rows=113 width=8) (actual time=0.078..2.305 rows=19 loops=1) - Index Scan using groups_join_uid_idx on groups_join gj (cost=0.00..182.65 rows=159 width=4) (actual time=0.017..0.485 rows=196 loops=1) Index Cond: (uid = 3) - Index Scan using groups_pkey on groups g (cost=0.00..4.97 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=196) Index Cond: (outer.gid = g.gid) Filter: ((NOT deleted) AND (deactivated true)) - Index Scan using events_pkey on events ev (cost=0.00..3.36 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=92) Index Cond: (ev.eid = outer.eid) Filter: ((status 0) AND (type_id = 0) AND (timestart = '2006-01-23 00:00:00'::timestamp without time zone) AND (timestart = '2006-02-23 23:59:00'::timestamp without time zone)) Total runtime: 2511.920 ms (17 rows) Regards, Dan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
