I/O Wait Problem

A

Ayb

New Member
Hallo,

Nach einen Server Umzug von einem KVM Server mit SSD auf einen Dedicated Server mit RAID10 ohne SSD haben wir nun plötzlich extreme Schwankungen des I/O Wait => Load ist aber normal genauso wie der Ram.

Mal läuft alles für 1-2 Stunden normal, der Wert schwankt im Sekundentakt zwischen 1 und 3 und dann irgendwann haben wir Werte von 12 bis 20 die sich Sekunden lang halten bevor Sie wieder zurück fallen und die Performance der Webseite in den keller zieht.

Laut iotop ist in diesem Moment der mysql Prozess am Arbeiten.

Alle HDD wurde bereits durch neue ersetzt daran sollte es nicht liegen.

Jemand ne Idee wie wir das ändern können?
 

Attachments

  • mytop.png
    mytop.png
    41.7 KB · Views: 242
  • io.png
    io.png
    72.4 KB · Views: 231
  • iowait.PNG
    iowait.PNG
    25.4 KB · Views: 189
Last edited by a moderator:
Naja - natürlich ist dein Storage-Unterbau jetzt erheblich langsamer als vorher. Dedizierte Server sind guten virtuellen Servern im mittleren Preissegment heutzutage oftmals in vielerlei Hinsicht unterlegen.

Ich würde mit den bewährten Tools wie Tuning-Primer schauen, ob das Caching von MySQL verbessert werden kann, ansonsten wirst du vermutlich mit den Peaks leben oder applikationsseitig etwas (sprich an den verursachenden Queries) verbessern müssen. Auch phpMyAdmin bietet einen solchen "Guide" durch diverse suboptimale Direktiven an. Joe User hatte hier auch mal eine grundsolide Standardkonfiguration für den mysqld gepostet.

Ebenso kannst du prüfen, ob bestimmte Tabellen in für deine Zwecke performantere Storage-Engines gewandelt werden können.

Ergänzend: Welche Platten sind denn im Server?
 
Naja, ich hatte eigentlich gedacht das das neue Setup gegen einen KVM Server von Contabo doch genauso gut abschneiden wird, zumal die Read/Write bei Contabo gedrosselt ist pro Kunde.

Es sind alle WD Festplatten im Server, diese wurde vor 2 Tagen durch IP-Project erst getauscht.

Edit:

Code: 
max_allowed_packet=100M

max_connections         = 300
thread_cache_size       = 2

max_allowed_packet      = 16M
key_buffer_size         = 128M
read_buffer_size        = 4M
read_rnd_buffer_size    = 512K
sort_buffer_size        = 2M
join_buffer_size        = 1M

query_cache_size        = 128M
query_cache_limit       = 512M
query_cache_type        = 1
#open_files_limit       = 5000
table_cache             = 12000
table_definition_cache  = 12000

max_heap_table_size     = 64M
tmp_table_size          = 64M

innodb_file_per_table   = 1
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
open_files_limit = 200000

