DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Header Right

ubuntu

10 Database Performance Monitoring Tools You Can Get For Free

by Leave a Comment

Database performance monitoring is something every DBA worth their salt should be doing on a regular basis.

It should be adopted as a proactive task to help identify issues early on before they become too serious and be part of a post code deployment monitoring process.

Bundled in with linux based operating systems are a heap of great tools that you can use as a DBA to help performance monitor your database server. If you are not happy with what you get “out of the box”, you can also find some great database monitoring tools online that are available to download for free.

For this post, I’m going to talk about both MySQL and Linux operating system performance monitoring tools. In many scenarios, you’ll need both types in order to get a complete understanding of where the delays are in your system.

MySQL Performance Monitoring Tools

1/ MySQL slow query log
The mysql slow query log is absolutely brilliant for capturing slow queries hitting your MySQL databases.

You can log queries whose durations match the number you specify in my.cnf. So you can analyze queries which take more than 3 seconds for example.

Activate in my.cnf with customizable settings for log location, long query time and whether to log queries that do not use any indexes.

#slow query logging
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow-log
long-query-time = 3
log-queries-not-using-indexes = 0

Once you have been logging for a while you can aggregate the results with the mysqldumpslow utility,  optimize them and then monitor for improvements! 🙂

2/ MySQL Performance Schema
Introduced in version 5.5, the performance_schema database provides a way of querying internal execution of the server at run-time.

To enable add “performance_schema” to my.cnf

There are many objects to query, too many to talk about in this post. Check out the documentation here.

3/ The MySQL process list

To get an idea of how many processes are connected to your MySQL instance, what they are running and for how long, you can run SHOW FULL PROCESSLIST or alternatively read from the information_schema.processlist table.

mysql> SELECT user, host, time, info FROM information_schema.processlist;
+-------------+------------+-------+-------------------------------------------------------------------+
| user        | host       | time  | info                                                              |
+-------------+------------+-------+-------------------------------------------------------------------+
| root        | localhost  |     0 | SELECT user, host, time, info FROM information_schema.processlist |
| replication | srv1:46892 | 11843 | NULL                                                              |
+-------------+------------+-------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

4/ mtop
I love this utility, it provides a real-time view of the MySQL process list and updates according to the number of seconds your specify when you run it.

What I really like about it is that you can have it running on one screen and as problems occur, the colours of the threads change colour with red indicating that something has been running for some time.

There is a great article here about how to install it on different flavours of Linux as well as some detail on how to run it.

5/ SHOW STATUS
Like other command line tools, such as SHOW PROCESSLIST, you run these to get moment in time reports on different variable status’s.

For example, if you want to get information about the query cache, you can run :

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 9353       |
| Qcache_free_memory      | 93069936   |
| Qcache_hits             | 9719103977 |
| Qcache_inserts          | 1451857238 |
| Qcache_lowmem_prunes    | 897050960  |
| Qcache_not_cached       | 222234089  |
| Qcache_queries_in_cache | 20856      |
| Qcache_total_blocks     | 52497      |
+-------------------------+------------+
8 rows in set (0.00 sec)

This type of reporting can help you monitor specific areas of your MySQL instance. For example, if you wanted to know the query cache hit rate, you could get the numbers from above and calculate based on this formula:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

For more information, see this link.

Operating System Performance Monitoring Tools

6/ TOP
This will list running processes and the resources that they are consuming. It updates real-time and you can quickly gage if there are processes which are consuming large areas of resource in CPU and memory at a very high level.

top - 17:33:48 up 7 min,  1 user,  load average: 0.03, 0.04, 0.04
Tasks:  64 total,   1 running,  63 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    604332k total,   379280k used,   225052k free,    11724k buffers
Swap:        0k total,        0k used,        0k free,   135064k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  809 tomcat7   20   0 1407m 149m  13m S  0.3 25.4   0:10.99 java
 1153 ubuntu    20   0 81960 1592  756 S  0.3  0.3   0:00.01 sshd
 1318 root      20   0 17320 1256  972 R  0.3  0.2   0:00.07 top
    1 root      20   0 24340 2284 1344 S  0.0  0.4   0:00.39 init
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd
    3 root      20   0     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/0
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kworker/0:0
    5 root      20   0     0    0    0 S  0.0  0.0   0:00.01 kworker/u:0

7/ free
This utility helps to give you an idea whether you have a memory issue. Again this is another great tool for getting a high level view. I like to use “free -m” as it returns the numbers to me in megabytes instead of bytes. The information returned shows you in use, free and swap usage. It also shows what is in use by the kernel and buffers.

