Tuning Mysql and Monitoring 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&lt;0,0,IF(PowerOf1024&gt;3,0,PowerOf1024)))+0.4999),
    SUBSTR(' KMG',IF(PowerOf1024&lt;0,0,
    IF(PowerOf1024&gt;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
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 &amp;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!