开发者

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.


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