Tuesday, July 2, 2013

Understand your MySQL Server

I figured it might be helpful to write out a few guidelines to help everyone understand different aspects of their MySQL server.

This data has been gathered over time and I will do my best to reference posts. Overall consider your best sources of information to be listed here:
So for the sake of this example let us assume you  have just taken over responsibility for a MySQL server. What is the best way to gather valid need to know information...

Do you know the password to the database ? If it is not using the mysql_config_editor (so pre 5.6) you can do a little digging to find out. Otherwise you have to restart it and force your way in. We assume you do not want to restart it. If you have to figure out how to use --skip-grant-tables review some previous blog posts with examples: http://anothermysqldba.blogspot.com/search/label/--skip-grant-tables

So first some placed to look for the password. This also means that if you do one of these people can find the password later.
  • find out if someone used the password on the command line. 
    • history | grep mysql
  • crontab -l
    • If you see backup scripts or etc look in those scripts for a password 
  • locate .my.cnf 
  • cat .bash_history | grep mysql
  • try mysql without a password but of course the hope is your box is secure.
  • With MySQL 5.6 check if you have a configured account already 
    • mysql_config_editor print --all 
OK so you have the account information and you are on the box.
Before you jump into the MySQL server know a little about your server first.
Some of this information you might just know but to be safe.
Run the following on a Linux system for example to get the OS, Distro , Memory, disk information.
  • cat /etc/*-release
  • uname -a
  • df -ah
  • mount
  • free -m
  • top
  • Look over this post for Swappiness info http://www.mysqlplus.net/tag/swappiness/
  • Do you have a specific cnf file being used?
    • ps -ef | grep mysql | grep cnf
    • Datadir : 
      • ps -ef | grep mysql | grep datadir

So now jump into the MySQL server and start to gather some data. While a review of the my.cnf file can give you most of this information you can also gather it from the server.

show variables like '%log%';


Look over your error (.err) file to review any problems. This should be the first place you look after an error with start up and etc.

So how is the server running? To quickly gather the following run the status command.
  • Version
  • Threads
  • Questions
  • Slow queries
  • Opens
  • Flush tables
  • Open tables
  • Queries per second avg

mysql> status


The "mysqladmin extended-status" command can also give you information to use grep with but inside the server is also valid.

Keep an eye on the following information.
Some of this is also posted here http://www.techrepublic.com/blog/opensource/10-mysql-variables-that-you-should-monitor/56

You can review the post mentioned to see a nice table that explains some of these values.  These are the ones I think you should keep an eye on though.

> show status like 'Handler_read_first';
> show status like 'Key_reads';
> show status like 'Select_full_join';


Find out the percentage overall of your queries are running slow. Hopefully this is a small number.
Use the status information to populate these variables for a quick check. 

 set @slow_queries=<value in the status output>;
set @questions=<value in the status output>;
SELECT ROUND(100* (@slow_queries /  @questions),2) "Slow Query percentage" \G


Learn to understand the output from : SHOW ENGINE INNODB STATUS\G
Keep an eye on deadlock information so you can start to debug those.

Does your system have triggers, events, stored procedures?

mysql> SELECT db, name FROM mysql.proc;
mysql> SELECT TRIGGER_SCHEMA , TRIGGER_NAME  FROM information_schema.TRIGGERS ;
mysql> SELECT EVENT_NAME FROM information_schema.EVENTS ;


Know your server variables

            show variables like '%innodb%';
            show variables like '%thread%';
            show variables like '%table%';
            show variables like '%buffer%';
            show variables like '%cache%';


Check your user accounts.
Do they all have valid passwords.

SELECT Host, User ,
CASE
WHEN  CHARACTER_LENGTH(Password) = 16 THEN 'Pre-4.1 I should update this'
WHEN  CHARACTER_LENGTH(Password) > 16 THEN 'Valid password'
WHEN  CHARACTER_LENGTH(Password) =0 THEN ' BLANK PASSWORD so I just do not care if people steal my stuff'
END as Password
FROM mysql.user;


Understand your InnoDB log file and figure out a good buffer pool size.
Baron posted about this here: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
Read his post to understand more about this. Below is an example of how to use this information as well. I used his numbers for the example to help.  Like Baron said, run this when traffic is heavy to get valid information.

mysql>pager grep sequence; show engine innodb status\G select sleep(60); show engine innodb status\G  pager;
mysql>SET @sequence1= 3836410803;
mysql>SET @sequence2= 3838334638;
mysql>select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) /2 as innodb_log_file_size_used ;
mysql>select (@@innodb_log_file_size / 1024) / 1024 as current_log_file_MB_size;


Buffer pool size.
While the following query is posted around the net and does give a valid starting point for a buffer pool, I have also seen some invalid or unrealistic numbers based on the server it had.  Personally, review the results of the query below. Review the results of peak traffic and the amount data you are sending to the logs per Baron's post. Then take into account how much memory you have on the server.  The more your database runs in memory the faster it is to get results, but you have to take into account what else is the database doing. So you have to use your own judgement and research to get a good value for the buffer pool size. Another option could be to use the tools.percona.com, answer the questions and see what it tells you .


mysql>select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) *2  as innodb_buffer_pool_GB_test ;

mysql>SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B \G



Next you should review this open ark blog post to dig into your indexes and etc more. Great information on that post and site overall.
http://code.openark.org/blog/mysql/useful-database-analysis-queries-with-information_schema


To dig into more data about your server....


What are the oldest tables, maybe need to archive those?

SELECT CONCAT(`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','test','performance_schema')  AND `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 25;



What is taking most the space?

SELECT concat(table_schema,'.',table_name) table_name,
concat(round(data_length/(1024*1024),2),'M') data_length
FROM information_schema.TABLES
ORDER BY data_length DESC LIMIT 5;


This is just a starting point for you to understand what is going on with your server. Use the sites listed within this site to learn more.

Posted by at
Labels: