2

I have an application on a web server.

The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this table to check if a domain name is available and it works surprisingly fast.

Every day, however, the list of taken domain names is updated by the organization which provides it. I have to download the compressed new list of words and then use it to update the domains table for it to remain accurate.

I am not sure, however, how to go about updating the table which is live on the web MySQL server and takes up roughly 3 gigabytes.

I've researched it a bit, and the best so far I've found is to create a new table/database then delete the old one and switch them over. The changes to the table, however, are probably less than 1% daily, so this feels overkill and inefficient.

Would be great to know if there is a better way.

3
  • 1
    So why not dump the new data in a table and JOIN-UDPATE the existing one?
    – Mihai
    Commented Sep 2, 2014 at 14:13
  • @David Gurevich What is the ENGINE and structure of the table? Which version of mysql are you using? In which format do you receive the list (is it ordered?).
    – jynus
    Commented Sep 2, 2014 at 14:14
  • @jynus The Engine is InnoDB. The table consists of one column named domains which is the primary key and has the domain names in it as a string. The list of domains is sorted alphabetically before I insert it. The MySQL version is 5.5 As for the JOIN-UPDATE idea, it seems that would basically be the same as just creating a new table and switching, though. Commented Sep 2, 2014 at 14:27

2 Answers 2

1

Given the clarifications on your comment, I would recommend 2 options:

  • Use LOAD DATA INFILE IGNORE to load the data directly from the filesystem. This will insert new domains and not touch the "old" ones, but it will not delete the ones that are removed. On the bright side, it will reduce IO a lot.

  • Go for your approach: use LOAD DATA INFILE on a new table, then RENAME the old and new table, which is a very fast operation. Inserting on smaller tables is faster than inserting on larger tables on InnoDB, but the whole table has to be written.

In both cases, there are a lot of things that I can recommend you to improve the inserting performance.

If you do not perform other write operations than the import, this would be one of the few cases in which I would recommend you to use MyISAM- its import performance is faster, as it does not have to deal with a transaction log, and you will not have to deal with corruption, as you will only use it as a read-only table. If the import fails, you can always repeat it from the file.

If you use InnoDB, on the other side, you can check for duplicates/missing records and modify them in parallel to match MyISAM performance but that only will be performant on the latest MySQL versions (>= 5.6).

Other things to have into account: disable temporarily the binary log; disable keys, try fixed-with format (for MyISAM), disable the doublewrite buffer and autocommit, augment the buffer pool and transaction log, make sure you use innodb_file_per_table=1 (for InnoDB). With proper tuning, for 5.5, I am able to insert a 50 million table (much wider than yours) in 2-3 minutes in MyISAM (without flushing the cache) and 11-12 minutes (durably) in InnoDB on spinning disks. 5.6 and 5.7 improved InnoDB performance closer to MyISAM.

Addendum: On other databases, I would recommend you to use a cursor to analyse the table row by row, but I do not have much faith on MySQL's programatic features.

0

Another answer that I can think of, if you have enough space on disk, is to do the pre-processing outside of the database.

You can use a large-file comparison tool that do not require the files to be on memory (or even program your own quick script, I do not see it too complicated) and then generate a patch-like syntax, so you can, from that, generate a list of values to be DELETEd and INSERTd. If you presort the file before inserting it, you definitely can do that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.