Mysql slows down temporarily after large file copying

MySQL

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

Best Answer

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.

MAJOR CHANGE #1 : Refactor the Query

Here is your original query

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

You could try

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A;

or maybe the count per email

select email,count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A group by email;

MAJOR CHANGE #2 : Proper Indexing

I think you have this already since Query 1 and Query 2 run fast. Make sure you have a compound index on (account_id,email). Do SHOW CREATE TABLE mybigtable\G and make sure you have one. If you don't have it or if you are not sure, then create the index anyway:

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

UPDATE 2012-03-07 13:26 EST

If you want to do a NOT IN(), change the INNER JOIN to a LEFT JOIN and check for the right side being NULL, like this:

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    LEFT JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
    WHERE tbl345.email IS NULL
) A;

UPDATE 2012-03-07 14:13 EST

Please read these two links on doing JOINs

Here is a great YouTube Video where I learned to refactor queries and the book it was based on

Mysql – Derby slows down after 1.2 million records

DISCLAIMER : Not a Derby Expert

DERBY

There are options you can set to increase data pages for Derby

For a mass load of table prodtable, you may want to consider

create temptable just like prodtable, but with no indexes
load data into the temptable
rebuild indexes on temptable
rename prodtable to zaptable
rename temptable to prodtable
drop zaptable

MySQL

If you switch over to MySQL, you could do wonderful things to performance tune data processing

Start with using only InnoDB
You can configure InnoDB to
- hyperthread read threads
- hyperthread write threads
- access multiple CPUs/Cores
- setup partitions buffer pools

Here are some of my past posts on configuring InnoDB in MySQL for multicore performance

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)

While I may be a little biased towards MySQL, make sure you have Derby's caching configured to accommodate mass INSERTs. That way, you need not perform a major data migration if Derby can be made to handle large transactions.

Best Answer

Related Solutions

MySQL subquery slows down drastically, but they work fine independently

MAJOR CHANGE #1 : Refactor the Query

MAJOR CHANGE #2 : Proper Indexing

UPDATE 2012-03-07 13:26 EST

UPDATE 2012-03-07 14:13 EST

Mysql – Derby slows down after 1.2 million records

DERBY

MySQL

Related Question