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
Thanx, I got a littel more than 1000. One question, I had innodb before and I set innodb_buffer_pool_size to 16GB and it really used 16GB of my ram. Which one is faster if I only want to update these tables?– AliBZDec 21, 2011 at 23:40
If all tables were InnoDB, you could update data faster. You can also engage more CPUs using InnoDB. Dec 21, 2011 at 23:45
Does removing the FK s from my innodb tables make updating faster?– AliBZDec 21, 2011 at 23:47
Definitely faster without Foreign Keys. Dec 21, 2011 at 23:50
1If this wasn't MySQL I'd say don't remove FKs for performance because you sacrifice data integrity. If it wasn't MySQL I'd downvote too...– gbnDec 22, 2011 at 6:02