How to speed up mysql update join query between 2 tables?

Your query should be taking a long time. Why ??? Look at your query:

UPDATE content a JOIN peers_data b ON a.hash = b.hash

SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated;

Please note the columns begin mysql update join by the query in the content table:

seeders

leechers

is_updated

Which of these columns are indexed ?

seeders

leechers

What indexes do you have in the content table involving those columns ?

KEY `seeders` (`seeders`), <<<<-------------------------------- THIS ONE !!!

KEY `uploader_sub_category` (`uploader`,`sub_category`),

KEY `upload_date` (`upload_date`),

KEY `uploader_upload_date` (`uploader`,`upload_date`),

KEY `leechers` (`leechers`), <<<<------------------------------ THIS ONE !!!

KEY `size` (`size`),

KEY `uploader_seeders` (`uploader`,`seeders`), <<<<------------ THIS ONE !!!

What is going on under the hood ?

If you are changing the values for seeders and leeches, these three(3) indexes are having their leaf nodes reshuffled. Even if the majority of the values are not being changed, rows are being locked and copies of your data are being stockpiled in your undo logs (for the sake of MVCC). This results is additional disk I/O (ibdata1 should be growing)

#INNODB

The InnoDB Buffer Pool will be going through the "Perfect Storm". Why ?

Please note the InnoDB Architecture (Picture from Percona CTO Vadim Tkachenko)

Please note the Insert Buffer. Here is what the MySQL Documentation says about it:

The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

Unlike clustered indexes, secondary indexes are usually non unique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read-in secondary index pages from disk.

Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

Change buffer merging may take several hours when there are numerous secondary indexes to update and many affected rows. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed. In fact, change buffer merging may continue to occur after a server shutdown and restart (see Section 14.21.2, “Forcing InnoDB Recovery” for more information).

In memory, the change buffer occupies part of the InnoDB buffer pool. On disk, the change buffer is part of the system tablespace, so that index changes remain buffered across database restarts.

Here is where the "Perfect Storm" comes in: By default, InnoDB storage engine reserves up to 25% of the Buffer Pool for Change Buffering. All changes to your three(3) secondary indexes have to pile up in the InnoDB Buffer Pool's Insert Buffer. When the actual index pages land in the Buffer Pool, the merge process will subsequently be pushed to disk (Note the Insert Buffer inside ibdata1), producing more disk I/O.

#SUGGESTIONS

###SUGGESTION #1

Get rid of those indexes. Why ? This will eliminate the change buffering needed for managing those indexes during your mass UPDATE.

Run the following query:

SELECT

    COUNT(1) rowcount,

    COUNT(DISTINCT seeders) seeders_count,

    COUNT(DISTINCT leechers) leechers_count

FROM content;

If seeders_count is less than 5% of rowcount, then the cardinality of seeders_count can disqualify the seeders from being of any use. Same going with leechers.

For that index uploader_seeders, run this query:

SELECT COUNT(1) uploader_seeders_count FROM

(SELECT DISTINCT uploader,seeders FROM content) A;

If uploader_seeders_count is less than 5% of rowcount (from previous query), then get rid of uploader_seeders index.

To get rid of those indexes, run this:

ALTER TABLE content DROP INDEX uploader_seeders,DROP INDEX seeders,DROP INDEX leechers;

###SUGGESTION #2

Change the Insert Buffer size to the max value, the run the query:

SET GLOBAL innodb_change_buffer_max_size = 50;

UPDATE content a JOIN peers_data b ON a.hash = b.hash

SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated;

SET GLOBAL innodb_change_buffer_max_size = 25;

Also, set the Buffer Pool Size (innodb_buffer_pool_size) to 20G. Change that value in my.cnf.

If you have MySQL 5.7, simply run

mysql> SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 20;

If you have MySQL 5.6 and prior, you must restart mysqld.

###SUGGESTION #3 (RISKY)

If you do not want to drop the indexes, you could disable change buffering during the mass UPDATE, then enable it afterwards:

Run the following:

SET GLOBAL innodb_change_buffering = 'none';

UPDATE content a JOIN peers_data b ON a.hash = b.hash

SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated;

FLUSH TABLES;

SET GLOBAL innodb_change_buffering = 'all';

This is risky because this speeds up changes to indexes in favour of not having buffering to recover in the event of a crash or reboot.

  ###SUGGESTION #4 (RISKY)

Another cavalier approach would be to disable the Double Write Buffer. Since a restart is required, do this:

service mysql restart --skip-innodb_doublewrite

Do your mass UPDATE

service mysql restart

This is not recommended for Production. Dev and Staging only please !!!