I am a beginner managing a server and I have a problem after upgrading from MySQL 5.7 to MariaDB 10.2. Performance usage is very high: 191%. Can somebody help optimize values in my.cnf for a database server with the following specifications:
> Architecture: x86_64
>
> CPU op-mode(s): 32-bit, 64-bit
>
> Byte Order: Little Endian
>
> CPU(s): 2
>
> On-line CPU(s) list: 0,1
>
> Thread(s) per core: 1
>
> Core(s) per socket: 1
>
> Socket(s): 2
>
> NUMA node(s): 1
>
> Vendor ID: GenuineIntel
>
> CPU family: 6
>
> Model: 79
>
> Model name: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
>
> Stepping: 1
>
> CPU MHz: 2199.998
>
> BogoMIPS: 4399.99
>
> Virtualization: VT-x
>
> Hypervisor vendor: KVM
>
> Virtualization type: full
>
> L1d cache: 32K
>
> L1i cache: 32K
>
> L2 cache: 4096K
>
> L3 cache: 16384K
>
> NUMA node0 CPU(s): 0,1
and MySQLTuner:
> >> 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.21-MariaDB
>
> [OK] Operating on 64-bit architecture
>
> -------- Log file Recommendations ----------------------------------------------
> -------------------- [--] Log file: /var/lib/mysql/mvpiot.novalocal.err(513K)
>
> [OK] Log file /var/lib/mysql/mvpiot.novalocal.err exists
>
> [OK] Log file /var/lib/mysql/mvpiot.novalocal.err is readable.
>
> [OK] Log file /var/lib/mysql/mvpiot.novalocal.err is not empty
>
> [OK] Log file /var/lib/mysql/mvpiot.novalocal.err is smaller than 32
> Mb
>
> [!!] /var/lib/mysql/mvpiot.novalocal.err contains 1146 warning(s).
>
> [!!] /var/lib/mysql/mvpiot.novalocal.err contains 2355 error(s).
>
> [--] 37 start(s) detected in /var/lib/mysql/mvpiot.novalocal.err
>
>
> [--] 1) 2019-01-12 8:25:46 140431271381184 [Note] /usr/sbin/mysqld:
> ready for connections.
>
> [--] 2) 2019-01-12 7:29:03 140574858127552 [Note] /usr/sbin/mysqld:
> ready for connections.
>
> [--] 3) 2019-01-11 18:39:26 140560611846336 [Note] /usr/sbin/mysqld:
> ready for connections. [--] 4) 2019-01-11 17:00:17 140623437183168
> [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2019-01-11
> 16:30:08 140674500737216 [Note] /usr/sbin/mysqld: ready for
> connections. [--] 6) 2019-01-11 14:55:17 140065307875520 [Note]
> /usr/sbin/mysqld: ready for connections. [--] 7) 2019-01-11 13:34:41
> 140588138420416 [Note] /usr/sbin/mysqld: ready for connections. [--]
> 8) 2019-01-11 13:01:12 139932833945792 [Note] /usr/sbin/mysqld: ready
> for connections. [--] 9) 2019-01-11 12:27:23 140694443542720 [Note]
> /usr/sbin/mysqld: ready for connections. [--] 10) 2019-01-11 12:08:55
> 139655472789696 [Note] /usr/sbin/mysqld: ready for connections. [--] 9
> shutdown(s) detected in /var/lib/mysql/mvpiot.novalocal.err [--] 1)
> 2019-01-10 18:03:26 139706578528000 [Note] /usr/sbin/mysqld: Shutdown
> complete [--] 2) 2019-01-10 17:48:26 140143107737344 [Note]
> /usr/sbin/mysqld: Shutdown complete [--] 3) 2019-01-10 17:45:56
> 140094187407104 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4)
> 2019-01-10 17:42:51 139985407059712 [Note] /usr/sbin/mysqld: Shutdown
> complete [--] 5) 2019-01-10 17:26:12 140542010558208 [Note]
> /usr/sbin/mysqld: Shutdown complete [--] 6) 2019-01-10 17:26:09
> 140119564142336 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7)
> 2019-01-10 13:42:59 140154423613184 [Note] /usr/sbin/mysqld: Shutdown
> complete [--] 8) 2019-01-10 9:21:20 140596763801344 [Note]
> /usr/sbin/mysqld: Shutdown complete [--] 9) 2019-01-10 9:21:18
> 139833937012480 [Note] /usr/sbin/mysqld: Shutdown complete
>
>
> -------- Storage Engine Statistics -----------------------------------------------------------------
>
> [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM
> +PERFORMANCE_SCHEMA +SEQUENCE
>
> [--] Data in Aria tables: 192.0K (Tables: 6)
>
> [--] Data in MyISAM tables: 15.5M (Tables: 85)
>
> [--] Data in InnoDB tables: 140.7M (Tables: 87)
>
> [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
>
> [!!] There is no basic password file list!
>
>
> -------- CVE Security Recommendations --------------------------------------------------------------
>
> [--] Skipped due to --cvefile option undefined
>
>
> -------- Performance Metrics -----------------------------------------------------------------------
>
> [--] Up for: 1h 26m 8s (432K q [83.607 qps], 20K conn, TX: 710M, RX:
> 32M)
>
> [--] Reads / Writes: 97% / 3%
>
> [--] Binary logging is disabled
>
> [--] Physical Memory : 3.9G
>
> [--] Max MySQL memory : 2.9G
>
> [--] Other process memory: 0B
>
> [--] Total buffers: 672.0M global + 4.5M per thread (500 max threads)
>
> [--] P_S Max memory usage: 0B
>
> [--] Galera GCache Max memory usage: 0B
>
> [OK] Maximum reached memory usage: 1016.7M (25.74% of installed RAM)
>
> [OK] Maximum possible memory usage: 2.9G (74.41% of installed RAM)
>
> [OK] Overall possible memory usage with other process is compatible
> with memory available
>
> [OK] Slow queries: 0% (0/432K)
>
> [OK] Highest usage of available connections: 15% (76/500)
>
> [OK] Aborted connections: 0.39% (82/20849)
>
> [!!] 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.
>
> [OK] Query cache efficiency: 39.5% (237K cached / 602K selects)
>
> [OK] Query cache prunes per day: 0
>
> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 129 sorts)
>
> [!!] Joins performed without indexes: 23
>
> [OK] Temporary tables created on disk: 21% (296 on disk / 1K total)
>
> [OK] Thread cache hit rate: 99% (76 created / 20K connections)
>
> [OK] Table cache hit rate: 94% (319 open / 339 opened)
>
> [OK] Open file limit used: 1% (188/10K)
>
> [OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
>
>
> -------- Performance schema ------------------------------------------------------------------------
>
> [--] Performance schema is disabled.
>
> [--] Memory used by P_S: 0B
>
> [--] Sys schema is installed.
>
>
> -------- ThreadPool Metrics ------------------------------------------------------------------------
>
> [--] ThreadPool stat is enabled.
>
> [--] Thread Pool Size: 2 thread(s).
>
> [--] Using default value is good enough for your version
> (10.2.21-MariaDB)
>
>
> -------- MyISAM Metrics ----------------------------------------------------------------------------
>
> [!!] Key buffer used: 18.4% (24M used / 134M cache)
>
> [OK] Key buffer size / total MyISAM indexes: 128.0M/9.4M
>
> [OK] Read Key buffer hit rate: 99.4% (748K cached / 4K reads)
>
> [!!] Write Key buffer hit rate: 1.3% (255K cached / 3K writes)
>
>
> -------- InnoDB Metrics ----------------------------------------------------------------------------
>
> [--] InnoDB is enabled.
>
> [--] InnoDB Thread Concurrency: 0
>
> [OK] InnoDB File per table is activated
>
> [!!] InnoDB buffer pool / data size: 128.0M/140.7M
>
> [!!] 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: 100.00% (532580940 hits/ 532588160
> total)
>
> [!!] InnoDB Write Log efficiency: 51.86% (13396 hits/ 25832 total)
>
> [OK] InnoDB log waits: 0.00% (0 waits / 12436 writes)
>
>
> -------- AriaDB Metrics ----------------------------------------------------------------------------
>
> [--] AriaDB is enabled.
>
> [OK] Aria pagecache size / total Aria indexes: 128.0M/96.0K
>
> [!!] Aria pagecache hit rate: 79.6% (1K cached / 302 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/mvpiot.novalocal.err file
>
> Control error line(s) into /var/lib/mysql/mvpiot.novalocal.err file
>
> 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
>
> Performance schema should be activated for better diagnostics
>
> 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)
>
> join_buffer_size (> 1.0M, or always use indexes with JOINs)
>
> performance_schema = ON enable PFS
>
> innodb_buffer_pool_size (>= 140.7M) if possible.
>
> innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
If anyone has an optimized my.cnf, can you give it to me?
Best Answer
How is "performance usage" measured?
The main tunable is
innodb_buffer_pool_size. It should be about 70% of available RAM. The other things in my.cnf should not be changed without first understanding the situation. (The 128MB default is OK until your table get a bunch bigger.)High CPU usage is almost always caused by lack of an optimal
INDEXand/or poor formulation of someSELECT. Find the naughty queries and let's work on them. If you need help finding them, see this on the slowlog.You have a lot of MyISAM tables; consider converting them to InnoDB. Tips here
For NUMA, see my notes here.
I repeat; You can't tune your way out of performance issues.