Re: Sudden drop in DBb performance

From: Gerhard Wohlgenannt <wohlg(at)ai(dot)wu(dot)ac(dot)at>
To: Tomas Vondra <tv(at)fuzzy(dot)cz>
Cc: Andy Colson <andy(at)squeakycode(dot)net>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org, Heinz-Peter Lang <heinz(at)langatium(dot)net>, Gerhard Wohlgenannt <wohlg(at)ai(dot)wu-wien(dot)ac(dot)at>, "Weichselbraun, Albert" <albert(dot)weichselbraun(at)wu(dot)ac(dot)at>
Subject: Re: Sudden drop in DBb performance
Date: 2011-09-06 08:55:57
Message-ID: 4E65E01D.9020305@ai.wu.ac.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


> That's why I love dstat, just do this
>
> $ dstat -C 0,1,2,3,4,5,6,7
>
> and you know all you need.

dstat looks like a very nice tool, results below ..
(now the system load seems a bit lower then before when generating
results for vmstat and iostat)
>> Good catch, thanks Scott.
> Yes, good catch.
>
> Still, this does not explain why the queries were running fast before, and
> why the RAID array is so sluggish. Not to mention that we don't know what
> were the conditions when collecting those numbers (were the VMs off or
> running?).
>
the VMs were running. they are in something like production use, so i
shouldn't just turn them off .. :-)
and the processes in the VMs cause a big portion of the DB load, so
turning them off would distort the results ...

and thanks again for all the replies!!! :-)

