Search code examples
mysqlperformanceinnodblarge-data

What MySQL my.ini parameters should I change for a large table join and update?

I have the following:

  • TableA - 20M rows
  • TableB - 500K rows

And many queries, in particular the following, take forever.

UPDATE
    TableA AS A
INNER JOIN
    TableB AS B
    ON   B.Value IS NOT NULL AND A.Key=B.Key    
SET
    A.Value = B.Value
WHERE
    A.Value IS NULL;

I know my MySQL (MyISAM) is not configured for large tables and I'm sure it could use more of the available memory (4GB total) or CPUs (i7). What parameters in my.ini should I be looking at?

I've started with key_buffer_size = 1536M because tableA has a 1gb index.

Solution

  • For innodb

    • innodb_buffer_pool_size set to approx 80% of memory you want available to mysql
    • innodb_log_file_size * innodb_log_files_in_group should be large enough that changes will be written to disk no more than once a second

    But easier still use the configuration wizard https://tools.percona.com/wizard