I have a database with HUGE table which holds historical mrtg utilization data for 12 months, see below
-rw-rw---- 1 mysql mysql 8924 2008-01-29 11:17 mrtg_util.frm
-rw-rw---- 1 mysql mysql 8750329384 2009-02-27 11:30 mrtg_util.MYD
-rw-rw---- 1 mysql mysql 2473731072 2009-02-27 11:30 mrtg_util.MYI
So there are over 300 million records in this table and physical size is over 10Gb. The machine this MYSQL runs on has only 3Gb of DRAM.
When my code accesses (SELECT)this table (to get for example 3 months worth of data), my machine runs out of memory and as result starts paging (swapping) which dramatically reduces its performance and it takes few minutes before I see result which is already too late as Web browser has timeout by then.
Could you please tell me what is the best way to address problems like that.
I was thinking about splitting this big table into 12 monthly tables, but the resulting monthly table might still exceed RAM size with the same result.
It’s hard to make a good suggestion without a little more information about your situation. What is the query that you’re doing and what does the output of EXPLAIN look like for that query? Are you using MyISAM or InnoDB? What does the output of SHOW INDEXES look like for this table?
Thanks for your response.
I am using MyISAM tables.
Query is very simple
EXPLAIN SELECT mrtg_util_id, mrtg_util_in_val, mrtg_util_out_val, mrtg_util_in_val_abs, mrtg_util_out_val_abs, mrtg_util_tst, mrtg_util_mrtg_cfg_id FROM mrtg_util WHERE ( 1235890613 - mrtg_util_tst ) < 86400 AND ( mrtg_util_mrtg_cfg_id = ‘540’ OR mrtg_util_mrtg_cfg_id = ‘633’ ) ORDER BY mrtg_util_tst;
±—±------------±----------±-----±--------------±---- -±--------±-----±--------±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±----------±-----±--------------±---- -±--------±-----±--------±----------------------------+
| 1 | SIMPLE | mrtg_util | ALL | NULL | NULL | NULL | NULL | 301419975 | Using where; Using filesort |
±—±------------±----------±-----±--------------±---- -±--------±-----±--------±----------------------------+
1 row in set (0.00 sec)
This table: mrtg_util has only one index, which is mrtg_cfg_id - primary key.
I think I have found a work around by splitting this huge table into many small ones (thousands), but I have few more tables like that which I can’t fix that easily.
I must admit that I have not touched my.cnf AT ALL.
It is as it has been created during the MySQL install and has bugger all info in it.
I will try to play with it tomorrow.