MYSQL Huge table and DRAM problem

Hi

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.

Here is table structure, very simple
mysql> describe mrtg_util;
±----------------------±--------±-----±----±--------±- --------------+
| Field | Type | Null | Key | Default | Extra |
±----------------------±--------±-----±----±--------±- --------------+
| mrtg_util_id | int(11) | | PRI | NULL | auto_increment |
| mrtg_util_in_val | int(11) | | | 0 | |
| mrtg_util_out_val | int(11) | | | 0 | |
| mrtg_util_tst | int(11) | | | 0 | |
| mrtg_util_mrtg_cfg_id | int(11) | | | 0 | |
| mrtg_util_in_val_abs | int(11) | | | 0 | |
| mrtg_util_out_val_abs | int(11) | | | 0 | |
±----------------------±--------±-----±----±--------±- --------------+

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.

Really appreciate your help with this.

Best regards

Mikhail Chelomanov
Brisbane Australia

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?

vgatto Hi

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.

Thanks again for your help.

Best regards

Mikhail Chelomanov
Brisbane Australia