~# dstat -C 0,1,2,3,4,5,6,7
-------cpu0-usage--------------cpu1-usage--------------cpu2-usage--------------cpu3-usage--------------cpu4-usage--------------cpu5-usage--------------cpu6-usage--------------cpu7-usage------
-dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq
siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq:usr sys idl wai hiq
siq:usr sys idl wai hiq siq:usr sys idl wai hiq siq| read writ| recv
send| in out | int csw
7 1 75 17 0 0: 4 5 84 7 0 0: 5 3 80 12 0
0: 4 3 85 9 0 0: 7 2 75 16 0 0: 4 2 87 8 0
0: 7 2 75 16 0 0: 4 1 87 8 0 0|5071k 2578k| 0 0
|9760B 9431B|2468 4126
0 0 98 2 0 0: 0 0 98 2 0 0: 6 2 22 71 0
0: 5 0 76 19 0 0: 0 12 82 6 0 0: 3 7 88 2 0
0: 3 1 84 12 0 0: 2 0 94 4 0 0|5160k 1376k| 60k
225k| 0 0 |2101 3879
11 1 84 4 0 0: 2 0 93 6 0 0: 3 4 72 22 0
0: 2 2 92 3 0 1: 10 13 22 54 0 1: 6 7 75 12 0
0: 3 0 87 10 0 0: 12 0 81 7 0 0|6640k 1683k| 140k
240k| 0 0 |2860 4617
1 1 29 68 0 1: 12 0 80 8 0 0: 6 0 78 16 0
1: 3 1 80 16 0 0: 14 14 57 16 0 0: 0 11 78 12 0
0: 9 1 83 7 0 0: 0 0 96 4 0 0|4448k 1266k| 102k
336k| 0 0 |2790 4645
0 0 89 11 0 0: 1 0 98 1 0 0: 14 0 57 29 0
0: 1 1 89 9 0 0: 1 15 41 43 0 0: 3 15 75 7 0
0: 3 2 60 35 0 0: 0 0 95 5 0 0| 18M 1622k| 97k
285k| 0 0 |3303 4764
0 0 96 4 0 0: 0 0 99 0 0 1: 1 2 14 83 0
0: 1 25 17 57 0 0: 1 0 87 12 0 0: 1 0 19 80 0
0: 3 3 0 94 0 0: 0 0 48 52 0 0|1320k 19M| 40k
113k| 0 0 |2909 4709
1 0 63 36 0 0: 5 2 88 5 0 0: 34 2 0 63 1
0: 8 8 72 12 0 0: 0 9 85 6 0 0: 1 2 84 13 0
0: 2 1 60 37 0 0: 1 1 62 36 0 0|9160k 5597k| 52k
143k| 32k 0 |2659 4650
4 0 43 53 0 0: 2 0 93 5 0 0: 9 0 63 28 0
0: 3 1 89 7 0 0: 2 9 72 16 0 1: 0 13 81 6 0
0: 9 1 52 38 0 0: 3 0 84 13 0 0|4980k 1358k| 106k
239k| 0 0 |2993 5158
2 1 90 7 0 0: 2 0 95 3 0 0: 2 3 82 13 0
0: 0 0 87 13 0 0: 6 10 32 52 0 0: 2 10 82 6 0
0: 5 0 86 9 0 0: 10 5 81 4 0 0|4376k 2949k| 119k
295k| 0 0 |2729 4630
1 0 93 6 0 0: 2 0 91 6 1 0: 15 4 71 11 0
0: 7 2 90 1 0 0: 13 10 12 65 0 0: 2 13 41 45 0
0: 1 0 97 2 0 0: 1 0 94 5 0 0|3896k 15M| 87k
242k| 0 0 |2809 5514
2 0 98 0 0 0: 0 0 73 27 0 0: 0 0 100 0 0
0: 2 1 29 68 0 0: 4 5 0 92 0 0: 2 5 92 2 0
0: 0 0 100 0 0 0: 1 0 77 22 0 0| 172k 19M| 40k
127k| 0 0 |2221 4069
0 0 48 52 0 0: 0 0 97 3 0 0: 0 0 92 8 0
0: 3 0 91 6 0 0: 2 10 10 78 0 0: 4 10 81 6 0
0: 2 0 29 69 0 0: 1 0 26 73 0 0| 652k 6931k| 66k
233k| 0 0 |2416 4389
6 2 72 21 0 0: 3 1 86 10 0 0: 7 0 60 34 0
0: 2 2 91 6 0 0: 1 13 78 9 0 0: 2 8 84 6 0
0: 2 0 79 19 0 0: 0 2 87 11 0 0|2784k 1456k| 96k
206k| 0 0 |2854 5226
9 4 50 37 0 0: 3 3 84 10 0 0: 4 0 84 12 0
0: 2 3 86 9 0 0: 10 2 73 15 0 0: 3 5 84 8 0
0: 8 4 81 6 0 0: 1 2 84 13 0 0|2952k 1374k| 133k
305k| 0 0 |3249 5076
9 1 78 13 0 0: 4 4 83 9 0 0: 3 1 68 28 0
0: 3 3 82 12 0 0: 9 0 64 26 0 1: 2 1 83 13 0
1: 9 3 63 24 0 1: 3 1 91 5 0 0|3648k 1420k| 188k
444k| 0 0 |3560 5981
3 1 63 33 0 0: 0 1 86 13 0 0: 1 0 67 32 0
0: 1 2 84 12 0 1: 4 2 49 45 0 0: 6 3 82 9 0
0: 1 1 93 5 0 0: 1 1 91 7 0 0|2980k 1385k| 181k
457k| 0 0 |3023 5230
3 5 90 2 0 0: 3 9 84 4 0 0: 2 2 55 41 0
0: 21 3 69 7 0 0: 1 3 76 20 0 0: 2 1 93 4 0
0: 1 3 67 29 0 0: 0 1 93 6 0 0|2796k 1359k| 104k
237k| 0 0 |2339 4598
6 5 74 15 0 0: 0 5 87 8 0 0: 4 0 91 5 0
0: 0 0 98 2 0 0: 11 0 74 16 0 0: 3 1 87 9 0
0: 26 4 53 17 0 0: 0 1 92 7 0 0|1920k 1401k| 107k
278k| 0 0 |2352 4480
2 0 91 7 0 0: 1 10 84 5 0 0: 4 0 93 3 0
0: 3 2 93 2 0 0: 6 2 84 8 0 0: 1 0 97 2 0
0: 2 0 85 13 0 0: 3 2 89 6 0 0|1508k 1397k| 134k
313k| 0 0 |2374 4547
2 0 74 24 0 0: 0 0 97 3 0 0: 1 0 97 2 0
0: 3 2 95 0 0 0: 1 0 96 3 0 0: 4 2 91 4 0
0: 0 5 89 6 0 0: 3 1 91 6 0 0|1464k 1464k| 68k
143k| 0 0 |1839 3950
4 0 91 5 0 0: 5 4 89 3 0 0: 1 0 98 1 0
0: 0 1 97 2 0 0: 2 2 68 28 0 0: 1 0 91 8 0
0: 2 8 70 20 0 0: 1 5 91 2 0 1|1060k 1903k| 133k
142k| 0 0 |2358 4720
3 0 91 6 0 0: 4 3 88 5 0 0: 1 0 95 4 0
0: 1 2 94 3 0 0: 3 1 53 43 0 0: 3 2 93 2 0
0: 0 2 86 13 0 0: 0 4 89 7 0 0|1580k 1332k| 91k
273k| 0 0 |2249 4564
4 0 79 16 0 1: 3 6 84 8 0 0: 1 0 92 7 0
0: 2 3 88 7 0 0: 5 2 86 7 0 0: 2 4 90 3 0
1: 4 1 81 14 0 0: 7 3 81 10 0 0|1128k 1343k| 122k
263k| 0 0 |3262 5503
0 2 60 38 0 0: 3 5 90 3 0 0: 0 11 78 12 0
0: 1 5 91 3 0 0: 2 4 90 4 0 0: 4 2 89 5 0
0: 3 0 94 3 0 0: 4 2 87 8 0 0|1252k 1255k| 329k
494k| 0 0 |3223 5661
2 0 84 13 0 1: 3 6 84 7 0 0: 3 7 83 8 0
0: 5 15 72 8 0 0: 2 1 96 1 0 0: 1 2 93 4 0
0: 4 1 65 30 0 0: 1 0 89 10 0 0|1660k 2513k| 148k
460k| 0 0 |2597 5129
1 2 92 5 0 0: 0 1 41 58 0 0: 10 5 83 2 0
0: 3 18 78 2 0 0: 3 0 93 4 0 0: 3 0 97 0 0
0: 4 0 80 17 0 0: 2 0 91 7 0 0| 652k 15M| 72k
198k| 0 0 |2340 6382
0 1 88 11 0 0: 3 3 88 7 0 0: 2 1 80 17 0
0: 0 13 75 13 0 0: 1 1 73 25 0 0: 1 1 93 5 0
0: 0 1 94 5 0 0: 1 0 86 13 0 0|1568k 1155k| 68k
166k| 0 0 |2348 4738
3 0 54 43 0 0: 4 9 82 6 0 0: 2 0 88 11 0
0: 2 5 88 6 0 0: 1 0 88 11 0 0: 2 0 93 5 0
0: 3 1 80 17 0 0: 0 0 88 12 0 0|1200k 1319k| 113k
233k| 0 0 |2660 5168
0 1 91 8 0 0: 4 6 87 4 0 0: 1 0 72 27 0
0: 2 2 70 26 0 0: 2 0 85 13 0 0: 1 0 94 5 0
0: 2 0 95 3 0 0: 1 1 89 9 0 0|1032k 1346k| 119k
234k| 0 0 |2398 4808
0 1 73 26 0 0: 3 5 83 10 0 0: 1 2 96 1 0
0: 4 0 91 5 0 0: 2 2 90 6 0 0: 2 0 94 4 0
0: 3 4 70 23 0 0: 3 6 85 6 0 0|1272k 1522k| 117k
279k| 0 0 |2739 5237
1 0 56 43 0 0: 0 2 86 12 0 0: 2 1 89 8 0
0: 2 2 92 4 0 0: 7 1 82 10 0 0: 4 1 92 3 0
0: 5 2 80 13 0 0: 4 4 87 6 0 0|1080k 1637k| 141k
345k| 0 0 |2631 5142
0 0 65 35 0 0: 0 1 89 10 0 0: 2 0 84 14 0
0: 1 3 92 4 0 0: 0 0 89 11 0 0: 2 0 91 7 0
0: 2 5 87 6 0 0: 2 7 87 5 0 0|1012k 1412k| 95k
215k| 0 0 |2304 4762
1 0 93 6 0 0: 3 1 95 1 0 0: 2 1 97 0 0
0: 3 3 91 3 0 0: 2 0 72 26 0 0: 1 0 93 6 0
0: 0 7 70 23 0 0: 2 8 82 8 0 0|1152k 1548k| 101k
225k| 0 0 |2334 4706
1 0 93 6 0 0: 2 3 93 2 0 0: 5 2 91 3 0
0: 6 3 91 1 0 0: 5 2 85 8 0 0: 2 1 91 6 0
0: 1 5 60 34 0 0: 2 6 86 6 0 0|1096k 1553k| 126k
289k| 0 0 |2471 4766
0 0 63 37 0 0: 0 0 90 10 0 0: 1 0 99 0 0
0: 0 0 93 7 0 0: 1 0 86 13 0 0: 2 2 87 9 0
0: 0 7 70 24 0 0: 2 3 93 3 0 0|1256k 1262k| 110k
277k| 0 0 |2281 4534
0 0 24 76 0 0: 2 1 81 16 0 0: 1 3 44 52 0
0: 2 2 65 31 0 0: 1 2 75 22 0 0: 1 0 94 5 0
0: 0 4 17 79 0 0: 5 3 93 0 0 0| 240k 19M| 83k
200k| 0 0 |2388 4131
1 0 76 23 0 0: 1 1 94 4 0 0: 5 3 81 11 0
0: 2 4 80 14 0 0: 2 1 56 41 0 0: 0 0 77 23 0
0: 3 7 74 17 0 0: 1 5 90 5 0 0|1108k 3127k| 113k
262k| 0 0 |2584 4930
2 1 90 8 0 0: 2 0 93 5 0 0: 2 1 75 22 0
0: 2 1 83 15 0 0: 0 1 79 20 0 0: 0 0 92 7 0
1: 2 6 77 14 0 1: 2 8 87 4 0 0|1220k 1358k| 152k
318k| 0 12k|2590 5104
3 0 81 16 0 0: 3 1 88 9 0 0: 3 1 93 3 0
0: 2 3 90 5 0 0: 1 0 93 6 0 0: 4 1 92 4 0
0: 5 13 37 46 0 0: 2 7 81 10 0 0|1256k 1437k| 167k
336k| 0 0 |2734 5356
0 0 93 7 0 0: 1 2 88 9 0 0: 2 0 92 5 0
1: 1 2 94 3 0 0: 0 0 50 50 0 0: 0 0 99 1 0
0: 3 11 81 5 0 0: 3 7 86 4 0 0|1108k 1448k| 109k
225k| 0 0 |2081 4316
3 0 42 55 0 0: 0 3 92 5 0 0: 0 0 100 0 0
0: 1 0 92 7 0 0: 3 1 69 27 0 0: 1 0 66 33 0
0: 4 7 56 33 0 0: 3 3 61 33 0 0| 720k 17M| 104k
290k| 0 0 |2300 4168
0 2 59 39 0 0: 3 7 75 15 0 0: 0 6 89 5 0
0: 12 9 75 4 0 0: 1 0 81 18 0 0: 2 1 94 3 0
0: 1 8 56 35 0 0: 3 5 57 35 0 1| 428k 8241k| 96k
259k| 0 124k|2082 5154
1 1 90 8 0 0: 2 2 46 49 0 0: 1 0 93 6 0
0: 9 4 83 4 0 0: 0 0 95 5 0 0: 1 0 98 1 0
0: 6 7 76 11 0 0: 1 4 85 10 0 0| 592k 14M| 82k
194k| 0 0 |2195 5839
4 1 82 13 0 0: 3 0 87 10 0 0: 4 3 81 12 0
0: 0 0 82 18 0 0: 1 1 88 11 0 0: 1 0 90 9 0
0: 5 7 64 24 0 0: 5 3 86 6 0 0|1876k 1273k| 145k
323k| 0 0 |2544 4949
0 1 66 33 0 0: 5 1 89 6 0 0: 0 1 88 12 0
0: 1 3 82 14 0 0: 1 0 86 12 0 1: 1 1 88 10 0
0: 8 3 82 8 0 0: 6 7 84 3 0 0|2544k 1259k| 125k
280k| 0 0 |2487 4776
0 1 85 14 0 0: 0 0 100 0 0 0: 3 0 95 2 0
0: 5 3 91 2 0 0: 0 0 94 6 0 0: 6 1 84 10 0
0: 1 10 50 39 0 0: 4 5 82 9 0 0| 896k 1378k| 92k
181k| 0 0 |2452 4823
0 0 94 6 0 0: 2 1 97 0 0 0: 3 0 94 3 0
0: 0 0 89 11 0 0: 2 3 83 12 0 0: 2 2 83 13 0
0: 2 9 50 40 0 0: 5 10 66 19 0 0| 848k 1237k| 98k
204k| 0 0 |2309 4733
3 0 93 3 0 1: 2 9 83 6 0 0: 1 0 98 1 0
0: 1 3 96 0 0 0: 3 2 84 12 0 0: 1 0 88 11 0
0: 1 0 39 60 0 0: 0 4 88 8 0 0| 992k 1113k| 99k
212k| 0 0 |2318 4696
10 0 87 3 0 0: 1 8 90 2 0 0: 2 0 91 7 0
0: 4 2 90 4 0 0: 4 2 78 16 0 0: 3 0 89 9 0
0: 4 0 90 7 0 0: 0 0 99 1 0 0| 964k 1165k| 119k
291k| 0 0 |2505 4842
8 3 83 6 0 0: 7 0 80 13 0 0: 2 6 67 25 0
0: 4 6 80 10 0 0: 2 1 77 20 0 0: 2 1 87 8 0
2: 2 1 82 16 0 0: 1 0 95 4 0 0| 888k 1154k| 158k
331k| 0 0 |2759 5495
6 0 91 3 0 0: 4 1 93 2 0 0: 0 10 85 5 0
0: 1 10 85 5 0 0: 2 1 37 60 0 0: 0 0 88 12 0
0: 5 0 83 12 0 0: 2 1 88 9 0 0| 992k 1143k| 149k
300k| 0 0 |2606 5054
5 0 75 19 0 1: 7 1 81 11 0 0: 4 11 73 12 0
0: 6 16 62 16 0 0: 5 1 88 6 0 0: 6 1 84 8 0
0: 1 4 71 24 0 1: 1 2 91 6 0 0|1372k 1109k| 193k
434k| 0 0 |3216 5887
3 1 90 6 0 0: 10 3 80 7 0 0: 0 2 92 6 0
0: 2 0 94 4 0 0: 0 2 65 33 0 0: 2 0 95 3 0
0: 0 7 76 18 0 0: 5 6 79 10 0 0|1044k 1077k| 191k
409k| 0 0 |2815 5311
1 0 90 9 0 0: 6 4 89 2 0 0: 3 0 92 5 0
0: 6 1 93 0 0 0: 3 3 45 50 0 0: 1 1 88 10 0
0: 3 7 88 3 0 0: 2 7 85 7 0 0| 800k 1214k| 135k
282k| 0 0 |2570 5140
7 1 89 3 0 0: 0 3 91 6 0 0: 1 2 93 4 0
0: 3 4 90 3 0 0: 3 1 39 57 0 0: 1 0 88 11 0
0: 1 4 79 17 0 0: 3 2 85 9 0 1|1060k 1076k| 108k
225k| 0 0 |2681 5304
1 1 85 13 0 0: 4 5 90 2 0 0: 2 5 91 2 0
0: 4 8 83 5 0 0: 2 0 41 58 0 0: 2 2 92 4 0
0: 0 2 62 36 0 0: 0 1 90 9 0 0|1956k 948k| 153k
360k| 0 0 |2802 5189
2 0 92 6 0 0: 4 2 85 9 0 0: 0 2 95 3 0
0: 1 2 93 4 0 0: 2 3 34 61 0 0: 1 3 85 11 0
0: 3 0 55 42 0 0: 1 0 91 8 0 0|1768k 921k| 170k
367k| 0 0 |2511 4701

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2011-09-06 09:04:38 Re: Sudden drop in DBb performance
Previous Message Gerhard Wohlgenannt 2011-09-06 08:40:40 Re: Sudden drop in DBb performance