MySQL Forums
Forum List  »  Performance

High IOWait After A Few Days
Posted by: CK C
Date: December 04, 2005 12:43AM

Hello,

I'm having a problem with high iowait and hope someone can shed some light.

The backend application (on other servers) generates about 60 queries/sec and most of them are INSERTs. Very few queries are performed by human, so the query rate is more or less constant and I'd expect the server load to be constant too.

The MySQL is on a dedicated machine, it runs NO other daemons/applications:
- Redhat Linux ES 4
- MySQL 5.0.15 - binary install
- IDE Raid 1

When I start MySQL from fresh, the linux avg load will be less than 0.5. In a few days, the load will slowly increase to more than 2. When I restart MySQL at this time, the load will drop to less than 0.5 immediately. (Nothing else being done on the application, I just restart MySQL)

I wrote a script to record uptime and vmstat every 15 minutes and here's the result:

When MySQL FIRST STARTED:

Thu Dec 1 21:00:02 SGT 2005
21:00:02 up 22 days, 3:56, 0 users, load average: 0.47, 0.56, 0.66
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 3 36140 853776 3720 76644 0 0 4 8 0 2 2 2 77 19
1 1 36140 858656 3552 77332 0 0 1326 834 2275 1894 10 5 39 46
1 0 36140 857892 3560 77324 0 0 45 209 2656 1966 27 4 65 4
0 0 36140 858284 3616 78048 0 0 64 475 2649 1817 18 6 60 16
0 2 36140 858116 3616 78048 0 0 29 245 2235 1372 3 3 90 4
0 1 36140 857436 3624 78560 0 0 66 240 1877 1093 1 2 87 10
0 1 36140 856932 3636 79068 0 0 42 506 2440 1567 3 3 82 12
2 1 36140 856452 3644 79320 0 0 22 253 2517 1534 15 4 71 10
1 2 36140 856340 3500 79204 0 0 14 221 2389 1471 28 5 55 13
1 1 36140 856648 3516 78928 0 0 12 272 2207 1348 4 3 88 5
0 1 36140 856700 3396 78528 0 0 5 246 1978 987 2 3 81 14
1 1 36140 856876 3404 78520 0 0 2 122 1475 604 1 1 94 4
0 0 36140 858932 3192 76652 0 0 49 376 2044 1218 3 3 81 14
0 0 36140 863136 3200 76644 0 0 26 190 1988 1160 2 2 86 10
0 0 36140 862852 3208 76636 0 0 12 230 1808 1066 1 2 91 5


A few days later, it gets high load:

Sun Dec 4 13:45:02 SGT 2005
13:45:02 up 24 days, 20:41, 0 users, load average: 2.65, 2.47, 2.42
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 7 36136 841140 768 23848 0 0 8 0 3 2 2 2 75 21
0 4 36136 831408 1616 25600 0 0 2508 486 1991 1838 9 4 10 77
0 2 36136 833624 1544 28012 0 0 1348 338 2344 1839 2 2 30 65
0 2 36136 833496 1484 28852 0 0 869 312 2035 1437 1 2 38 59
0 3 36136 835040 1400 26856 0 0 750 292 2152 1426 2 3 26 69
0 3 36136 837208 1300 24876 0 0 602 376 2194 1409 2 3 23 73
0 3 36136 836200 1016 26200 0 0 1254 146 1997 1344 2 3 21 74
3 3 36136 836916 672 25156 22 0 1259 250 2397 1777 3 3 13 81
0 4 36136 833128 900 27984 13 0 1890 213 2332 1801 3 3 17 76
0 2 36136 833452 904 27208 0 0 897 334 2095 1424 2 2 26 70
0 4 36136 833896 860 26992 0 0 1120 210 2184 1561 2 2 22 73
1 2 36136 832452 884 28268 0 0 1023 270 2059 1377 2 2 27 68
0 3 36136 834248 1036 26712 21 0 1066 390 1991 1319 2 2 25 71
0 1 36136 835216 1040 30380 0 0 1242 229 2429 1889 3 3 33 60
0 3 36300 836580 1044 28964 0 0 827 234 2151 1568 2 2 35 60


From top and vmstat, I saw the iowait has increased, which seems to be caused by the higher "bi" (more blocks are being read from the disk).

Any idea why MySQL reads the disk more aggresively after a few days? key-buffer shouldn't be an issue as mysql administrator shows key efficiency of 99%.

Comments are appreciated. TIA.

Options: ReplyQuote


Subject
Views
Written By
Posted
High IOWait After A Few Days
2701
December 04, 2005 12:43AM
1599
December 05, 2005 08:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.