Moodle in English: Moodle MySQL overload error

Moodle MySQL overload error

by Alejandro Calderón - Tuesday, April 28, 2015, 4:01 AM

Hi everyone:

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!

Re: Moodle MySQL overload error

by Visvanath Ratnaweera - Tuesday, April 28, 2015, 10:59 PM

An on-line exam (using quizzes) for 400 participants is not a simple thing. Read for example: "quiz on moodle 2.4 slow" https://moodle.org/mod/forum/discuss.php?d=311814.

Read the initial pointers in this forum (Hardware and performance, which become visible only when you write a new post and then vanish).

Average of ratings: -

Re: Moodle MySQL overload error

by Alejandro Calderón - Wednesday, April 29, 2015, 12:42 AM

Thank you Mr. Visvanath I believe that my server have a good requeriments but I believe that my MySQL have a bad configuration, if you know an way to do better, I'll be really gratefull.

Regards!

Average of ratings: -

Re: Moodle MySQL overload error

by Howard Miller - Wednesday, April 29, 2015, 4:45 AM

Explain why you think your server is specified properly? You haven't told us anything about it!

I do hope it's not Windows

When I do very large quizzes (and I have a very big load-balanced system) we are very cautious. We try to split the quiz into much smaller groups and stagger the start of each by 15 minutes. Also be very sure that you are not overloading some part of your network (probably not the Moodle server itself).

Average of ratings: -

Re: Moodle MySQL overload error

by Ken Task - Wednesday, April 29, 2015, 4:48 AM

Quiz is heavy usage of DB in constructing the questions during actual testing session. One way to reduce the load is to limit the number of questions presented to the student - say 5 per page?

And do you really have 400 users hit the quiz/test at the same time?

The only way I know to really see usage is to have ssh into server and use the watch command that splits output of things like free while view realtime apache logs for each hit.

Also see by the output of tuner that there are several db's ... am guessing ... two WordPress sites? What else? Are they also being accessed at the same time?

'spirit of sharing', Ken

Average of ratings: -

Re: Moodle MySQL overload error

by Visvanath Ratnaweera - Wednesday, April 29, 2015, 5:29 AM

Alejandro,

you are welcome! I do agree that your server has high requirements. As I said, 400 people starting an on-line examination at the same time is not a simple thing. Looking at your Internet connectivity, "10 Mbps of download and 181 kbps(!) of upload", I doubt whether you have chosen a state-of-the-art server.

As others have pointed out, there is nobody here who can convert your beliefs into facts. The kind of facts which are needed show up only when you write a new post (and then vanish).

Average of ratings: -

Re: Moodle MySQL overload error

by Alejandro Calderón - Wednesday, April 29, 2015, 8:00 AM

Thanks to everyone for your responses.

At first time, I'm so gratefull with your help, really is so important to me, in second fact, I have a server to use with 3.200 child of poor communities, to strengthen their educational habilities, the estimated traffic is like 400 in the same moment, I'm trying to make the quizzes in groups of child like 50 in one hour.

Howard, I'm using, Linux and I don't have load-balanced system really is possible that my network is overloaded but when I close the platform for the WAN users my Moodle cannot do a faster response in the LAN.

Ken, I have two Wordpress sites, but the traffic is a really bit, sorry for this exit, I wanna delete this content of my thread but I don't know how can I do this. ¿can you help me to use the watch command or tell me wath is the apache log important content?.

Visvanat, I agree with you, I'm trying to find help with some contacts with this because is a really complex situation, I hope to find a solution and share with you, and I believe do active part of this community because I'm a Moddle admin from 6 years ago.

Thanks a lot.

Average of ratings: -

Re: Moodle MySQL overload error

by Usman Asar - Friday, May 1, 2015, 2:37 AM

Alejandro,

I dont think your server has good specifications for 400 users taking quiz, your server is based on single CPU on 4 physical and logical cores, and is 6 years old, besides your total memory is close to 20G, where Moodle's recommendation is of using 1G for 20 concurrent users, making it 20G just for your users and then memory taken by PHP, O/S, Web Server and your database server is a separate thing.

I'll tell you straight away, your CPU is primary source of lags caused as it must be eaten up just by your database server, and I am not even sure about the disc architecture of your server, is it based on 1 hard drive or multiple in RAID ?

Average of ratings: -

Re: Moodle MySQL overload error

by Alejandro Calderón - Wednesday, May 6, 2015, 1:13 AM

Hi Everyone.

I think I've solved the problem, I tried using memcache and this is a really good tool to improve the performance of Moodle, I configured the memcached watching some webpages, between they are the followings:

https://docs.moodle.org/28/en/Caching#Memcached

http://midact.com/content/moodle-how-enable-memcached

This is my better solution, thanks for your help.

Regars!

Average of ratings:Useful (1)