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.

We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance.

We have tried to configure my.cnf but heavy queries got stuck for many hours without any result. Some queries take one or two hours to complete on our SQL server (8GB RAM).

It's a dedicated MySQL server.

OS = Ubuntu
Installed RAM = 512GB
Table type = MyISAM

We have very large tables (a few tables having more then 70 million rows). Almost every time new tables to match so indexes won't help that much, although the necessary indexes exist.

We do not have any application to connect our database, just 2 users who write adhoc queries directly.

Below are some variables we have changed from the default values.

key_buffer_size         = 250G
read_buffer_size        = 2G
read_rnd_buffer_size    = 50M
join_buffer_size        = 25M
tmp_table_size          = 5G
sort_buffer_size        = 2G
innodb_buffer_pool_size = 2G
query_cache_limit       = 10M
query_cache_size        = 100M
max_connections         = 20

Below is the MySQLTuner recommendation:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1909G (Tables: 940)
[--] Data in InnoDB tables: 2G (Tables: 3)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 59s (9 q [0.021 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 252.1G global + 4.1G per thread (20 max threads)
[OK] Maximum possible memory usage: 333.6G (66% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 10M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)

Any help would be greatly appreciated.

share|improve this question
    
Are you monitoring the hardware resource usage? What seems to be the bottleneck according to it? –  golimar Apr 12 '13 at 13:34
add comment

migrated from stackoverflow.com Apr 11 '13 at 20:14

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

Look at this line

[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G

The sum of all your MyISAM indexes is 131.6G

You should lower key_buffer_size to 140G. This will give back 110G.

I have a little suggestion for you. You may want to consider using a dedicate keycache for each large table. See my Nov 16, 2012 post Cache all mysql table

Look at this line

[!!] InnoDB data size / buffer pool: 2.2G/2.0G

innodb_buffer_pool_size could be raised to 2560M (That's 2.5G)

Look at these lines

read_buffer_size=2G
sort_buffer_size = 2G

Why is so big? If you change both of these to 32M, you can increase max_connections. In fact, do the following right now:

SET @ThrityTwoMeg = 1024 * 1024 * 32
SET GLOBAL read_buffer_size = @ThrityTwoMeg;
SET GLOBAL sort_buffer_size = @ThrityTwoMeg;
SET GLOBAL join_buffer_size = @ThrityTwoMeg;

Then, go rerun mysqltuner.pl and you will see a dramatic difference from 4.1G per thread

BTW go change join_buffer_size to 32M in my.cnf

Give it a Try !!!

UPDATE 2013-04-11 16:53 EDT

I just thought of another change you can make to increase SELECT speed against MyISAM tables. For every MyISAM you need to read a lot from, do this to the table:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

Believe it or not, this will bloat the table 80-100% in size, but SELECTs will go 20-25% faster. I have written about this many times. Here is just few of my posts:

share|improve this answer
    
Rolando, It has been a real help... –  Umair Apr 12 '13 at 17:32
    
I am working on both "Cache all mysql table" and "Converting all columns to char" suggestion,i have never heard these solution before. Thank you. -- I changed the key buffer, but whenever i restart mysql services and try to login it comes up with the following error --- root@srv2:/etc/mysql# mysql -u root -p; Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) And after few tries (about 5 minutes) it logs in with no more error, is it normal? -- –  Umair Apr 12 '13 at 17:53
    
-- 2) After key buffer allocation when i check free ram i get the following output total used free shared buffers cached Mem: 515986 515341 644 0 216 471700 -/+ buffers/cache: 43424 472561 Swap: 230778 2498 228280 why still 472561 ram is free and only 43424 is used whereas i assigned 150G to keybuffer, i am not sure if its a normal behavior -- whats the difference between changing the values in my.cnf and "Setting Global variables" -- –  Umair Apr 12 '13 at 17:53
    
Values set with SET GLOBAL do not survive any restart of mysqld. Any variable you change with SET GLOBAL and you want to keep indefinitely in the event of a MySQL restart, you must add it to my.cnf. Otherwise, you would have to run the SET GLOBAL commands every time mysqld is restarted. –  RolandoMySQLDBA Apr 12 '13 at 18:06
    
got it...thank you. –  Umair Apr 12 '13 at 18:39
add comment

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.