1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Um Zugriff auf die Ressourcen sowie auf Teile des Forums zu erhalten, müsst ihr euren XenForo Validation Token an dieser Stelle hinterlegen. Probleme in diesem Umfeld bitte im Forum melden. Danke!
    Information ausblenden

SQL optimierung

Dieses Thema im Forum "Technik und Co." wurde erstellt von michael24179, 31. Aug. 2017.

  1. michael24179

    michael24179 Mitglied Lizenzinhaber

    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

    • 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 = 1
    Hier 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.
     
    Lemminator und otto gefällt das.