Mysql – Tuning Mysql and Monitoring

database-tuningMySQLperformanceperformance-tuningquery-performance

I would like to know if anybody can point me towards a tool/way for MySQL database tuning.

Actually the tuning of my.cnf is like below:

My database engine is MyISAM | Mysql 5.1.73

  • key_buffer = 500M
  • sort_buffer_size = 256M
  • read_buffer_size = 256M
  • read_rnd_buffer_size = 256M
  • myisam_sort_buffer_size = 256M
  • thread_cache_size = 16
  • thread_concurrency = 32
  • tmp_table_size = 500M #default 33M!
  • max_heap_table_size = 500M
  • skip-name-resolve
  • skip-host-cache
  • myisam_recover = BACKUP,FORCE
  • query_cache_size=256M

My computer is:

Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 16 cores
Linux 2.6.32-5
Memory - 23.58 GB total

I also have had look at slow query but what do you recognize or recommend?

I've made the select:

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;

3967M

Best Answer

There is no way, there is only the journey! You could start here with MySQLTuner - it's a tuning script which issues recommendations - it doesn't modify anything on the server and I have read (can't find ref, sorry) that it's impact on performance while running is minimal. There's also this - but it seems to be dead.

I'll just repeat the warning that comes with MySQLTuner:

It is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's output, or if you don't understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.

There are many books out there on MySQL - the most highly recommended one at the moment is Schwartz et al's High Performance SQL. Continuous monitoring of your database is as important as tuning, so that when things do start to go wrong, you can at least begin to point the finger in the right direction.

Another thing that is very important to remember is that your MySQL database is just part of a system and it's also critical to be monitoring the various components of that system using Linux's tools for that iostat, vmstat, sysstat &c.

You say:

I also have had look at slow query but what do you recognize or recommend?

but we have no query, no tables (SHOW CREATE TABLE blah1, blah2...) - so it's difficult to help with that. This site is more for specific questions on technical issues rather than broad questions to which you might get a better answer from studying a text.

[EDIT] in response to OP's comments.

Take a look at the table of contents for Schwartz's book here. You'll see that it's a large book with a lot to say about MySQL. More importantly, it places the database in the context of a SYSTEM. I can't tell without more information, but you may need to change the key_buffer_size or your issues may not relate to that particular variable.

In the first instance, I have two recommendations for you - firstly, upgrade to 5.6. Then you'll have the Performance Schema at your fingertips allowing you to properly profile your application - after profiling, you can then proceed to tuning. Profiling is determining where (at a detailled level), your system spends its time - once you figure that out, you can then make rational logical decisions as to where to put your tuning efforts - it could be MySQL variables - it could be to increase RAM, CPU, disk throughput or network bandwidth (in conjunction with OS profiling as I mentioned above).

Secondly, I recommend that you move your system to InnoDB - it makes better use of the machine's resources and is better in almost every way - Googling "innodb vs myisam" will give you a truckload of links - take a look. MyISAM is nearing end of life - see here for remarks by Bill Karwin and Morgan Tocker - both big hitters in the MySQL world.

Then, read the book that I've recommended (yes, I know, groan...) and then post here if you have more specific questions.

Disclaimer: the usual warnings about testing changes before implementing them on a production system apply!

Related Question