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.
Add a comment
|
1 Answer
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
answered Dec 21, 2011 at 23:34
-
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
-
-
-
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