Re: OT: MySQL Healthcheck

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Tue, 26 Mar 2013 08:19:31 +0100
Message-ID: <51514C03.6060901_at_gmail.com>

On 25/03/2013 20:24, Guillermo Alan Bort wrote:

>    I've been asked (by a project manager) to perform a healthcheck on a
> MySQL database before we take over administration. I found myself googling
> for something as simple as a Healthcheck.

Hi Alan,
this is what I would do:

1. Security:
   • lock the user that owns the mysqld process (remove the password or lock the user) use mysqld_safe to start the server
   • check for passwordless accounts (the table is mysql.user)
   • check for users that can connect from any host (mysql.host = '%'), if possibile restrict the hostnames (or, even better, use an IP addresses)
   • make sure only administrative users can read .mysql_history file (prior to 5.6 statements containing password are logged in plain text)
   • make sure that only administrative users have write permissions on the datadir, the plugins dir and the configuration files (usually my.cnf on Unix)
2. General configuration:
   • datadir: the default is /var/lib/mysql, usually not a good choice
   • tmpdir: in most cases you don't have control over the size of the temporary tables on disk, I would use a separate dedicated filesystem (or /dev/shm for non-slave hosts)
     • in most default configurations some parameters need attention:
       1. if you use InnoDB (and you should), make sure innodb_buffer_size is big enough (this is the buffer cache for the InnoDB tables, for MyISAM tables only the indexes are cached - key_buffer_size - the OS cache is used for the data)
       2. usually the InnoDB log files (online redo log files in Oracle) are undersized (5M are ridiculous, 128M could be sufficient, but (just as Oracle) you may need a bigger ones (1G and more - if I recall correctly, only recent versions support > 4G); on the mysqlperformanceblog site you'll find a way to size it correctly
       3. as already mentioned by other posters, read the documentation about SQL_MODE, but usually that's not a parameter that can be changed on a live system easily
       4. in most cases the sql cache sould be disabled completely (sql_cache_size and type)
3. Administration and troubleshooting:
   • read mysqlperformanceblog
   • get familiar with the percona toolkit (I use pt-query-digest quite often)
   • when you have performance issues, enable the slow query log (with long_query_time=0 you'll get all the code (like trace 10046, dynamic parameter since 5 or 5.1, don't remember exactly); when the problems get serious, enable performance schema (enabled by default in 5.6); use explain (like Oracle's explain plan), use show engine innodb status (read the documentation and the articles by Percona to understand how to interpret their output and how to use them)

Note that I'm not affiliated with Percona in any way.

Hope this helps
Dimitre

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 26 2013 - 08:19:31 CET