- Registriert
- 2. Jan. 2016
- Beiträge
- 72
- Punkte
- 48
Hi, ich habe seit der umstellung von vb zu xenforo das problem, das vieles im admin sehr lang dauert wie zb. Änderung der gruppen Rechte dauert 3 Minuten, das Installieren/Deinstallieren dauert 5 und mehr minuten. Ich gehe mal davon aus, das hier das schreiben/suchen in der SQL zu lange dauert was ich jetzt prüfen will.
Meine Server Daten
File Server
SQL Server
Beide Server stehen im Rechenzentrum nebeneinander und sind direkt mit einander verbunden.
Hier noch ein kurzer auszug meiner slow querys
Nun die frage, habe ich etwas übersehen das änderung so schwerfällig laufen. Das Forum an sich ist sau schnell. Serverlast des SQL Server liegt im schnitt bei 0,3 und RAM auslastung übersteigt auch nach mehrere Tage nicht die 17 GB.
Bei dem File Server ist es nicht viel anders, RAM auslastung liegt bei 4GB und Serverlast bei 0,40 und das mit 200 - 300 zugriffe die Minute.
Meine Server Daten
File Server
- supermicro x9dr3/f
- Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz (24 Threads)
- 64GM RAM
- 5x SAS HDDs RAID 10
- Server: Nginx 1.12.1 PHP 7.1 mit php-fpm und Redis zum Cachen.
SQL Server
- DELL 610 mit 5 SSD HDDs im RAID10
- Intel(R) Xeon(R) CPU E5620 @ 2.40GHz ( 16 Threads)
- 48 GB RAM
- MariaDB 10.1.14
Beide Server stehen im Rechenzentrum nebeneinander und sind direkt mit einander verbunden.
Code:
>> MySQLTuner 1.7.4 - 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] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 10.1.14-MariaDB-1~trusty
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(5K)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[!!] /var/log/mysql/error.log contains 2 warning(s).
[!!] /var/log/mysql/error.log contains 19 error(s).
[--] 1 start(s) detected in /var/log/mysql/error.log
[--] 1) 2017-08-31 9:40:29 140009851881344 [Note] /usr/sbin/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2017-08-31 9:39:57 140018214525696 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 406M (Tables: 4)
[--] Data in InnoDB tables: 9G (Tables: 249)
[--] Data in MEMORY tables: 40M (Tables: 5)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'sql1@%' hasn't specific host restriction.
[!!] User 'debian-sys-maint@%' 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: 2h 52m 13s (2M q [209.267 qps], 107K conn, TX: 23G, RX: 634M)
[--] Reads / Writes: 80% / 20%
[--] Binary logging is disabled
[--] Physical Memory : 47.2G
[--] Max MySQL memory : 39.5G
[--] Other process memory: 252.5M
[--] Total buffers: 13.6G global + 32.5M per thread (800 max threads)
[--] P_S Max memory usage: 512M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 15.0G (31.74% of installed RAM)
[OK] Maximum possible memory usage: 39.5G (83.75% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (14/2M)
[OK] Highest usage of available connections: 3% (28/800)
[OK] Aborted connections: 0.00% (0/107767)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (14 temp sorts / 241K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 97% (175K on disk / 181K total)
[OK] Thread cache hit rate: 99% (28 created / 107K connections)
[OK] Table cache hit rate: 94% (554 open / 584 opened)
[OK] Open file limit used: 1% (74/5K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 512.9M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.1.14-MariaDB-1~trusty)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.8% (591M used / 3B cache)
[OK] Key buffer size / total MyISAM indexes: 2.9G/188.0M
[OK] Read Key buffer hit rate: 99.9% (1M cached / 1K reads)
[!!] Write Key buffer hit rate: 56.1% (184K cached / 103K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 15
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 10.0G/9.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/10.0G should be equal 25%
[OK] InnoDB buffer pool instances: 10
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95% (285444715 hits/ 285600188 total)
[!!] InnoDB Write Log efficiency: 60.93% (296518 hits/ 486665 total)
[OK] InnoDB log waits: 0.00% (0 waits / 190147 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 95.2% (3M cached / 173K 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/mysql/error.log file
Control error line(s) into /var/log/mysql/error.log file
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Code:
# optimization for mysqld
# ~ key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
# ~ 256M + ( 2M + 2M ) * 500 = 2256M
[mysqld]
max_connections = 800
thread_concurrency = 15
thread_cache_size = 58
key_buffer_size = 3000M # 3500M
read_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 16M
query_cache_size = 0 #1024M
query_cache_limit = 16M
query_cache_type = 0
open_files_limit = 4000
table_definition_cache = 4000
table_open_cache = 4000
max_heap_table_size = 512M
tmp_table_size = 512M
innodb_buffer_pool_size = 10G # 2G
innodb_buffer_pool_instances = 10 # 2
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 1000
innodb_log_file_size = 1G # Neu
innodb_thread_concurrency = 15
innodb_open_files = 8192 # Neu
innodb_file_per_table = 1 # Neu
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log_queries_not_using_indexes = 0
interactive_timeout = 120
wait_timeout = 60
performance_schema = on
skip-name-resolve = 1Hier noch ein kurzer auszug meiner slow querys
Code:
# Time: 170831 12:33:19
# User@Host: sql1[sql1] @ [192.168.10.1]
# Thread_id: 108185 Schema: xenforo QC_hit: No
# Query_time: 8.199122 Lock_time: 0.000033 Rows_sent: 20 Rows_examined: 3210576
# Rows_affected: 0
SET timestamp=1504175599;
SELECT user.*
,
user_profile.*,
user_option.*,
user_privacy.*
FROM xf_user AS user
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = user.user_id)
WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
ORDER BY user.username
LIMIT 20 OFFSET 629740;
# Time: 170831 12:33:22
# User@Host: sql1[sql1] @ [192.168.10.1]
# Thread_id: 108210 Schema: xenforo QC_hit: No
# Query_time: 8.376097 Lock_time: 0.000037 Rows_sent: 20 Rows_examined: 3210656
# Rows_affected: 0
SET timestamp=1504175602;
SELECT user.*
,
user_profile.*,
user_option.*,
user_privacy.*
FROM xf_user AS user
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = user.user_id)
WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
ORDER BY user.username
LIMIT 20 OFFSET 629760;
# Time: 170831 12:36:19
# User@Host: sql1[sql1] @ [192.168.10.1]
# Thread_id: 110424 Schema: xenforo QC_hit: No
# Query_time: 7.241379 Lock_time: 0.000045 Rows_sent: 20 Rows_examined: 3308338
# Rows_affected: 0
SET timestamp=1504175779;
SELECT user.*
,
user_profile.*,
user_option.*,
user_privacy.*
FROM xf_user AS user
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = user.user_id)
WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
ORDER BY user.username
LIMIT 20 OFFSET 654180;
# Time: 170831 12:38:44
# User@Host: sql1[sql1] @ [192.168.10.1]
# Thread_id: 112453 Schema: xenforo QC_hit: No
# Query_time: 7.359957 Lock_time: 0.000034 Rows_sent: 18 Rows_examined: 3454892
# Rows_affected: 0
SET timestamp=1504175924;
SELECT user.*
,
user_profile.*,
user_option.*,
user_privacy.*
FROM xf_user AS user
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = user.user_id)
WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
ORDER BY user.username
LIMIT 20 OFFSET 690820;Nun die frage, habe ich etwas übersehen das änderung so schwerfällig laufen. Das Forum an sich ist sau schnell. Serverlast des SQL Server liegt im schnitt bei 0,3 und RAM auslastung übersteigt auch nach mehrere Tage nicht die 17 GB.
Bei dem File Server ist es nicht viel anders, RAM auslastung liegt bei 4GB und Serverlast bei 0,40 und das mit 200 - 300 zugriffe die Minute.