Code: 
[root@srv1 ~]# perl mysqltuner.pl
 >>  MySQLTuner 1.7.0 - 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.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(73K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 444 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 4 error(s).
[--] 9 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170222 13:46:29 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 170222 13:40:14 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 170222 11:49:24 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 170222 11:06:50 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 170220 17:00:46 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 170218 18:30:48 [Note] /usr/libexec/mysqld: ready for connections.
[--] 7) 170218 14:47:25 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8) 170117 13:20:10 [Note] /usr/libexec/mysqld: ready for connections.
[--] 9) 170114  7:53:07 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170222 13:46:26 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 170222 13:38:13 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 170222 11:49:21 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 170222 11:06:47 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 170220 16:52:59 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 6) 170218 18:23:45 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 7) 170218 14:38:49 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 8) 170117 14:15:07 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 136M (Tables: 1047)
[--] Data in InnoDB tables: 1G (Tables: 5250)
[--] Data in MEMORY tables: 0B (Tables: 54)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'datenbanbtest@%' has no password set.
[!!] User 'datenbanbtest@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 24s (444 q [18.500 qps], 20 conn, TX: 2M, RX: 122K)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 31.1G
[--] Max MySQL memory    : 3.0G
[--] Other process memory: 6.0G
[--] Total buffers: 716.0M global + 7.8M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 754.9M (2.37% of installed RAM)
[OK] Maximum possible memory usage: 3.0G (9.57% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/444)
[OK] Highest usage of available connections: 1% (5/300)
[!!] Aborted connections: 5.00%  (1/20)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 9.3% (36 cached / 389 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36 sorts)
[!!] Joins performed without indexes: 6
[!!] Temporary tables created on disk: 42% (21 on disk / 50 total)
[OK] Thread cache hit rate: 75% (5 created / 20 connections)
[OK] Table cache hit rate: 131% (108 open / 82 opened)
[OK] Open file limit used: 0% (54/10K)
[OK] Table locks acquired immediately: 100% (453 immediate / 453 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (5.5.52-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/16.5M
[!!] Read Key buffer hit rate: 88.5% (530 cached / 61 reads)
[!!] Write Key buffer hit rate: 0.0% (20 cached / 20 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 256.0M/1.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.90625 %): 5.0M * 2/256.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 83.26% (38143 hits/ 45814 total)
[!!] InnoDB Write Log efficiency: 61.11% (22 hits/ 36 total)
[OK] InnoDB log waits: 0.00% (0 waits / 14 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 93.7% (126 cached / 8 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

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

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Control error line(s) into /var/log/mariadb/mariadb.log file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'xxxxxxxxxx' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@xxxxxxxx
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_type (=0)
    query_cache_limit (> 512M, or use smaller result sets)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    performance_schema = OFF disable PFS
    innodb_buffer_pool_size (>= 1G) if possible.
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=128M) if possible.

Code: 
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.52-MariaDB x86_64

Uptime = 0 days 0 hrs 16 min 18 sec
Avg. qps = 45
Total Questions = 44166
Threads Connected = 5

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 4 out of 44197 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 2
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 6
Historic max_used_connections = 7
The number of used connections is 2% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 336 M
Current InnoDB data space = 1.17 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 256 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 586 M
Configured Max Per-thread Buffers : 2.27 G
Configured Max Global Buffers : 524 M
Configured Max Memory Limit : 2.79 G
Physical Memory : 31.13 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 16 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 16
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 21 M
Current query_cache_limit = 512 M
Current Query cache Memory fill ratio = 16.59 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 512 K
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 1.00 M
You have had 113 queries where a join could not use an index properly
You have had 1 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 12000 tables
Current table_definition_cache = 512 tables
You have a total of 6392 tables
You have 6457 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 3395 temp tables, 30% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 4 M
Current table scan ratio = 15 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 22446
Your table locking seems to be fine
 
Last edited by a moderator:
Naja - natürlich ist dein Storage-Unterbau jetzt erheblich langsamer als vorher. Dedizierte Server sind guten virtuellen Servern im mittleren Preissegment heutzutage oftmals in vielerlei Hinsicht unterlegen.
Click to expand...

*hust* Das worauf die virtuelle Maschine läuft ist auch letztlich nur ein dedicated Server. Nur ist die Frage, was man für eine Storage einsetzt.

Zudem es darauf ankommt, brauche ich Geschwindigkeit oder Speicher. Ein 2 TB Storage Array mit SSD Festplatten kostet nach wie vor ein vielfaches mehr als ein RAID 10 mit HDDs. Es kommt eben immer auf den Anwendungszweck an.

Tendentiell ist es allerdings so. Du musst insgesamt unterscheiden zwischen IOPS und Schreib / Lesegeschwindigkeit. Das ist generell nicht das Selbe. Stelle ich eine IO Anfrage an ein RAID 10 Array mit normalen HDDs, erreiche ich genauso eine Geschwindigkeit zwischen 200-300 MB/s. Problematisch wird es nur, stelle ich 1000 Anfragen pro Sekunde an das HDD Array. Dann kommt es zu einen I/O Wait da die HDD die Informationen nicht so schnell verarbeiten kann wie diese angefragt werden. Das ist natürlich der Mechanik in der Festplatte geschuldet.

Wie Tim aber richtigerweise sagt, kann man durch Caches sehr viel erreichen. Der Einsatz von Memcache, verschiedenen Optimierungen im MySQL Server kann hier natürlich schon den gewünschten Geschwindigkeitsvorteil bringen. Hardwareseitig konnte ich bei Ihnen zumindest kein Problem feststellen und die HDD Benchmarks sehen generell auch ok aus.



dd if=/dev/zero of=/root/testfile bs=1G count=2 oflag=direct
2+0 records in
2+0 records out
2147483648 bytes (2.1 GB) copied, 10.1526 s, 212 MB/s


dd if=/dev/zero of=/root/testfile bs=512 count=4000 oflag=direct
4000+0 records in
4000+0 records out
2048000 bytes (2.0 MB) copied, 0.985376 s, 2.1 MB/s

hdparm -tT --direct /dev/sda
/dev/sda:
Timing O_DIRECT cached reads: 766 MB in 2.00 seconds = 382.40 MB/sec
Timing O_DIRECT disk reads: 1124 MB in 3.00 seconds = 374.60 MB/sec
 
Das sollte kein Vorwurf gegen euch sein :-)

Ich bin soweit sehr zufrieden auch das Großzüge tauschen der 4 HDD war sehr nett und hätte vielleicht das Problem beheben können.


P.S Du kannst mich hier auch ruhig mit "DU" ansprechen...
 
ok. Vielleicht schaut Ihr euch auch mal memcache an. Ich war gerade nochmal auf dem System und habe es geprüft. Der IO Wait scheint nicht permanent aufzutreten, es scheint daher so zu sein, dass hier wenige Abfragen für kurze Zeit sehr viele IO Anfragen starten. Das könnte man mit memcache durchaus in den Griff bekommen, wenn die häufig verwendeten Daten in den RAM ausgelagert werden.
 
Ausgehend von den geposteten mysqltuner.pl Werten, lässt sich bereits sagen, dass die key_buffer_size auf 256MB erweitert und der innodb_buffer_pool_size auf 2GB vergrössert werden sollte. Für weitere Optimierungen sind die Werte nicht aussagekräftig, da der MySQL keine 48 Stunden lief.

Ich würde empfehlen erstmal folgende Werte zu setzen:
Code: 
table_open_cache                = 16384
table_definition_cache          = 8192
max_allowed_packet              = 64M
key_buffer_size                 = 256M
myisam_sort_buffer_size         = 16M
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 256M
tmp_table_size                  = 256M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
innodb_buffer_pool_size         = 2G
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
Wenn der MySQLd mit diesen Werten mindestens 48 Stunden lief, dann bitte nochmal die Ausgaben von mysqltuner und tuning-primer posten.

Zusätzlich bitte darüber nachdenken, die letzten MyISAM Tabellen nach InnoDB zu wandeln, denn MyISAM ist nicht sehr IO freundlich und demnächst auch obsolet.
 
Danke..

Einige Dinge die du von der my.cnf gepostet hat sind gar nicht vorhanden? Diese einfach hinzufügen in der my.cnf?

hm welche MyISAM Tabelle meinst du da?

Edit: Okay nicht hinzufügen, dann startet er nicht mehr.....
 
Last edited by a moderator:
Ayb said:
Einige Dinge die du von der my.cnf gepostet hat sind gar nicht vorhanden? Diese einfach hinzufügen in der my.cnf?
Click to expand...
Ja.

Ayb said:
hm welche MyISAM Tabelle meinst du da?
Click to expand...
Laut mysqltuner hast Du noch 1047 MyISAM Tabellen und diese sollten, sofern es die zugehörigen Apps zulassen, möglichst bald nach InnoDB gewandelt werden.
 
Last edited by a moderator:
Wenn ich die Werte die fehlen hinzufüge startet er nicht mehr....

innodb_log_file_size = 256M

Den will er nicht...
 
Last edited by a moderator:
Ah okay, stimmt habs gerade auch gelesen..

Rein aus Neugier: Warum?
query_cache_type = 0
query_cache_size = 0
 
Hier die Ergebnisse

Code: 
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.52-MariaDB x86_64

Uptime = 1 days 17 hrs 12 min 4 sec
Avg. qps = 53
Total Questions = 7886066
Threads Connected = 6

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 0.500000 sec.
You have 138 out of 7886111 that take longer than 0.500000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 2
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 5
Historic max_used_connections = 29
The number of used connections is 9% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 337 M
Current InnoDB data space = 1.17 G
Current InnoDB buffer pool free = 23 %
Current innodb_buffer_pool_size = 2.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 2.43 G
Configured Max Per-thread Buffers : 1.62 G
Configured Max Global Buffers : 2.27 G
Configured Max Memory Limit : 3.89 G
Physical Memory : 31.28 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 16 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 673
Key buffer free ratio = 80 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 512 K
Sort buffer seems to be fine

JOINS
./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 516.00 K
You have had 23571 queries where a join could not use an index properly
You have had 224 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 16384 tables
Current table_definition_cache = 8192 tables
You have a total of 6392 tables
You have 12967 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 558001 temp tables, 37% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 256 K
Current table scan ratio = 63 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 115223
Your table locking seems to be fine

Code: 
 >>  MySQLTuner 1.7.0 - 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.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(94K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 479 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 22 error(s).
[--] 18 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170222 16:06:04 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 170222 15:56:17 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 170222 15:38:47 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 170222 15:38:18 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 170222 15:35:37 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 170222 15:33:48 [Note] /usr/libexec/mysqld: ready for connections.
[--] 7) 170222 15:01:34 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8) 170222 14:59:24 [Note] /usr/libexec/mysqld: ready for connections.
[--] 9) 170222 14:37:31 [Note] /usr/libexec/mysqld: ready for connections.
[--] 10) 170222 13:46:29 [Note] /usr/libexec/mysqld: ready for connections.
[--] 20 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170222 16:06:02 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 170222 15:54:16 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 170222 15:38:44 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 170222 15:38:15 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 170222 15:35:18 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 6) 170222 15:35:17 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 7) 170222 15:32:41 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 8) 170222 15:32:05 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 9) 170222 15:32:04 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 10) 170222 15:01:31 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 136M (Tables: 1035)
[--] Data in InnoDB tables: 1G (Tables: 5262)
[--] Data in MEMORY tables: 0B (Tables: 54)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'datenbanbtest@%' has no password set.
[!!] User 'datenbanbtest@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 17h 13m 49s (7M q [53.193 qps], 145K conn, TX: 47G, RX: 1G)
[--] Reads / Writes: 89% / 11%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 4.3G
[--] Other process memory: 14.9G
[--] Total buffers: 2.6G global + 5.5M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.8G (8.97% of installed RAM)
[OK] Maximum possible memory usage: 4.3G (13.64% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (139/7M)
[OK] Highest usage of available connections: 9% (29/300)
[OK] Aborted connections: 2.22%  (3241/145823)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 984K sorts)
[!!] Joins performed without indexes: 23811
[!!] Temporary tables created on disk: 59% (330K on disk / 558K total)
[OK] Thread cache hit rate: 94% (7K created / 145K connections)
[OK] Table cache hit rate: 93% (12K open / 13K opened)
[OK] Open file limit used: 32% (3K/10K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (5.5.52-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.7% (52M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/16.4M
[OK] Read Key buffer hit rate: 99.9% (13M cached / 20K reads)
[!!] Write Key buffer hit rate: 24.4% (59K cached / 44K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/1.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.48828125 %): 5.0M * 2/2.0G should be equal 25%
[!!] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.98% (512582784 hits/ 512673570 total)
[!!] InnoDB Write Log efficiency: 66.75% (1188433 hits/ 1780404 total)
[OK] InnoDB log waits: 0.00% (0 waits / 591971 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 95.9% (4M cached / 168K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

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

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Control error line(s) into /var/log/mariadb/mariadb.log file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'XXXXX' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@XXXXX
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    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
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    performance_schema = OFF disable PFS
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=1G) if possible.
    innodb_buffer_pool_instances(=2)
 
Das sieht schon erheblich besser aus und sollte vorerst an my.cnf Optimierung reichen.
Wobei zwei Werte sollten auf jeden Fall noch angepasst/ergänzt werden:
Code: 
innodb_buffer_pool_instances    = 4
innodb_log_file_size            = 256M

Jetzt noch die Apps optimieren (JOINs ohne INDEX verhindern, TEXT/BLOB Nutzung verringern, Queries allgemein optimieren) und wenn möglich über den UNIX-Socket statt dem Netzwerk zugreifen.
 
Naja bei über 100 Webseiten schwer die Apps zu verbessern, da es ein Kunden Server ist mit LiveConfig.
 
You must log in or register to reply here.
Back
Top