2

I have already done a lot of adjustments to the MySQL Configuration, but unfortunately I always get back to time utilization very high CPU.

Multiple MySQL processes partially between 200-750% CPU / Ram usage is ok about 50%.

Information on the system:

Root: HP ProLiant DL120 1Gbps / Intel Quad-Core Xeon X3440 - 16GB Ram / Ubuntu Server operating system

There are 3 databases (3 sites), each with 16 tables | DB_1 = 35MB - 560MB DB_2 = - = DB_3 3,4GB

Websites are optimized (database queries / cache, etc.) | visitors per day about 15K / day per page

At the root runs only Nginx + PHP5-FPM and mySQL, and all 8 processors are completely overloaded.

MySQLTuner edition:

>> MySQLTuner 1.1.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.44-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 65K (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 3G (Tables: 44)
[!!] Total fragmented tables: 18

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4s (991 q [247.750 qps], 73 conn, TX: 8M, RX: 91K)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 12.5G global + 3.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 12.8G (81% of installed RAM)
[OK] Slow queries: 0% (8/991)
[OK] Highest usage of available connections: 28% (28/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/120.0K
[!!] Query cache efficiency: 17.8% (155 cached / 873 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26 sorts)
[OK] Temporary tables created on disk: 20% (61 on disk / 302 total)
[OK] Thread cache hit rate: 61% (28 created / 73 connections)
[OK] Table cache hit rate: 95% (138 open / 145 opened)
[OK] Open file limit used: 5% (52/1K)
[OK] Table locks acquired immediately: 100% (712 immediate / 712 locks)
[OK] InnoDB data size / buffer pool: 3.4G/7.8G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

MySQL Configuration I have so often changed, can not think of the current "my.cnf":

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice    = 0


[mysqld]
user    = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port    = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address    = 127.0.0.1

# Tunning #

tmp_table_size = 4000M
max_heap_table_size = 4200M
max_connections = 100
max_allowed_packet = 16M

thread_cache_size = 32M
query_cache_size = 512M
query_cache_min_res_unit    = 4K

sort_buffer_size    = 2M
join_buffer_size    = 128K

preload_buffer_size = 32K
key_buffer_size = 256M
read_buffer_size    = 1M
read_rnd_buffer_size    = 256K

low_priority_updates    = 1
concurrent_insert   = ALWAYS


# INNODB #
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_file_per_table = 1
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 8000M

innodb_read_io_threads  = 16
innodb_write_io_threads = 16
innodb_thread_concurrency   = 0


# Logging
log_warnings = 2
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 20


# Binary Log / Replication
server_id = 1
binlog_cache_size = 1M 
sync_binlog = 8
binlog_format = row
expire_logs_days = 7
max_binlog_size = 128M 
relay-log = /var/log/mysql/slave-relay.log
relay-log-index = /var/log/mysql/slave-relay-log.index 


[mysqldump]
quick
single-transaction
max_allowed_packet = 8M


[mysql]
no_auto_rehash


[myisamchk]
key_buffer = 265M
read_buffer = 8M
write_buffer = 8M


[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/error.log

!includedir /etc/mysql/conf.d/

key_buffer  = 8M
max_allowed_packet  = 8M
thread_stack    = 192K
thread_cache_size = 4


[mysqldump]
quick
quote-names
max_allowed_packet  = 8M


[mysql]
#no-auto-rehash # faster start of mysql but no tab completition


[isamchk]
key_buffer  = 8M

!includedir /etc/mysql/conf.d/

PROCESSLIST Output:

mysql> SHOW PROCESSLIST;
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id    | User    | Host      | db          | Command | Time | State                | Info                                                                                                 |
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
|   251 | pb_root | localhost | db_name     | Sleep   |    1 |                      | NULL                                                                                                 |
| 48726 | root    | localhost | NULL        | Query   |    0 | NULL                 | SHOW PROCESSLIST                                                                                     |
| 48775 | pb_root | localhost | db_name     | Sleep   |    0 |                      | NULL                                                                                                 |
| 48838 | ww_root | localhost | db_name     | Query   |    1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 48845 | ww_root | localhost | db_name     | Query   |    0 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 48846 | ww_root | localhost | db_name     | Query   |    0 | preparing            | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND (wp_posts.ID NOT IN (          |
| 48847 | ww_root | localhost | db_name     | Sleep   |    0 |                      | NULL                                                                                                 |
| 48854 | pb_root | localhost | db_name     | Sleep   |    1 |                      | NULL                                                                                                 |
| 48735 | pb_root | localhost | db_name     | Sleep   |    0 |                      | NULL                                                                                                 |
| 48878 | ww_root | localhost | db_name     | Query   |    1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 48845 | pb_root | localhost | db_name     | Query   |    0 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 48827 | ww_root | localhost | db_name     | Sleep   |    0 |                      | NULL                                                                                                 |

+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2
  • I know it's a basic, but... what table engine temporary tables utilize? Sep 2, 2015 at 6:18
  • You appear to be running WordPress and with very large databases. WP isn't known for handling large DBs very well. Note the SQL_CALC_FOUND_ROWS and copying to tmp table. It's struggling to measure your tables and may not be able to use indexes on those. You should check indexing on those and perhaps try to avoid hitting the DB for mundane things, through caching. Like Falcon Cache for example which will store full page cache on disk via Nginx or Apache.
    – JayMcTee
    Sep 2, 2015 at 10:22

0

You must log in to answer this question.

Browse other questions tagged .