Basic MySQL Performance Monitoring

Basic MySQL Performance Monitoring

regular Ubuntu cli tools

  • mysqladmin status – mysqladmin status -uroot -p

MySQL command line interface tools

mysql -uroot -p

to open the command line.

SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Qcache_%';

cli tools

  • mytop – top for MySQL. Install using: sudo apt-get install mytop (assuming Ubuntu operating system). There is a very useful setting file that can be used to set parameters instead of having to include them in each command. Save the file as ~/.mytop.
  • MySQLTuner – provides suggestions on performance improvements and my.cnf settings by analyzing data on your mysql database server.

Setting considerations

  • If Open_tables (SHOW GLOBAL STATUS will show this) is equal to your
    table_cache size

    (set in /etc/mysql/my.cnf) that means it is being capped by your setting. The more MySQL has to read the table from disk the more IO and slower response, so if you have available RAM increasing the table_cache size may well make a big difference.

  • Key_reads/Key_read_request ratio should normally be < 0.01 (per MySQL manual, this means that nearly all key requests are taken from RAM). You can get both values using SHOW GLOBAL STATUS and then calculate the ratio. If the ratio is too high, consider increasing the key_buffer (in /etc/mysql/my.sql).
  • key_writes/key_writes_request should normally be near 1 (per MySQL manual)

Phusion Passenger Tips and Troubleshooting Ideas

Some tips and troubleshooting ideas for Phusion Passenger

Phusion Passenger manages resources for rails applications – spawning new instances as needed, etc..

  • passenger-status

    – provide the status of passenger rails processes

Configuring Phusion Passenger

Add lines to /etc/apache2/apache2.conf to change the default settings

  • PassengerMaxPoolSize 10

    – maximum number of total rails application instances, the default is 6

  • PassengerMaxInstancesPerApp 5 – sets the maximum pool size for any 1 rails application to 10 instances (default is no limit).
  • PassengerUseGlobalQueue ON

    – sets globaly queing on, it is off by default. You want globaly queuing on if your requests have large differences in response times (slow and fast responses).

Related: Passenger documentation

Troubleshooting

If you try

sudo passenger-status

and get something like
*** ERROR: Cannot query status for Passenger instance 2280:
Connection refused – /tmp/passenger.2280/info/status.socket
Restarting (not reloading) apache

sudo /etc/init.d/apache2 restart

may fix the problem.

System Monitoring Tools for VPS

Tools for monitoring performance and troubleshooting Ubuntu VPS web servers

  • Munin – graphs of system resources over time. Very nice. Can be a bit difficult to setup.
  • top – system stats
  • iotop – like top, but for io stats. Install
    sudo apt-get install iotop

    Useful setup

    iotop -b -o -d 30 -t

    -b (batch – so you can keep a running tally of results) -o (only those processes with io) -d (delay and seconds – how often to print out stats) -t (include time in printout)

  • vmstat – stats on memory, io, swap, cpu and system. Example:
    vmstat 10

    (prints out stats every ten seconds.

  • iostat

Error logs

  • sudo nano /var/log/apache2/error.log

Apache web server access log statistics

  • Webalizer –
    sudo apt-get install webalizer

    GeoIP is required for webalizer

    sudo apt-get install geoip-bin

    detailed instructions