Mysql Tuning for a small server

innodbmemorymemory-optimized-tablesmysql-5.5mysqltuner

I have a small Cloud Server running under Ubuntu 2 core, 2GB ram and 2GB swap, with Plesk Panel, Apache, Nginx as reverse proxy and Mysql 5.5. I have about 18 domains hosted here and each domain has his DB. Most of the websites are running under WordPress or Joomla.

All the websites together doesn't make more than 2000 visits per day.
My biggest problem is mysql as it looks like it is eating my RAM and server goes at swap for only 1 day up and as well the server doesn't look like it uses more than 70% of RAM but still it uses swap. Swappines is set to 10.

I have used mysqltuner.pl for tuning Mysql but i can't get it work as it should.

Here is mysqltuner output:

>>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0ubuntu0.14.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MEMORY tables: 0B (Tables: 2)
[--] Data in MyISAM tables: 21M (Tables: 284)
[--] Data in InnoDB tables: 170M (Tables: 920)
[!!] Total fragmented tables: 129

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 15h 23m 44s (632K q [4.458 qps], 11K conn, TX: 2B, RX: 167M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Total buffers: 1.0G global + 3.6M per thread (100 max threads)
[OK] Maximum reached memory usage: 1.1G (55.03% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (70.29% of installed RAM)
[OK] Slow queries: 0% (0/632K)
[OK] Highest usage of available connections: 16% (16/100)
[!!] Aborted connections: 4.23%  (473/11192)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 100K sorts)
[!!] Joins performed without indexes: 10841
[!!] Temporary tables created on disk: 49% (48K on disk / 98K total)
[OK] Thread cache hit rate: 99% (16 created / 11K connections)
[OK] Table cache hit rate: 80% (1K open / 2K opened)
[OK] Open file limit used: 7% (628/8K)
[OK] Table locks acquired immediately: 100% (802K immediate / 802K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/10.8M
[OK] Read Key buffer hit rate: 96.0% (2M cached / 115K reads)
[!!] Write Key buffer hit rate: 89.0% (27K cached / 3K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 512.0M/170.5M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 33.34% (10925 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 99.99% (78464393 hits/ 78474138 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 28642 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 1.0M, or always use indexes with joins)

free -m out:

             total       used       free     shared    buffers     cached
Mem:          1995       1727        267        282        245        692
-/+ buffers/cache:        789       1205
Swap:         1951        370       1581

I will really appreciate any suggestions.
Thank you for you time 🙂

Mysql Global Variables.
As there are about 307 lines i added them to Pastebin:
http://pastebin.com/bVUUG9zL

I found this on askubuntu, can it be possible?
Cuz i checked the server monitor logs and the maximum RAM usage was 25%.

Best Answer

I agree with already done advice - check runner script advices only if You full understand what happens, but most important other.

Configuration parameters give only one side view of problem, other it is - what really happens, and what really give loading?

I just suggest us one of tools for check and collect statistics from the running server:

MONyog - https://www.webyog.com/product/monyog
JetProfiler - https://www.jetprofiler.com
Neor Profiler - http://www.profilesql.com

it allow You identify and split problems by 80/20 rules 1-2 queries, can easily take 80-90% of loading, simple - queries NOT using indexes

and this is give You exactly information - which parameters better to tune from suggested by runner script.

UPDATE 2013-06-20 02:39 EDT

Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.

Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.

SUGGESTION

Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.

Give it a Try !!!

MySQL not releasing memory

First all take a look at the InnoDB Architecture (courtesy of Percona CTP Vadim Tkachenko)

InnoDB Architecture

InnoDB

Your status for the Buffer Pool says

Buffer pool size 1310719

That's your Buffer Size in Pages. Each page is 16K. That turns out 20G - 16K.

Please note the the following: You pushed data into the InnoDB Buffer Pool. What changed ?

Buffer pool size   1310719 
Free buffers       271419 (It was 347984)
Database pages     1011886 (Is was 936740)
Old database pages 373510 (It was 345808)
Modified db pages  4262 (It was 0)

Also, note the difference between the Buffer Pool Size in Pages.

1310719 (Buffer pool size) - 1011886 (Database pages) = 298833

That's 298833 InnoDB pages. How much space is that ???

mysql> select FORMAT(((1310719  - 1011886) * 16384) / power(1024,3),3) SpaceUsed;
+-----------+
| SpaceUsed |
+-----------+
| 4.560     |
+-----------+

That's 4.56GB. That space is used for the Insert Buffer Section of the InnoDB Buffer Pool (a.k.a. Change Buffer). This is used to mitigate changes to nonunique indexes into the System Tablespace File (which all have come to know as ibdata1).

The InnoDB Storage Engine is managing the Buffer Pool's internals. Therefore, InnoDB will never surpass 62.5% of RAM. What is more, the RAM for the Buffer Pool is never given back.

WHERE IS THE 70.2% OF RAM COMING FROM ???

Look back at the output of mysqltuner.pl at these lines

[OK] Maximum possible memory usage: 22.6G (82% of installed RAM)
Key buffer size / total MyISAM indexes: 2.0G/58.7M
[--] Total buffers: 22.2G global + 2.7M per thread (151 max threads)

mysqld has three major ways of allocating RAM

You set 20G of the InnoDB Buffer Pool
You have 2G for MyISAM Key Cache
The remaining 0.6G comes from 151 (max_connections) times (2.7M per DB Connection or thread). The 2.7M comes from (join_buffer_size + sort_buffer_size + read_buffer_size)

Any small spike in DB Connections will raise RAM past the 62.5% threshold you see for InnoDB.

MyISAM (Side Note)

What catches my eye is

Key buffer size / total MyISAM indexes: 2.0G/58.7M

Since you have so little indexes for MyISAM. You could set the key_buffer_size to 64M.

You do not need to restart mysql for that. Just run

SET GLOBAL key_buffer_size = 1024 * 1024 * 64;

Then, modify this in my.cnf

[mysqld]
key_Buffer_size = 64M

This will give the OS 2GB of RAM. Your VM will simply love you for it !!!

Give it a Try !!!

CAVEAT

Running FLUSH TABLES on InnoDB tables simply closes files against the .ibd files. This will not really push changes directly. The changes have to migrate its way through the pipes of InnoDB. This is why you see the spike in Modified db pages. The 4262 changed pages (66.59 MB) gets flushed when InnoDB's scheduless its flush.

Best Answer

Related Solutions

MySQL using too much CPU

UPDATE 2013-06-20 02:39 EDT

SUGGESTION

MySQL not releasing memory

InnoDB

WHERE IS THE 70.2% OF RAM COMING FROM ???

MyISAM (Side Note)

Give it a Try !!!

CAVEAT

Related Question