[email protected]:~# free -m
             total       used       free     shared    buffers     cached
Mem:           590        373        216          0         11        131
-/+ buffers/cache:        229        360
Swap:            0          0          0

8/ vmstat
This utility is very useful for monitoring many areas of the system, CPU, IO blocks and swap. I find it particularly good to monitor swap file usage.

Whilst “free” might tell you if there are any pages in the swap file, vmstat will tell you if your system is actively swapping.  Computers and servers do need to use their swap file but the less this happens, the better it is for your applications performance.

When you have a problem with swap, it is when it is being used constantly and can be a sign that you don’t have enough memory installed in your system.

By default, running vmstat will not give you a real time view of your system. So you need to add a figure to the command to give you a fresh read out in the number of seconds specified. In this example, I am specifying every 2 seconds.

[email protected]:~# vmstat 2
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0      0 221324  12556 135252    0    0    93    19   40   75  1  0 98  0
 0  0      0 221324  12556 135276    0    0     0     0   34   65  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0    24   38   64  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   32   56  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   33   56  0  0 100  0
 0  0      0 221324  12564 135280    0    0     0     0   30   55  0  1 100  0
 0  0      0 221324  12564 135280    0    0     0     0   35   59  0  0 100  0

The columns you are interested in are swap si and so. Which stands for “swap in” and “swap out”. These figures tell you what is being read in from disk swap file (si) and what is being swapped out to the swap file (so). Swapping is very slow I/O intensive process and you want to be doing some optimization somewhere or adding more memory if this is a problem.

Run “man vmstat” for a full list of features and documentation.

9/ sar
I love sar! It will capture you a whole bunch of metrics based on CPU time, CPU queues, RAM, IO and network activity. It will give you a point in time view of the resource usage in the form of a historical report.

The default time between report lines is 10 minutes but you can change that. It’s great for seeing whether you have any particularly heavy areas of resource pressure at any time in the day. You can also use it as a performance monitoring tool to measure the effects of optimizations to your system.

Some examples, run “man sar” for a full list of features and documentation on what each column header means.

sar -q (check CPU queue length)

11:20:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
11:30:01 AM         1       201      0.00      0.00      0.00
11:40:01 AM         1       200      0.00      0.00      0.00
11:50:01 AM         1       201      0.00      0.00      0.00
12:00:01 PM         2       201      0.00      0.00      0.00

sar -r (check RAM usage)

11:20:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
11:30:01 AM    151308   3765480     96.14     91416   1054136   2961684     49.25
11:40:01 AM    151076   3765712     96.14     91664   1054136   2961012     49.24
11:50:01 AM    150680   3766108     96.15     91888   1054148   2961152     49.24
12:00:01 PM    150704   3766084     96.15     92104   1054152   2961340     49.24

10/ iostat
This tool will you give you statistics for CPU and I/O for devices, partitions and network file systems. Great for knowing where the busiest drives are for example.

[email protected] ~# iostat
Linux 2.6.32-431.11.2.el6.x86_64 (vm1)        06/27/2014      _x86_64_        (4 CPU)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.23    0.00    0.07    0.10    0.00   99.60Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              11.78       785.38       450.12 1437054564  823620760
dm-0              1.00         1.35         6.67    2472280   12211040
dm-1             64.52       783.30       441.42 1433252442  807699512
dm-2              0.00         0.00         0.02       7658      29336
dm-3              0.27         0.53         2.01     978626    3680440

Finally

So there you have it – 10 really useful tools which you can utilize in your database performance monitoring efforts. There are many more but I’ve run out of time now. 🙂

How to Resize MySQL Innodb Log Files Without Errors

by 2 Comments

Upon installing MySQL, the default innodb log file size is 5MB. This might be fine for a lot of servers but should you want to resize it, it is not simply a case of changing innodb_log_file_size to the new size and restarting MySQL.

MySQL will not start and produce error log output similar to the following:

140610 20:09:14 InnoDB: The InnoDB memory heap is disabled
140610 20:09:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140610 20:09:14 InnoDB: Compressed tables use zlib 1.2.3.4
140610 20:09:14 InnoDB: Initializing buffer pool, size = 128.0M
140610 20:09:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
140610 20:09:14 [ERROR] Plugin 'InnoDB' init function returned error.
140610 20:09:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140610 20:09:14 [ERROR] Unknown/unsupported storage engine: InnoDB
140610 20:09:14 [ERROR] Aborting
140610 20:09:14 [Note] /usr/sbin/mysqld: Shutdown complete

