Duyuruyu Kapat
Facebook Gözat
Twitter Gözat

mysqltunner ve mariadb performans sonucu?

Konu, 'Linux' kısmında Lnxtr tarafından paylaşıldı.

  1. Lnxtr

    Lnxtr Daimi Üye

    Kayıt:
    11 Mayıs 2006
    Mesajlar:
    1,346
    Beğenilen Mesajlar:
    2
    Meslek:
    İşsiz
    Şehir:
    Denizli
    e5-2630 v2 2 tane fiziksel işlemcili sunucum var.
    Ram 32 Gb (Yükseltebilirim)
    cpanel kullanıyorum.
    MariaDB 10.2
    PHP 7.1
    Suncumdaki bir sistemde bazı zamanlar mysql yoran sql sorguları yapıyorum. Bazen bunun sayısı aynı anda 250-300 olabiliyor.
    mysqltunner bilgisi aşağıdaki gibi.
    mysqltunner çıktısı:
    Kod:
    [root@srv1 mysqltuner]# perl mysqltuner.pl
     >>  MySQLTuner 1.7.14 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.2.18-MariaDB
    [OK] Operating on 64-bit architecture
    -------- Log file Recommendations ------------------------------------------------------------------
    [--] Log file: /var/lib/mysql/srv1.siteadi.com.err(26M)
    [OK] Log file /var/lib/mysql/srv1.siteadi.com.err exists
    [OK] Log file /var/lib/mysql/srv1.siteadi.com.err is readable.
    [OK] Log file /var/lib/mysql/srv1.siteadi.com.err is not empty
    [OK] Log file /var/lib/mysql/srv1.siteadi.com.err is smaller than 32 Mb
    [!!] /var/lib/mysql/srv1.siteadi.com.err contains 165478 warning(s).
    [!!] /var/lib/mysql/srv1.siteadi.com.err contains 2677 error(s).
    [--] 48 start(s) detected in /var/lib/mysql/srv1.siteadi.com.err
    [--] 1) 2019-01-10 15:34:36 139914536888448 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 2) 2019-01-10 14:59:07 140071557740672 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 3) 2019-01-09 17:25:42 140692099618944 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 4) 2019-01-09 16:18:36 140241532819584 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 5) 2019-01-09 16:17:09 139899493329024 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 6) 2019-01-09 16:14:21 140497883424896 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 7) 2019-01-09 15:08:55 140320588765312 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 8) 2018-12-16 13:13:20 140406863403136 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 9) 2018-11-21 15:39:38 140237842012288 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 10) 2018-11-21 15:39:34 140043909224576 [Note] /usr/sbin/mysqld: ready for connections.
    [--] 34 shutdown(s) detected in /var/lib/mysql/srv1.siteadi.com.err
    [--] 1) 2019-01-10 15:33:59 140070629263104 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 2) 2019-01-10 14:58:26 140683581748992 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 3) 2019-01-09 17:25:40 140240195606272 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 4) 2019-01-09 16:18:10 139898699659008 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 5) 2019-01-09 16:16:28 140496976119552 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 6) 2019-01-09 16:14:17 140319538673408 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 7) 2019-01-09 15:08:53 140405487560448 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 8) 2018-12-16 13:11:33 140229597255424 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 9) 2018-11-21 15:39:35 140043786004224 [Note] /usr/sbin/mysqld: Shutdown complete
    [--] 10) 2018-11-17 10:26:48 140208456750848 [Note] /usr/sbin/mysqld: Shutdown complete
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in MyISAM tables: 3.5G (Tables: 3574)
    [--] Data in InnoDB tables: 9.7G (Tables: 504)
    [--] Data in MEMORY tables: 0B (Tables: 8)
    [OK] Total fragmented tables: 0
    -------- Analysis Performance Metrics --------------------------------------------------------------
    [--] innodb_stats_on_metadata: OFF
    [OK] No stat updates during querying INFORMATION_SCHEMA.
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] User 'cedrapor@%' does not specify hostname restrictions.
    [!!] User 'pamukkal@%' does not specify hostname restrictions.
    [!!] User 'pamukkal_pamukka@%' does not specify hostname restrictions.
    [!!] User 'pamukkal_usr@%' does not specify hostname restrictions.
    [--] There are 618 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 31m 58s (1M q [560.093 qps], 13K conn, TX: 2G, RX: 480M)
    [--] Reads / Writes: 98% / 2%
    [--] Binary logging is disabled
    [--] Physical Memory     : 11.6G
    [--] Max MySQL memory    : 856.4M
    [--] Other process memory: 0B
    [--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 617.8M (5.21% of installed RAM)
    [OK] Maximum possible memory usage: 856.4M (7.23% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 45% (69/151)
    [OK] Aborted connections: 0.01%  (1/13656)
    [!!] 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: 0.0% (0 cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (126 temp sorts / 114K sorts)
    [!!] Joins performed without indexes: 447
    [OK] Temporary tables created on disk: 8% (3K on disk / 40K total)
    [OK] Thread cache hit rate: 99% (69 created / 13K connections)
    [!!] Table cache hit rate: 4% (2K open / 41K opened)
    [OK] Open file limit used: 4% (2K/50K)
    [OK] Table locks acquired immediately: 99% (78K immediate / 78K 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: 32 thread(s).
    [--] Using default value is good enough for your version (10.2.18-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 69.3% (93M used / 134M cache)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/593.1M
    [OK] Read Key buffer hit rate: 99.1% (26M cached / 228K reads)
    [!!] Write Key buffer hit rate: 93.3% (1K cached / 1K writes)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [!!] InnoDB buffer pool / data size: 128.0M/9.7G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
    [OK] InnoDB buffer pool instances: 1
    [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
    [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
    [OK] InnoDB Read buffer efficiency: 99.95% (496202283 hits/ 496471141 total)
    [!!] InnoDB Write Log efficiency: 55.85% (17445 hits/ 31238 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 13793 writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 99.7% (1M cached / 3K reads)
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    -------- XtraDB Metrics ----------------------------------------------------------------------------
    [--] XtraDB is disabled.
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] Binlog format: MIXED
    [--] XA support enabled: ON
    [--] Semi synchronous replication Master: Not Activated
    [--] Semi synchronous replication Slave: Not Activated
    [--] This is a standalone server
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Control warning line(s) into /var/lib/mysql/srv1.siteadi.com.err file
        Control error line(s) into /var/lib/mysql/srv1.siteadi.com.err file
        Restrict Host for user@% to user@SpecificDNSorIp
        MySQL was started within the last 24 hours - recommendations may be inaccurate
        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
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: [url]http://bit.ly/1mi7c4C[/url]
        Read this before increasing for MariaDB [url]https://mariadb.com/kb/en/library/optimizing-table_open_cache/[/url]
        This is MyISAM only table_cache scalability problem, InnoDB not affected.
        See more details here: [url]https://bugs.mysql.com/bug.php?id=49177[/url]
        This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
        Beware that open_files_limit (50000) variable
        should be greater than table_open_cache (2000)
        Performance schema should be activated for better diagnostics
        Consider installing Sys schema from [url]https://github.com/mysql/mysql-sys[/url] for MySQL
        Consider installing Sys schema from [url]https://github.com/good-dba/mariadb-sys[/url] for MariaDB
        Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: [url]https://bit.ly/2TcGgtU[/url]
    Variables to adjust:
        query_cache_size (=0)
        query_cache_type (=0)
        query_cache_limit (> 1M, or use smaller result sets)
        join_buffer_size (> 256.0K, or always use indexes with JOINs)
        table_open_cache (> 2000)
        performance_schema = ON enable PFS
        innodb_buffer_pool_size (>= 9.7G) if possible.
        innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    [root@srv1 mysqltuner]#
    
    etc/my.conf
    Kod:
    [mysqld]
    log-error=/var/lib/mysql/srv1.siteiadi.com.err
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    performance-schema=0
    max_allowed_packet=268435456
    open_files_limit=50000
    local-infile=0