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ı:
[root@srv1 mysqltuner]# perl mysqltuner.pl >> MySQLTuner 1.7.14 - 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 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: http://bit.ly/1mi7c4C Read this before increasing for MariaDB https://mariadb.com/kb/en/library/op...le_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 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 https://github.com/mysql/mysql-sys for MySQL Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 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
[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