I'm Using Moodle 28 with MySQL 5.5 but when my 400 students try to do a questionary the server is really slow.
We have an internet connection with 10 Mbps of download and 181kbps of upload.
When I do mysqltunner I get the next message.
MYSQLTUNNER RESULTS
>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 11M (Tables: 237)
[--] Data in InnoDB tables: 140M (Tables: 920)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 921
-------- Security Recommendations -------------------------------------------
[!!] User 'M3dFl150lU53r@%' has no password set.
[!!] User 'ccdepazuser@%' has no password set.
[!!] User 'formsorthusr@%' has no password set.
[!!] User 'glpuser@%' has no password set.
[!!] User 'moouser@%' has no password set.
[!!] User 'phplistuser@%' has no password set.
[!!] User 'pruebamoodleuser@%' has no password set.
[!!] User 'roboticuser@%' has no password set.
[!!] User 'sugarcrmuser@%' has no password set.
[!!] User 'wordespuser@%' has no password set.
[!!] User 'wordpuser@%' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 41s (2K q [5.073 qps], 150 conn, TX: 5M, RX: 438K)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 3.4G global + 17.0M per thread (500 max threads)
[OK] Maximum possible memory usage: 11.7G (58% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 1% (9/500)
[OK] Key buffer size / total MyISAM indexes: 220.0M/5.6M
[OK] Key buffer hit rate: 98.0% (1K cached / 21 reads)
[OK] Query cache efficiency: 59.1% (1K cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 230 sorts)
[!!] Joins performed without indexes: 5
[!!] Temporary tables created on disk: 45% (161 on disk / 353 total)
[OK] Thread cache hit rate: 94% (9 created / 150 connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 1% (522/29K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 284.0M/140.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with joins)
This is my my.cnf file.
MY.CNF FILE
[mysqld]
max_connections = 500
query_cache_size = 2636M
query_cache_min_res_unit = 2K.
table_cache = 512 #(table_open_cache in MySQL > 5.1.2)
thread_cache_size = 4
query_cache_limit= 10000M
query_cache_type=1
thread_cache_size=8
table_open_cache=4096
table_cache = 14400
innodb_buffer_pool_size= 284M
key_buffer_size= 220M
read_buffer_size= 8M
read_rnd_buffer_size= 8M
sort_buffer_size= 8M
join_buffer_size= 512K
tmp_table_size= 600M
max_heap_table_size= 300M
long_query_time=5
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql
# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so
# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1
# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
And this is my hardware features.
RAM MEMORY
MemTotal: 20999956 kB
MemFree: 18403840 kB
Buffers: 375684 kB
Cached: 1028700 kB
SwapCached: 0 kB
Active: 1539464 kB
Inactive: 567648 kB
Active(anon): 805276 kB
Inactive(anon): 28852 kB
Active(file): 734188 kB
Inactive(file): 538796 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 4095996 kB
SwapFree: 4095996 kB
Dirty: 96 kB
Writeback: 0 kB
AnonPages: 702720 kB
Mapped: 123936 kB
Shmem: 131408 kB
Slab: 304532 kB
SReclaimable: 228376 kB
SUnreclaim: 76156 kB
KernelStack: 2008 kB
PageTables: 25044 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 14595972 kB
Committed_AS: 4374992 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 64628 kB
VmallocChunk: 34359659640 kB
HardwareCorrupted: 0 kB
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 21594112 kB
DirectMap2M: 0 kB
CPU
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
stepping : 5
microcode : 17
cpu MHz : 2128.054
cache size : 4096 KB
physical id : 20
siblings : 1
core id : 0
cpu cores : 1
apicid : 20
initial apicid : 20
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good unfair_spinlock pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm dts
bogomips : 4256.10
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
stepping : 5
microcode : 17
cpu MHz : 2128.054
cache size : 4096 KB
physical id : 20
siblings : 1
core id : 0
cpu cores : 1
apicid : 20
initial apicid : 20
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good unfair_spinlock pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm dts
bogomips : 4256.10
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
stepping : 5
microcode : 17
cpu MHz : 2128.054
cache size : 4096 KB
physical id : 20
siblings : 1
core id : 0
cpu cores : 1
apicid : 20
initial apicid : 20
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good unfair_spinlock pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm dts
bogomips : 4256.10
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 26
model name : Intel(R) Xeon(R) CPU E5506 @ 2.13GHz
stepping : 5
microcode : 17
cpu MHz : 2128.054
cache size : 4096 KB
physical id : 20
siblings : 1
core id : 0
cpu cores : 1
apicid : 20
initial apicid : 20
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu de tsc msr pae cx8 sep cmov pat clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc rep_good unfair_spinlock pni ssse3 cx16 sse4_1 sse4_2 popcnt hypervisor lahf_lm dts
bogomips : 4256.10
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
Sorry for my bad english and thanks for your help.
Regards!