MySQL will not start because the configured value in my.cnf is not the same as the current log file size.

How to safely adjust Innodb log file size

(Example demonstrated on a Ubuntu 12.04LTS instance)

1/ Make sure that innodb_fast_shutdown is NOT configured as 2

You may suffer data loss if this is the case. Change to either 0 or 1. Setting as 0 will take longer to shutdown the server so 1 is preferred. Check out more information on this at this link

SHOW VARIABLES LIKE 'innodb_fast_shutdown'

Change to 1 to perform a fast shutdown

SET GLOBAL innodb_fast_shutdown=1;

2/ Safely stop the MySQL service checking for issues

Stop MySQL and check the log file as defined in my.cnf  by the variable “log_error” for any issues.

example :-

log_error = /var/log/mysql/error.log

You want to see a nice clean shutdown

service mysql stop

Check the log, it should show a good, error free shutdown…

tail -10 /var/log/mysql/error.log


140610 20:19:44 [Note] /usr/sbin/mysqld: Normal shutdown
140610 20:19:44 [Note] Event Scheduler: Purging the queue. 0 events
140610 20:19:44  InnoDB: Starting shutdown...
140610 20:19:44  InnoDB: Shutdown completed; log sequence number 1595685
140610 20:19:44 [Note] /usr/sbin/mysqld: Shutdown complete

3/ Move the existing log files out of your MySQL “datadir” folder

You need to move these, don’t delete them. The innodb log files are found in your “datadir” folder as defined in my.cnf

They are ib_logfile0, ib_logfile1 etc

cd /var/lib/mysql
mv ib_logfile0 ib_logfile1 /home/ubuntu

4/ Change innodb_log_file_size variable in my.cnf and start MySQL

Set the size that you need for your server, I’m setting to 64M in this case.

innodb_log_file_size = 64M

Start MySQL, it should start cleanly without error

service mysql start

5/ Now check your servers log file again

It should look similar to the below where the log will show MySQL creating new log files

140610 20:51:17  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:24 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140610 20:51:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140610 20:51:24  InnoDB: Waiting for the background threads to start
140610 20:51:25 InnoDB: 5.5.37 started; log sequence number 1596428

Finally

Check that you can query your innodb tables and then it should be safe to remove the old log files which you backed up earlier.

How to Install MySQL 5.5 on Ubuntu Server 12.04 LTS

by 4 Comments

For this how to, I am going to document how to install MySQL on an installation of Ubuntu 12.04 LTS

Installing it is a very straight forward and quick process and I prefer to use the terminal. If you are running a GUI, you can also install it through the Ubuntu sofware centre. Typically available with the Ubuntu desktop version.

If you are running the desktop version of Ubuntu, fire up a terminal either by clicking on the icon or by pressing Ctrl + Alt + T

It’s a good idea to update your server package cache before proceeding.

sudo apt-get update

Then, run

sudo apt-get install mysql-server-5.5

Once you have entered the password for sudo access, the installation will proceed. The only thing it will ask you is what to set the root password for MySQL as. You SHOULD set a password up, don’t leave it blank with the intention of doing it later. I would recommend you find a decent password generator too.

Once installation has completed, you just need to check that mysql is running. To do this, run

sudo netstat -tap | grep mysql

If MySQL is running you will see output similar to the following:

tcp  0  0 localhost:mysql  *:*  LISTEN  2480/mysqld

Now test that you can login

mysql -uroot -p

Type in the root password directly after the -p switch without spaces.

You can also view what packages have been installed by typing:

dpkg -l | grep mysql

Which should return output similar to the following:

[email protected]:~$ dpkg -l | grep mysql
ii  libdbd-mysql-perl                4.020-1build2                     Perl5 database interface to the MySQL database
ii  libmysqlclient18                 5.5.35-0ubuntu0.12.04.1           MySQL database client library
ii  mysql-client-5.5                 5.5.35-0ubuntu0.12.04.1           MySQL database client binaries
ii  mysql-client-core-5.5            5.5.35-0ubuntu0.12.04.1           MySQL database core client binaries
ii  mysql-common                     5.5.35-0ubuntu0.12.04.1           MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                     5.5.35-0ubuntu0.12.04.1           MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                 5.5.35-0ubuntu0.12.04.1           MySQL database server binaries and system database setup
ii  mysql-server-core-5.5            5.5.35-0ubuntu0.12.04.1           MySQL database server binaries

Job done, you have just installed MySQL 5.5 on Ubuntu 12.04 LTS

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close