1

We have a MySQL 5.0 server on a Centos box. 32GB memory. We have only one harddrive, 374GB capacity, 50GB available. All our tables are MyISAM.

When we copy a large file (2GB-3GB) within the hard drive, MySQL slows down temporarily. The file copying takes less than a minute. But the temporary slow down lasts about one hour (could even be exactly one hour).

Once one hour is over, suddenly everything is back to its routine.

It feels like as if the large file copying pushes MySQL to start using the harddrive for memory, maybe the "sar -b" output could be a proof to that. But why would mysql start using the harddrive, the copied file is about 10% of total RAM.

Any ideas to prevent this?

Here is the sar output, file copying was done around 11:00AM:

10:20:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
10:30:01 AM       all      5.52      0.00      4.40      5.35      0.00     84.72
10:40:01 AM       all      5.38      0.00      4.34      5.34      0.00     84.93
10:50:01 AM       all      6.44      0.00      4.86      7.00      0.00     81.70
11:00:01 AM       all      6.06      0.00      4.33      6.48      0.00     83.12
11:10:02 AM       all      6.18      0.00      6.11      9.60      0.00     78.11
11:20:01 AM       all      1.92      0.00      1.95      8.58      0.00     87.55
11:30:01 AM       all      2.23      0.00      2.59      8.53      0.00     86.64
11:40:01 AM       all      3.63      0.00      3.57      8.95      0.00     83.85

Here is the sar -r output: 

10:20:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree kbswpused  %swpused  kbswpcad
10:30:01 AM    823860  32125192     97.50    345996  24971808  34995984       232      0.00         0
10:40:01 AM    895852  32053200     97.28    360620  24877364  34995984       232      0.00         0
10:50:01 AM    295556  32653496     99.10    372440  25451620  34995984       232      0.00         0
11:00:01 AM    809260  32139792     97.54    395116  24869132  34995984       232      0.00         0
11:10:02 AM    430732  32518320     98.69    659220  23661112  34995984       232      0.00         0
11:20:01 AM    153580  32795472     99.53    687740  24438764  34995984       232      0.00         0
11:30:01 AM    251816  32697236     99.24    778396  24284876  34995984       232      0.00         0
11:40:01 AM    297912  32651140     99.10    782468  24354304  34995984       232      0.00         0

Looking at sar -P ALL, CPU 1 has %70+ iowait:

10:40:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
10:50:01 AM       all      6.44      0.00      4.86      7.00      0.00     81.70
10:50:01 AM         0      5.87      0.00      4.70      1.77      0.00     87.66
10:50:01 AM         1     12.14      0.00      5.80     44.59      0.00     37.48
10:50:01 AM         2      2.68      0.00      2.30      0.43      0.00     94.58
10:50:01 AM         3      5.90      0.00      4.07     11.72      0.00     78.31
10:50:01 AM         4      2.68      0.00      2.53      0.59      0.00     94.20
10:50:01 AM         5      6.45      0.00      4.88      9.13      0.00     79.54
10:50:01 AM         6      2.84      0.00      2.43      0.52      0.00     94.22
10:50:01 AM         7      5.04      0.00      4.31      3.04      0.00     87.61
10:50:01 AM         8      6.37      0.00      4.85      0.73      0.00     88.04
10:50:01 AM         9      8.88      0.00      6.45     17.30      0.00     67.37
10:50:01 AM        10      5.17      0.00      3.79      1.34      0.00     89.69
10:50:01 AM        11     10.85      0.00      8.95      8.72      0.00     71.48
10:50:01 AM        12      4.12      0.00      3.41      0.93      0.00     91.53
10:50:01 AM        13     10.27      0.00      7.59      7.06      0.00     75.08
10:50:01 AM        14      6.81      0.00      5.69      3.01      0.00     84.48
10:50:01 AM        15      6.94      0.00      6.00      1.15      0.00     85.91
11:00:01 AM       all      6.06      0.00      4.33      6.48      0.00     83.12
11:00:01 AM         0      5.86      0.00      6.11      5.44      0.00     82.59
11:00:01 AM         1     11.14      0.00      4.55     45.71      0.00     38.60
11:00:01 AM         2      3.33      0.00      3.24      1.18      0.00     92.25
11:00:01 AM         3      4.33      0.00      3.04      7.62      0.00     85.01
11:00:01 AM         4      2.28      0.00      1.83      1.87      0.00     94.02
11:00:01 AM         5      4.61      0.00      4.51      3.97      0.00     86.91
11:00:01 AM         6      2.59      0.00      2.42      1.80      0.00     93.19
11:00:01 AM         7      3.90      0.00      3.23      2.55      0.00     90.33
11:00:01 AM         8      4.86      0.00      3.94      1.21      0.00     89.99
11:00:01 AM         9      7.06      0.00      5.12     12.43      0.00     75.39
11:00:01 AM        10      4.87      0.00      4.76      1.09      0.00     89.28
11:00:01 AM        11      6.42      0.00      5.20      5.67      0.00     82.71
11:00:01 AM        12      6.28      0.00      5.61      4.63      0.00     83.48
11:00:01 AM        13     14.10      0.00      7.61      4.17      0.00     74.12
11:00:01 AM        14      4.76      0.00      3.54      3.67      0.00     88.04
11:00:01 AM        15     10.64      0.00      4.61      0.74      0.00     84.01

11:00:01 AM       CPU     %user     %nice   %system   %iowait    %steal     %idle
11:10:02 AM       all      6.18      0.00      6.11      9.60      0.00     78.11
11:10:02 AM         0      5.79      0.00      4.76      1.17      0.00     88.28
11:10:02 AM         1      5.87      0.00     16.54     72.31      0.00      5.29
11:10:02 AM         2      3.24      0.00      3.04      4.31      0.00     89.42
11:10:02 AM         3      5.66      0.00      5.32      9.17      0.00     79.85
11:10:02 AM         4      3.81      0.00      3.40      1.59      0.00     91.20
11:10:02 AM         5      6.06      0.00      5.02      9.16      0.00     79.77
11:10:02 AM         6      4.10      0.00      3.94      0.71      0.00     91.25
11:10:02 AM         7      4.64      0.00      4.29      6.18      0.00     84.88
11:10:02 AM         8      6.19      0.00      5.12      0.50      0.00     88.20
11:10:02 AM         9      8.72      0.00      8.12     26.72      0.00     56.44
11:10:02 AM        10      6.37      0.00      4.78      1.01      0.00     87.83
11:10:02 AM        11      8.59      0.00      7.01      8.53      0.00     75.86
11:10:02 AM        12      5.43      0.00      5.09      0.72      0.00     88.76
11:10:02 AM        13     11.04      0.00      9.00      6.99      0.00     72.97
11:10:02 AM        14      5.60      0.00      5.33      1.12      0.00     87.95
11:10:02 AM        15      7.73      0.00      7.05      3.40      0.00     81.82
11:20:01 AM       all      1.92      0.00      1.95      8.58      0.00     87.55
11:20:01 AM         0      1.85      0.00      1.79     11.58      0.00     84.78
11:20:01 AM         1      4.02      0.00      7.08     77.21      0.00     11.69
11:20:01 AM         2      1.32      0.00      1.14      1.08      0.00     96.46
11:20:01 AM         3      1.35      0.00      1.25      3.75      0.00     93.64
11:20:01 AM         4      1.29      0.00      1.11      1.08      0.00     96.53
11:20:01 AM         5      1.49      0.00      1.37      2.70      0.00     94.44
11:20:01 AM         6      1.59      0.00      1.27      0.92      0.00     96.21
11:20:01 AM         7      1.62      0.00      1.50      3.74      0.00     93.14
11:20:01 AM         8      1.84      0.00      1.68      3.32      0.00     93.15
11:20:01 AM         9      2.09      0.00      2.24     19.19      0.00     76.49
11:20:01 AM        10      2.15      0.00      1.42      1.02      0.00     95.41
11:20:01 AM        11      1.95      0.00      1.95      3.06      0.00     93.04
11:20:01 AM        12      1.90      0.00      1.68      1.10      0.00     95.32
11:20:01 AM        13      2.40      0.00      2.09      2.94      0.00     92.57
11:20:01 AM        14      1.90      0.00      1.87      1.14      0.00     95.09
11:20:01 AM        15      1.89      0.00      1.83      3.37      0.00     92.91

sar -W:

10:20:01 AM  pswpin/s pswpout/s
10:30:01 AM      0.00      0.00
10:40:01 AM      0.00      0.00
10:50:01 AM      0.00      0.00
11:00:01 AM      0.00      0.00
11:10:02 AM      0.00      0.00
11:20:01 AM      0.00      0.00
11:30:01 AM      0.00      0.00
11:40:01 AM      0.00      0.00

sar -b, notice the jump right after 11AM.

10:20:01 AM       tps      rtps      wtps   bread/s   bwrtn/s
10:30:01 AM   1941.72    296.49   1645.23   2619.86  26872.80
10:40:01 AM   2018.03    341.37   1676.66   3123.88  25695.44
10:50:01 AM   2581.44    460.03   2121.41   6384.41  35324.48
11:00:01 AM   3031.42    508.31   2523.11   4936.72  44437.03
11:10:02 AM   7222.13   1083.55   6138.58  61930.06 122374.97
11:20:01 AM   4832.72   1960.20   2872.53  55023.49  55869.57
11:30:01 AM   2174.64   1619.38    555.26  14751.91   9753.42
11:40:01 AM   2361.23   1682.74    678.49  15293.00  11799.85
11:50:01 AM   2210.12   1433.24    776.88  12757.86  13321.64
12:00:01 PM   3439.84    932.84   2507.01  22081.47  44653.18
Improve this question
8
  • MyISAM tables, right?
    – Aaron Brown
    Commented Jul 8, 2012 at 15:10
  • MyISAM. I'll update the question with this information.
    – Haluk
    Commented Jul 8, 2012 at 17:43
  • Are you using any compressed MyISAM tables ???
    – RolandoMySQLDBA
    Commented Jul 11, 2012 at 21:10
  • @RolandoMySQLDBA , great question given the symptoms. But, No. Non of the tables are compressed.
    – Haluk
    Commented Jul 11, 2012 at 21:25
  • Next question, are you using myisam_use_mmap ( dev.mysql.com/doc/refman/5.5/en/… ) ???
    – RolandoMySQLDBA
    Commented Jul 11, 2012 at 21:33

2 Answers 2

Reset to default
5

MyISAM tables rely on the filesystem cache to keep data in memory (the indexes are kept in the internal key buffer cache, as specified by key_buffer_size). You haven't said what your total data size is, but I suspect that your copy is pushing data blocks out of the fs cache, such that MySQL has to read them from disk again right after. Your sar -b output supports this by showing 4-5x the disk reads right after the copy. There is also a very small dip in the cached data in the sar -r output - it's a little hard to interpret that, because if the copy is pushing data out, it is replacing bytes in the cache, so I wouldn't expect to see a lot of fluctuation there.

I would recommend using a tool like collectl to gather higher resolution stats (and even better, send that data to a graphing engine like graphite, so that you can visualize it.

Why are you running MyISAM? InnoDB wouldn't have this type of problem (at least not nearly as pronounced, especially with innodb_flush_method=O_DIRECT set) as it maintains its own buffer pool which caches indexes, data, MVCC, and undo pages. If you can't switch to InnoDB or change this job, you could find or write a simple copy program that does what you need but sets O_DIRECT on the open() calls. This will prevent the OS from caching the pages.

Improve this answer
3
  • Total db size is over 50GB. So 3GB could possibly be pushing "everything" in the memory, out of memory? Or mysql somehow finds it necessary re-commit everything to memory?
    – Haluk
    Commented Jul 10, 2012 at 2:28
  • 3GB could be pushing enough pages out of memory to require reading a substantial amount from disk.
    – Aaron Brown
    Commented Jul 10, 2012 at 2:36
  • 1
    I definitely agree with your first paragraph. +1 !!!
    – RolandoMySQLDBA
    Commented Jul 11, 2012 at 21:42
0

Just updated to MySQL 5.5 and this issue is resolved. Tried it with files as large as 10GB and MySQL is not affected at all.

Improve this answer
2
  • I'd like to point out that my answer was the correct one. I explained the fs cache issue as well as recommended using InnoDB. At one point, you had a bounty on this post and it's bad form to redact it and then accept your own answer.
    – Aaron Brown
    Commented Jul 28, 2012 at 1:20
  • I've put up the answer so it would help other poor souls using 5.0. I'll go ahead and delete my innodb remarks, they were also there to help others, but it is not directly related to the question.
    – Haluk
    Commented Jul 28, 2012 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.