Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008

    Unanswered: optimize tables and 48 + hours to complete

    Hi all,

    I am using Mysql enterprise 5.0, there is huge amount of data in each table of last six months. whch is useless (as we have backup of this) so we want to delete old records.

    as i deleted the records and want to get the space used by tables (162 GB single table). it takes 48+ hours to get the job done. where as the same task when i used it on another server, time was 8 Hours.

    after checking the primary database server i analyzed that another server is trying to insert data into this server.

    The primary database server have 2 CPU (Quad core PPC) and 16 GB memory where as test database server have single CPU (Quad Xeon) and 8GB memory.

    how do i make this fast to complete or any suggestion.

  2. #2
    Join Date
    Jun 2007
    Only questions rather than a solution:
    • That's a big table - what sort of data are you storing?
    • Should you aggregate the old data and store it rather than just delete it all?
    • What percentage of the records will be deleted?
    • Can you turn of the inserts from the other server while you're doing the delete?
    • Can you do the delete as a number of smaller deletes?
    • What are the indexes on the table?
    • Will the data just build up again or have you changed the process that stores the data?

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    are you using DELETE or TRUNCATE? | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2008
    delete all old records (jan-01-2008---- june-31-2008)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts