2

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.

AliBZ
  • 1,789
  • 5
  • 16
  • 27

1 Answers1

1

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

You wiil need to optimize the InnoDB infrastructure

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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? – AliBZ Dec 21 '11 at 23:40
  • If all tables were InnoDB, you could update data faster. You can also engage more CPUs using InnoDB. – RolandoMySQLDBA Dec 21 '11 at 23:45
  • Does removing the FK s from my innodb tables make updating faster? – AliBZ Dec 21 '11 at 23:47
  • Definitely faster without Foreign Keys. – RolandoMySQLDBA Dec 21 '11 at 23:50
  • 1
    If 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... – gbn Dec 22 '11 at 06:02