I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get the best performance. I have windows 7 64 bit.
You will need to check out the sum total of your indexes for MyISAM. Please run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;
Whatever number comes out, use either that number or 8G, whichever is smaller.
MyISAM only caches index pages.
If you plan to migrate your data to InnoDB, use these settings:
[mysqld] innodb_file_per_table innodb_log_file_size=2047M innodb_log_buffer_size=64M innodb_buffer_pool_size=18G innodb_read_io_threads=64 innodb_write_io_threads=64 innodb_io_capacity=10000