4

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

1

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!

  • Thanks a lot I ve seen that key_buffer size is the most important variable in a lot of recommandations. I ve run the query recommanded for key_buffer size and the result is 3967M The best software you know is MySQLTuner ? – Jacques Zaoui Aug 7 '14 at 15:42
  • 1
    Just a small edition to my edit - I didn't respond to your specific question about is 4GB the best size in your case? Anybody who says that they can give you a definitive answer to this question based on the available information, is either ignorant or being untruthful. It may well help - I urge you to test and see with your system, your usage patterns, your disk array(s), your network configuration. I would again point you to the warning that I copied from the tool's author - the script cannot replace "a knowledgeable DBA or system administrator that you trust". – Vérace Aug 7 '14 at 18:55
  • @Jacques Zaoui I highly recommend you not to use mysqltuner, and this is why: mysqlperformanceblog.com/2010/02/28/… There are good guides out there to help you tune mysql: mysqlperformanceblog.com/2006/09/29/… mysqlperformanceblog.com/2014/01/28/… or you can ask for expert advice. I agree 100% with Vérace's last sentence. – jynus Aug 7 '14 at 19:00
  • @jynus - nice references (as usual). To echo Schwartz's (your 1st ref.) point about "scientificness" - here's a post which looks authoritative but conflicts significantly with Peter Zaitsev's post (2nd ref in your comment). Schwartz's (2006) points about timings are well made, but doesn't the Performance Schema go some way to mitigating that issue in more more recent MySQL releases (first appeared c. early 2010)? – Vérace Aug 7 '14 at 19:25
  • 1
    @jynus - Indeed - as some of the older, wiser hands on the Oracle lists used to say to the newbies - "There is no global GO_FASTER variable - if there was, it would always be set to true and not be in the V$Parameter table!". I was just suggesting that the P_S is a step forward in the right direction to help the DBA better tune these variables in MySQL. Schwartz's point was (essentially AIUI) that a lack of precise timings made guesswork inevitable "Here are some suggestions that are about as good as any:" (visions of DBAs flailing about...). I said it was better, not perfect. – Vérace Aug 7 '14 at 19:45
0

Your use of MySQLCalculator.com would be the best 2 minutes you can invest in first step of tuning your system. Only use values larger than defaults if you know HOW they will improve your use of MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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