Moodle REALLY slow with 20 concurrent users

Moodle REALLY slow with 20 concurrent users

by Oliver Boyers -
Number of replies: 12

Hi all, 


I have a system up running moodle 3.5.2. I have 2 servers Both on LAMP stacks. One for web which hosts the moodle code and moodledata. The other just for the database.

Web Server - 12CPUs, 32GB RAM, 2TB disk, using PHP-FPM, MPM. CentOS 7, PHP 7.2, MariaDB

Database server - 6CPU, 12GB RAM, 1TB disks, MariaDB

The site runs really slowly when we have just 25+ using the site concurrently (As per google analytics stats)

Our online users section normally show about 40-50 users online when things really slow down. 


My my.cnf file is as follows: 

[mysqld]

bind-address = 0.0.0.0

log-error=/var/lib/mysql/learnsysdb.dedicated.co.za.err

innodb_file_per_table=1

default-storage-engine=MyISAM

performance-schema=1

max_allowed_packet=512M

open_files_limit=10000

max_connections = 300


sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


# innodb_file_format = Barracuda

innodb_buffer_pool_size = 8G ### How much innodb data to store in memory. Higher = faster performance

innodb_buffer_pool_instances = 9

innodb_file_per_table = 1 ### Each innodb table is its own file on disk.

innodb_flush_method = O_DIRECT ## Innodb flushes to table files directly

innodb_log_file_size = 512M ##

innodb_log_files_in_group = 3 ##

innodb_read_io_threads = 8 ## innodb background threads for reads. Helps with reads

join_buffer_size = 2M ## Helps when a join doesn’t have an index or an index wont be more helpful than a full table scan

max_heap_table_size = 128M ## Max size a tmp table can be in memory


query_cache_limit = 16M ##

query_cache_min_res_unit = 2048 ##

query_cache_size = 512M ##

query_cache_type = 1 ## Caches complete query results. Data gets invalidated when underlying tables are changed.


table_open_cache_instances = 8 ## Divides the table cache to reduce contention

table-definition-cache = 4096 ## Stores table definitions - .frm files

key_buffer_size = 16M # As there are no MyISAM tables, this variable helps with MyISAM temp tables.

back_log = 1500 # When MySQL gets a lot of connections in a short time, it will keep them in a back log

innodb_purge_threads = 2 # May reduce locks for when updates/deletes are run often (like sessions table)

innodb_sort_buffer_size = 2M # Helps with index changes and create table statements

#innodb_page_cleaners = 4 # Will help with start up and shutdown in 5.7


[mysqld]

skip-networking=0

skip-bind-address


When I look at the htop of either server i see no strain on neither memory nor CPUs (Screenshots attached of both, when the server is overloading)

I spoke to my hosting provider and the said:

"Memory demand on the server was 32GB, your server only has 12GB, this would mean the server
was running extremely slow, due to thrashing, that it appeared as though the server was down."

They won't help me any further though. 

Im really struggling to understand where the bottle neck is. 


If its memory, then why am I not seeing 100% memory usage using htop? 

If its CPU why do I never go over 20% CPU usage. 


I'm a bit lost. Any help would be greatly appreciated. 

Attachment Screenshot 2019-08-07 at 16.05.08.png
Attachment Screenshot 2019-08-07 at 16.05.16.png
Average of ratings: -
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

The htop outputs are snapshots of a fast changing picture, therefore not too informative. One thing is clear, they are not overbooked (Load average). Your servers must be able to support a much bigger user base.

It is not clear to me, if the database server is separate why the web server runs MySQL? There is one mysqld process consuming 17.5% memory! The database server on the other hand is more stable, still the root mysqld with 23.9% CPU and 55.1% memory, rather heavy. You need to do a proper monitoring there. Have you consulted https://docs.moodle.org/35/en/Performance ?

You are not the super user 'root' of the servers? It will be tedious if you are not.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle REALLY slow with 20 concurrent users

by Oliver Boyers -

I was running the database on the webserver previously but moved it to its own server. I have subsequently deleted the old database there since this last reading. 

Which machine is likely to consume more memory? The DB server or the web server? 

I am the superuser so I can do whatever I need to. I have been over that performance doc. Im using opcache. 

Average of ratings: -
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
What are these machines? If they're VMs, are they reasonably good ones? Some have desperately slow file access.

Easiest way to check is to run the cache test for files (Site administration > Plugins > Cache). Numbers should be very low.

I haven't really looked closely at your DB configuration but I hope you're using something like 'mysqltuner' to tweak these.
Average of ratings: Useful (2)
In reply to Howard Miller

Re: Moodle REALLY slow with 20 concurrent users

by Oliver Boyers -
Hi Howard,

Thanks for your time. I think that they are pretty decent VMs. Their disk usage is dynamically allocated but i am told by my hosting company that we are using SSDs at the moment.

