Announcement

Announcement Module
Collapse
No announcement yet.

How to speed up Delete?

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

  • How to speed up Delete?

    Hello,

    my database loads 120,000 rec in 1 hour,keeps 2 hours and rolls the oldest hour into hrly table.
    Then I have to DELETE that rollup hour.
    I was using partitions in comminity version,but our classic has no partitions and I need to get it working with "delete".

    I need to delete 120,000 out of 300,000 rec in less 1 min...
    The next step is even worse:
    I need to delete 2,5 mln rec out of 40 mln rec. eek: eek:
    Even if I do it by chanks - it took very long time, 12 min! to delete 2,000 rec from 20 mln rec table

    I do have an index on the field for delete.

    However I noticed that

    delete from a where datex='2009-03-12 02:03:01'
    or delete from a where datex between t1 and t2
    ( uses an index,correct?)

    runs 4 times slowly then

    delete from a where date(datex)=datex2009-03-12'
    ( does not use an index)

    I'm using MyISAM , 4G memory

    max_tmp_tables=64
    tmp_table_size=32M
    max_heap_table_size=32M
    table_open_cache=512
    table_cache=2table_cache

    query_cache_size=64M
    query_cache_type=1
    query_cache_limit=4query_cache_limit

    max_connections=100
    key_buffer=512M

    myisam_sort_buffer_size=250M

    All the suggestions are greatly appreciated!

    thank you.
    Helen

  • #2
    delete from a where datex between t1 and t2


    should be the fastest. Make sure you have an index on `a`.

    Also, given that you're deleting a range, if you made the table InnoDB, and made `a` the primary key, the delete would occur in seconds at most. This is because InnoDB stores rows in primary key order -- so deleting a range would be touching row physically next to each other on the disk, making the changes extremely quick. MyISAM doesn't support this. If you decide to try InnoDB, investigate the setting innodb_flush_logs_at_trx_commit=2 . It will make inserts and updates as fast as MyISAM. Of course, you'll need to adjust innodb_buffer_pool_size to a reasonable size.

    Comment


    • #3
      Thank you,MarkRose!

      I have MyISAM tables. I do have an index on a(datex) and my new tests showed that index speeded up.

      but it is still too slow.

      Any ideas to change db parameters?

      thank you!
      Helen

      Comment


      • #4
        I'm not that familiar with MyISAM.

        You could always create a new MyISAM table ever hour, make a MERGE table over the tables you need. Then you wouldn't be moving data around at all.

        Comment


        • #5
          Thank you, Mark.
          I'll try MERGE tables.

          helen

          Comment

          Working...
          X