Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
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 6:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.