Here are the times from cache performance testing in plugins -> Caching -> Test performance. (Screenshots attached)

I am using MySQL tuner to tune the database ill post my results from a fresh scan shortly.
Attachment Screenshot 2019-08-08 at 07.53.47.png
Attachment Screenshot 2019-08-08 at 07.53.56.png
Average of ratings: -
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
They're not the best figures (I get about 10x faster for a 'set' on bare metal with ssd drives) but I doubt that's your problem.

Worth eliminating.
Average of ratings: -
In reply to Howard Miller

Re: Moodle REALLY slow with 20 concurrent users

by Oliver Boyers -
Here is the mysql tuner results:

>> MySQLTuner 1.7.15 - Major Hayden >> 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.3.17-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/learnsysdb.dedicated.co.za.err(477K)
[OK] Log file /var/lib/mysql/learnsysdb.dedicated.co.za.err exists
[OK] Log file /var/lib/mysql/learnsysdb.dedicated.co.za.err is readable.
[OK] Log file /var/lib/mysql/learnsysdb.dedicated.co.za.err is not empty
[OK] Log file /var/lib/mysql/learnsysdb.dedicated.co.za.err is smaller than 32 Mb
[!!] /var/lib/mysql/learnsysdb.dedicated.co.za.err contains 1313 warning(s).
[!!] /var/lib/mysql/learnsysdb.dedicated.co.za.err contains 396 error(s).
[--] 76 start(s) detected in /var/lib/mysql/learnsysdb.dedicated.co.za.err
[--] 1) 2019-08-08 4:34:52 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-08-07 22:26:03 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-08-07 22:25:08 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-08-07 22:19:34 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-08-07 20:32:01 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-08-07 20:19:35 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-08-07 20:08:49 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-08-07 20:04:43 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-08-07 18:02:46 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-08-07 16:45:05 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 71 shutdown(s) detected in /var/lib/mysql/learnsysdb.dedicated.co.za.err
[--] 1) 2019-08-08 4:34:49 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-08-07 22:26:00 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-08-07 22:25:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-08-07 22:17:29 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-08-07 20:31:58 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-08-07 20:19:33 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-08-07 20:08:46 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2019-08-07 20:04:39 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2019-08-07 18:00:05 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2019-08-07 16:45:02 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 8.4G (Tables: 764)
[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: 3h 23m 55s (772K q [63.165 qps], 539 conn, TX: 4G, RX: 102M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 11.9G
[--] Max MySQL memory : 15.1G
[--] Other process memory: 0B
[--] Total buffers: 9.1G global + 20.2M per thread (300 max threads)
[--] P_S Max memory usage: 129M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.5G (79.70% of installed RAM)
[!!] Maximum possible memory usage: 15.1G (127.49% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (45/772K)
[OK] Highest usage of available connections: 4% (13/300)
[!!] Aborted connections: 6.31% (34/539)
[!!] 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 / 730K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
[!!] Joins performed without indexes: 19943
[OK] Temporary tables created on disk: 7% (1K on disk / 22K total)
[OK] Thread cache hit rate: 95% (25 created / 539 connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 0% (55/63K)
[OK] Table locks acquired immediately: 100% (73 immediate / 73 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 129.9M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (10.3.17-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/135.0K
[!!] Read Key buffer hit rate: 59.2% (196 cached / 80 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.8G/8.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (17.1428571428571 %): 512.0M * 3/8.8G should be equal to 25%
[!!] InnoDB buffer pool instances: 7
[--] Number of InnoDB Buffer Pool Chunk : 70 for 7 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.98% (1673459983 hits/ 1673725194 total)
[!!] InnoDB Write Log efficiency: 7.4% (1307 hits/ 17660 total)
[OK] InnoDB log waits: 0.00% (0 waits / 16353 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 96.2% (45K cached / 1K 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: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/learnsysdb.dedicated.co.za.err file
Control error line(s) into /var/lib/mysql/learnsysdb.dedicated.co.za.err file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate unclosed connections and network issues
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
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 2.0M, or always use indexes with JOINs)
innodb_log_file_size should be (=746M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=8)
Average of ratings: -
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
At the risk of stating the obvious, you've tuned it to use more memory than you actually have. Which isn't ideal.

Are you monitoring your servers? Are you running something like munin? What happens when the performance is degraded? Is load high? Are you going into swap?
Average of ratings: Useful (2)
In reply to Howard Miller

Re: Moodle REALLY slow with 20 concurrent users

by Oliver Boyers -
Hi Howard,

Thanks for the info.

I have reconfigured my my.cnf file to utilise the correct number of memory now. (I upgraded my database to 32GB of RAM now)

Here are my new my.cnf config details:
[mysqld]
bind-address = 0.0.0.0
log-error=/var/lib/mysql/learnsysdb.dedicated.co.za.err
performance-schema=1
max_allowed_packet=268435456
open_files_limit=63000
max_connections = 120

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# innodb_file_format = Barracuda
innodb_buffer_pool_size = 20G ### How much innodb data to store in memory. Higher = faster performance
innodb_buffer_pool_instances = 20
innodb_file_per_table = 1 ### Each innodb table is its own file on disk.
innodb_flush_log_at_trx_commit = 2 ### Flushes to disk in batches instead of per change

read_buffer_size = 2M ### Helps with reading temp tables faster, bulk inserts and nested queries
read_rnd_buffer_size = 8M ### Helps with multi range queries and ORDER Bys.
sort_buffer_size = 4M ### Helps with ORDER BY queries, which we have a lot of.
table_open_cache = 4096 ### Cache data about open tables. Saves reopening tables.
thread_cache_size = 128 ### How many threads to cache. Can help reduce thread count.
thread_stack = 256k ### How much cache each thread can have. Higher = more complicated results are cached
tmp_table_size = 64M ### Max size a tmp table can be.
innodb_flush_method = O_DIRECT ## Innodb flushes to table files directly
innodb_log_file_size = 512M ##
innodb_log_files_in_group = 3 ##
innodb_read_io_threads = 8 ## innodb background threads for reads. Helps with reads
join_buffer_size = 2M ## Helps when a join doesn’t have an index or an index wont be more helpful than a full table scan

max_heap_table_size = 2048M ## Max size a tmp table can be in memory

table_open_cache_instances = 8 ## Divides the table cache to reduce contention
table-definition-cache = 4096 ## Stores table definitions - .frm files
back_log = 1500 # When MySQL gets a lot of connections in a short time, it will keep them in a back log
innodb_purge_threads = 2 # May reduce locks for when updates/deletes are run often (like sessions table)
innodb_sort_buffer_size = 2M # Helps with index changes and create table statements
#innodb_page_cleaners = 4 # Will help with start up and shutdown in 5.7

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 300M

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

[mysqld]
skip-networking=0
skip-bind-address


I performed a memory test to ensure that this is within my memory constraints using some scripting. Here are my memory stats now:

+------------------------------------------+--------------------+
| key_buffer_size | 128.000 MB |
| query_cache_size | 300.000 MB |
| innodb_buffer_pool_size | 20480.000 MB |
| innodb_additional_mem_pool_size | 0.000 MB |
| innodb_log_buffer_size | 16.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 20924.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 4.000 MB |
| read_buffer_size | 2.000 MB |
| read_rnd_buffer_size | 8.000 MB |
| join_buffer_size | 2.000 MB |
| thread_stack | 0.250 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 64.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 80.281 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 12 |
| max_connections | 120 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 21887.375 MB |
| TOTAL (MAX) | 30557.750 MB |
+------------------------------------------+--------------------+

I do have munin installed and the only thing im abit suspicious of is my disk latency times per device (Screenshot attached)

Does this indicate bad disk wait times? 

Attachment Screenshot 2019-08-08 at 14.21.05.png
Average of ratings: -
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
If you want to do *something* to see if it makes any difference...

1. Switch to database locking (setting is in config-dist.php)

2. Build a Redis server and point your MUC/Cache at that (use a prefix!)

3. Point your sessions at the Redis server (setting is in config-dist.php and use a *different* prefix)

This gets all the performance critical stuff off disk. If it doesn't make any difference it's not that... but you've still done a good thing.

Also, check your Task logs (Site administration > Server > Schedule task logs) make sure that's all running properly. Especially all cleanup processes.
Average of ratings: Useful (1)
In reply to Howard Miller

Re: Moodle REALLY slow with 20 concurrent users

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi Howard and all

18 CPUs, virtual or hardware, can not server 20 concurrent users? There is something very wrong.

About the poor file I/O performance of virtual machines: True, there is an added abstraction layer on top the file system. But I notice a huge improvement over the years. Both software wise and hardware, SSD, etc. Right now I am struggling with an opposite case. A state-of-art root server having disk I/O inferior to quality VMs. I am exploring the RAID, which is unfortunately a software RAID.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle REALLY slow with 20 concurrent users

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
"18 CPUs, virtual or hardware, can not server 20 concurrent users? There is something very wrong."
+1
I suggest installing a clean fresh moodle in a separate URL/floder with no plugins and confirm that shows the same performance issues.
Average of ratings: Useful (1)
In reply to Oliver Boyers

Re: Moodle REALLY slow with 20 concurrent users

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

You wrote:
> I was running the database on the webserver previously but moved it to its own server. I have subsequently deleted the old database there since this last reading.

What is the purpose of those outdated htop screens then?

> Which machine is likely to consume more memory? The DB server or the web server?

You can not treat a specific (running) server stochastically!

> I have been over that performance doc. Im using opcache.

What are your findings, other than that you use Opcache? Did you see this simple https://moodle.org/plugins/report_benchmark ?
Average of ratings: -