Re: Sudden drop in DBb performance

On 09/05/2011 03:51 PM, Andy Colson wrote:
> On 09/05/2011 02:48 AM, Tomas Vondra wrote:
>> On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote:
>>> Dear list,
>>>
>>> we are encountering serious performance problems with our database.
>>> Queries which took around 100ms or less last week now take several
>>> seconds.
>
>>> Results of Bonnie++
>>>
>>> Version 1.96 ------Sequential Output------ --Sequential Input-
>>> --Random-
>>> Concurrency 1 -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
>>> voyager 95G 1400 93 27804 3 16324 2 2925 96 41636 3
>>> 374.9 4
>>> Latency 7576us 233s 164s 15647us 13120ms
>>> 3302ms
>>> Version 1.96 ------Sequential Create------ --------Random
>>> Create--------
>>> voyager -Create-- --Read--- -Delete-- -Create-- --Read---
>>> -Delete--
>>> files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
>>> /sec %CP
>>> 16 141 0 +++++ +++ 146 0 157 0 +++++ +++
>>> 172 0
>>> Latency 1020ms 128us 9148ms 598ms 37us
>>> 485ms
>>>
>>
>> That seems a bit slow ... 27MB/s for writes and 41MB/s forreads is ait
>> slow with 8 drives.
>>
>> Tomas
>>
>>
>
> Agreed, that's really slow. A single SATA drive will get 60 MB/s.
> Did you run Bonnie while the VM's were up and running?
>
> root(at)host:~# vmstat
> procs -----------memory---------- ---swap-- -----io---- -system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us
> sy id wa
> 0 0 0 308024 884812 40512932 0 0 464 168 353 92
> 4 2 84 9
>
>
> Only one line? That does not help much. Can you run it as 'vmstat 2'
> and let it run while a few slow queries are performed? Then paste all
> the lines?
>
>
> -Andy

Hi Andy,

thanks a lot for your help.

Below please find the results of vmstat 2 over some periode of time ..
with normal database / system load.

0 0 1344332 237196 104140 31468412 0 0 330 102 4322 7130
4 2 90 4
1 1 1344332 236708 104144 31469000 0 0 322 105 2096 3723
1 2 92 5
2 1 1344204 240924 104156 31462484 350 0 1906 234 3687 4512
12 3 77 9
0 0 1344200 238372 104168 31462452 0 0 8 109 4050 8376
8 3 86 3
0 0 1344200 232668 104168 31462468 0 0 12 158 2036 3633
2 2 92 3
0 3 1344196 282784 104180 31413384 4 0 1768 343 2490 4391
1 2 84 13
1 1 1344196 278188 104192 31416080 0 0 1392 341 2215 3850
1 2 82 15
0 0 1344120 276964 104608 31416904 90 0 634 304 2390 3949
4 2 86 8
1 1 1344120 277096 104628 31417752 0 0 492 378 2394 3866
2 1 87 10
0 1 1344120 274476 104628 31418620 0 0 260 233 1997 3255
2 1 91 6
1 1 1344120 276584 104628 31418808 0 0 128 208 2015 3266
2 1 91 6
0 0 1343672 272352 106288 31418788 694 0 1346 344 2170 3660
3 1 89 6
0 1 1343632 270220 107648 31419152 48 0 468 490 2356 3622
4 2 88 5
0 0 1343624 270708 107660 31419344 20 0 228 138 2086 3518
2 3 91 4
0 1 1343612 268732 107660 31419584 12 0 168 112 2100 3585
3 2 91 3
0 0 1343544 266616 107660 31420112 14 0 154 73 2059 3719
3 2 93 3
0 1 1343540 267368 107684 31420168 0 0 78 260 2256 3970
3 2 90 6
0 1 1343540 268352 107692 31420356 0 0 94 284 2239 4086
2 2 89 6
0 0 1343540 274064 107692 31423584 0 0 1622 301 2322 4258
2 3 83 13
0 2 1343440 273064 107704 31423696 96 0 106 180 2158 3795
3 2 90 5
0 0 1342184 262888 107708 31426040 840 0 2014 146 2309 3713
5 3 83 9
0 0 1342184 261904 107732 31426128 0 0 60 158 1893 3510
1 3 91 5
2 0 1342184 258680 107732 31427436 0 0 794 114 2160 3647
2 3 90 5
0 2 1342176 258184 107744 31428308 24 0 310 116 1943 3335
2 2 91 4
1 0 1342172 259068 107756 31428700 2 0 138 143 1976 3468
1 1 93 5
0 0 1342172 258084 107756 31429948 0 0 620 88 2117 3565
3 1 90 6
0 0 1342172 258456 107952 31430028 0 0 62 305 2174 3827
1 2 91 6
1 0 1342172 257480 107952 31430636 0 0 300 256 2316 3959
3 2 86 8
0 0 1342172 257720 107952 31430772 0 0 46 133 2411 4047
3 2 91 3
1 2 1342172 257844 107976 31430776 0 0 136 184 2111 3841
1 1 92 6
1 2 1342172 338376 107576 31349412 0 0 462 8615 2655 5508
5 3 79 13
1 2 1342172 340772 107580 31351080 0 0 682 377 2503 4022
2 1 87 10
1 2 1342172 335688 107596 31351992 0 0 548 306 2480 3867
4 1 86 9
0 2 1342168 337432 107608 31352704 0 0 224 188 1919 3158
1 1 93 6
0 0 1342168 337804 107608 31353020 0 0 154 249 1933 3175
1 1 92 6
0 1 1342168 335944 107636 31353464 0 0 212 173 1912 3280
4 2 89 5
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 0 1342168 336936 107636 31353512 0 0 14 183 1911 3426
2 1 93 4
0 1 1342168 334440 107656 31353736 0 0 264 372 2119 3400
6 2 84 8
0 0 1342164 334084 107680 31354468 0 0 302 413 2361 3613
2 1 87 10
2 0 1342160 342764 107680 31354916 8 0 184 332 2142 3117
1 1 90 7
0 1 1342160 343788 107680 31355808 0 0 360 211 2247 3249
1 2 91 5
2 1 1342156 340804 107704 31355904 0 0 88 280 2287 3448
2 2 90 6
0 1 1342156 344276 107704 31356464 0 0 316 276 2050 3298
1 2 90 7
0 0 1342156 344160 107712 31356576 0 0 4 225 1884 3194
1 3 90 6
0 0 1342152 342548 107724 31356688 0 0 52 231 1963 3232
1 3 89 6
2 1 1342152 343664 107724 31356764 0 0 104 348 2643 3614
3 2 88 8
1 1 1342144 341060 107760 31357080 16 0 120 307 2511 3474
4 3 87 7
1 0 1342140 342332 107780 31357500 8 0 206 193 2243 3448
4 2 89 5
1 0 1342136 339472 107780 31357508 0 0 32 142 4290 3799
6 3 87 4
0 0 1342136 341160 107780 31357992 0 0 216 171 2613 3995
4 2 88 5
0 0 1342136 342168 107820 31357988 0 0 26 140 2347 3753
3 4 89 4
0 0 1342136 342532 107820 31358128 0 0 36 155 2119 3653
2 1 91 5
2 0 1342136 341564 107828 31358144 0 0 0 151 1973 3486
4 2 90 4
1 1 1342136 342076 107852 31358416 0 0 148 284 2251 3857
6 2 84 8
0 1 1342136 339944 107852 31359284 0 0 482 478 2902 5210
4 2 84 10
0 1 1342136 342184 107852 31359836 0 0 238 372 2292 4063
2 1 88 9

cheers gerhard