Guten Morgen,
ich habe jetzt schon so viele Sachen versucht, und bin jetzt langsamm am verzweifeln.
Zu meinem Problem, ich habe jetzt schon viele Anpassungen an der Mysql Configuration durchgeführt,
aber leider bekomme ich immer wieder nach Zeit eine sehr Hohe Cpu auslastung.
Mehrere Mysql Prozesse teilweise zwischen 200-750% Cpu / Ram Benutzung ist ok ca. 50%.
Infos zum System:
Root: 1Gbps HP ProLiant DL120/Intel Quad-Core Xeon X3440 - 16GB Ram / Ubuntu Server Betriebssystem
Es gibt 3 Datenbanken (3 Webseiten) mit je 16 Tabellen | DB_1 = 35MB -- DB_2 = 560MB -- DB_3 = 3,4GB
Webseiten sind Optimiert (Datenbankabfragen / Cache usw.) | Besucher pro Tag ca. 15K/Tag je Seite
Auf dem Root läuft nur Nginx+PHP5-FPM und Mysql, und alle 8 Prozessoren sind komplett überlastet.
Mysqltuner Ausgabe:
Code
>> MySQLTuner 1.1.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
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.44-0ubuntu0.14.04.1-log
[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 InnoDB tables: 3G (Tables: 44)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 18
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 10m 13s (4M q [124.602 qps], 49K conn, TX: 67B, RX: 71B)
[--] Reads / Writes: 89% / 11%
[--] 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: 2% (95K/4M)
[OK] Highest usage of available connections: 65% (65/100)
[OK] Key buffer size / total MyISAM indexes: 256.0M/120.0K
[OK] Key buffer hit rate: 100.0% (245M cached / 0 reads)
[OK] Query cache efficiency: 58.1% (2M cached / 4M selects)
[!!] Query cache prunes per day: 298892
[OK] Sorts requiring temporary tables: 9% (45K temp sorts / 499K sorts)
[!!] Temporary tables created on disk: 36% (149K on disk / 406K total)
[OK] Thread cache hit rate: 99% (65 created / 49K connections)
[OK] Table cache hit rate: 96% (225 open / 232 opened)
[OK] Open file limit used: 5% (53/1K)
[OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 3.5G/7.8G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increasing the query_cache size over 128M may reduce performance
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clausesDie Mysql Configuration habe ich schon so oft geändert, hier einfach mal die aktuelle "my.cnf":
Code
[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 Ausgabe:
Code
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 |
+-------+---------+-----------+-------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+Ich versuche meine Probleme eigentlich immer selbst irgenwie zu lösen, aber leider bin ich da jetzt echt Ratlos.
Würde mich sehr freuen, wenn ihr mir da weiterhelfen könnt.
Beste Grüße Peter