Re: Sudden drop in DBb performance

Поиск
Список
Период
Сортировка
От Gerhard Wohlgenannt
Тема Re: Sudden drop in DBb performance
Дата
Msg-id 4E64D7EA.3010704@ai.wu.ac.at
обсуждение исходный текст
Ответ на Re: Sudden drop in DBb performance  (Andy Colson <andy@squeakycode.net>)
Ответы Re: Sudden drop in DBb performance  (Scott Marlowe <scott.marlowe@gmail.com>)
Список pgsql-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@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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Andy Colson
Дата:
Сообщение: Re: Sudden drop in DBb performance
Следующее
От: Gerhard Wohlgenannt
Дата:
Сообщение: Re: Sudden drop in DBb performance