13GB DB - Performance Question

MySQL & MariaDB Other MySQL® Questions
21

When I say it dropped rows I mean that about 40 million rows never made it to the myisam db.

count(*) doesn’t match on both db’s.

I’m doing it through php right now by selecting one row at a time and inserting it. Should only take me 24 hours or so assuming it works.

With regards to the same charset, I tried it with the same charset and without to no avail.

I’m hopeful that going through php and doing it one select/insert at a time will work even if it is a bit slower )

22

Hello,

I’m in a habit to play with 50GB tables so I will give you my own advice for what it’s worth :wink:

  1. There’s a better way to move large data into MySQL. Don’t do “INSERT INTO … SELECT * FROM” it’s just plain inefficient.
    Dump your data into a textfile. “SELECT * FROM t1 INTO OUTFILE” will do the trick.
    There you can do external editing (removing bogus chars, converting charsets…) as you wish.
    Then insert your data in your new table using “LOAD DATA INFILE”
    This should be much faster.

  2. About your performance problem. As someone said, 128MB is EXTREMELY INEFFECTIVE for InnoDB. InnoDB likes memory, and for that you need at last 70% of available memory allocated to the buffer pool (or if you’re on a 32-bit system, 2GB will do).

  3. I see that you will be using MyISAM. Can you post your key_buffer_size variable? MyISAM buffer only stores indexes so the size will be inferior to InnoDB’s.

  4. Partitioning could be a key response to your performance problems. Can you use MySQL 5.1? If then, I recommend you have a look at the Partitioning stuff. You could easily partition your data by date and improve access times. Otherwise, if you stick with 5.0 you can try to divide your data in smaller tables, then use MERGE tables for the largest span queries.

Best Regards

23

If the query exceeds the maximum amount of memory available, it will fail. I’ve had this happen before, in fact, i have it happen nightly at least once for a query I have yet to optimize as MySQL is running its backups processes :wink: