/etc/apache2/mpm-prefork.conf
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxRequestWorkers: maximum number of server processes allowed to start
# MaxConnectionsPerChild: maximum number of requests a server process serves
# changed MaxRequestWorkers from 150 to 120 for 16GB RAM with 4GB allocated for MYSQL
# basically (16000 - 4000)MB/100MB(required, max per user in Moodle) = 12,000/100 = 120
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxRequestWorkers 120
MaxConnectionsPerChild 0
Here are the settings from /etc/apache2/apache.conf
Timeout 300
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 5
Running MySQL tuner
perl mysqltuner.pl
>> 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
Currently running supported MySQL version 5.7.32-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture
——– Log file Recommendations ——————————————————————
[–] Log file: /var/lib/mysql/xxxx.in.err(0B)
[!!] Log file /var/lib/mysql/xxxx.in.err doesn’t exist
[!!] Log file /var/lib/mysql/xxxx.in.err isn’t readable.
——– Storage Engine Statistics —————————————————————–
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[–] Data in InnoDB tables: 8G (Tables: 503)
[!!] Total fragmented tables: 1
——– 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: 10h 15m 42s (19M q [535.187 qps], 32K conn, TX: 7G, RX: 3G)
[–] Reads / Writes: 75% / 25%
[–] Binary logging is disabled
[–] Physical Memory : 15.7G
[–] Max MySQL memory : 4.6G
[–] Other process memory: 1.6G
[–] Total buffers: 4.1G global + 1.1M per thread (500 max threads)
[–] P_S Max memory usage: 72B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.1G (26.21% of installed RAM)
[OK] Maximum possible memory usage: 4.6G (29.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/19M)
[OK] Highest usage of available connections: 5% (26/500)
[OK] Aborted connections: 0.00% (1/32736)
[!!] 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 / 14M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 597K sorts)
[!!] Joins performed without indexes: 5504
[OK] Temporary tables created on disk: 4% (25K on disk / 515K total)
[OK] Thread cache hit rate: 97% (853 created / 32K connections)
[!!] Table cache hit rate: 5% (2K open / 38K opened)
[OK] Open file limit used: 0% (0/5K)
[OK] Table locks acquired immediately: 100% (9K immediate / 9K locks)
——– Performance schema ————————————————————————
[–] Memory used by P_S: 72B
[–] Sys schema is installed.
——– ThreadPool Metrics ————————————————————————
[–] ThreadPool stat is disabled.
——– MyISAM Metrics —————————————————————————-
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
——– InnoDB Metrics —————————————————————————-
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G/8.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (2.34375 %): 48.0M * 2/4.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[–] Number of InnoDB Buffer Pool Chunk : 32 for 8 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.97% (763634018 hits/ 763829334 total)
[!!] InnoDB Write Log efficiency: 32.26% (300858 hits/ 932478 total)
[OK] InnoDB log waits: 0.00% (0 waits / 631620 writes)
——– AriaDB Metrics —————————————————————————-
[–] AriaDB is disabled.
——– 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:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `moodledb`.`mdl_grade_grades_history`; — can free 1632 MB
Total freed space after theses OPTIMIZE TABLE : 1632 Mb
MySQL started within 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
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (2245)
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: 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 (> 256.0K, or always use indexes with joins)
table_open_cache (> 2245)
innodb_buffer_pool_size (>= 8G) if possible.
innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)