OS : CENTOS 7
Database : mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
PHP : PHP 5.6.29 (cli) (built: Dec 8 2016 08:42:35)
OpenEMR Ver: 4.2.2 (We done additional developments to match with our local Hospital Scenarios)
Maria DB administration | OPenEMr administartion
Mysqld CPU usage on top command shows above 100 or sometimes above 200 % when maximum users are logged in and active on openemr. It ends up with check your mysqld runs error on a daily basis and force to restart Mariadb.
Please find the mysqltuner 1.7 output
MySQLTuner-perl-eb73004]# ./mysqltuner.pl
MySQLTuner 1.7.9 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.52-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[–] Data in MyISAM tables: 3G (Tables: 290)
[–] Data in InnoDB tables: 999M (Tables: 168)
[OK] Total fragmented tables: 0
-------- Performance Metrics -----------------------------------------------------------------------
[–] Up for: 4h 48m 30s (5M q [306.767 qps], 10K conn, TX: 4G, RX: 779M)
[–] Reads / Writes: 99% / 1%
[–] Binary logging is enabled (GTID MODE: OFF)
[–] Physical Memory : 15.4G
[–] Max MySQL memory : 836.0M
[–] Other process memory: 44.2M
[–] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 755.3M (4.78% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (5.29% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (641/5M)
[OK] Highest usage of available connections: 80% (122/151)
[OK] Aborted connections: 0.02% (2/10568)
[!!] 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 / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 4% (16K temp sorts / 391K sorts)
[!!] Joins performed without indexes: 188768
[!!] Temporary tables created on disk: 40% (207K on disk / 512K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 113% (225 open / 199 opened)
[OK] Open file limit used: 19% (195/1K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[OK] Binlog cache memory access: 100.00% (10402 Memory / 10402 Total)
-------- 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: 4 thread(s).
[–] Using default value is good enough for your version (5.5.52-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 23.7% (31M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/290.4M
[OK] Read Key buffer hit rate: 100.0% (125M cached / 6K reads)
[OK] Write Key buffer hit rate: 98.4% (15K cached / 15K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[!!] InnoDB buffer pool / data size: 128.0M/999.7M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[–] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (6266183830 hits/ 6266276248 total)
[!!] InnoDB Write Log efficiency: 227.32% (14630 hits/ 6436 total)
[OK] InnoDB log waits: 0.00% (0 waits / 21066 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[–] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 89.0% (1M cached / 159K reads)
-------- Replication Metrics -----------------------------------------------------------------------
[–] Galera Synchronous replication: NO
[–] This server is acting as master for 1 server(s).
[–] Binlog format: STATEMENT
[–] XA support enabled: ON
[–] Semi synchronous replication Master: Not Activated
[–] Semi synchronous replication Slave: Not Activated
[!!] This replication slave is not running but seems to be configured.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Control error line(s) into /var/log/mariadb/mariadb.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Consider installing Sys schema from GitHub - mysql/mysql-sys: The MySQL sys schema
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 128.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
innodb_file_per_table=ON
innodb_buffer_pool_size (>= 999M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
So please suggest me is that safe to do the recommendations above for the mariadb tweak.
Or else please suggest how can I find or fix what is like to be cause for High CPU usage due to mysqld.