•  

Announcement

Announcement Module
Collapse
No announcement yet.

MYSQL Huge table and DRAM problem

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL Huge table and DRAM problem

    #1
    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
    Tags: None

  • #2
    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?

    Comment


    • #3
      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

      Comment


      • #4
        Ok, based on the explain, you're doing a table scan to satisfy this query. That means MySQL has to read all 10GB of data in your table. It's pretty clear you need some indexes. I'd recommend creating an index on mrtg_util_mrtg_cfg_id. If there are only a few rows which have a mrtg_util_mrtg_cfg_id of 540 or 633 than you should see a dramatic improvement in performance. Unfortunately, creating an index on a 300M row table will take quite some time.

        Also, just to be safe, I'd recommend changing your query just a little from:


        AND ( mrtg_util_mrtg_cfg_id = '540' OR mrtg_util_mrtg_cfg_id = '633' )


        to this, which should be the same:


        AND mrtg_util_mrtg_cfg_id IN (540,633)


        Some versions of MySQL make bad decisions about how to evaluate your query if they see an 'OR'.

        Comment


        • #5
          en,I have a idea,may be helpful

          you may consider if can separate the table

          but may occur other problems

          Comment

          Working...
          X