Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Pranata Ari -
Number of replies: 8

Hello, my name is Pranata, a student who is currently testing performance of a cluster designed for hosting e-learning website (intranet). I use 1 server as load balancer, 3 webservers (where I put Moodle on) & 1 MySQL server. All 3 Moodle servers mount the same data directory using NFS & connect to 1 MySQL database server. I build the cluster based on Linux Virtual Server (LVS) & deploy an LVS-NAT topology with round robin scheduling algorithm.

Load balancer : CentOS Intel Xeon Quad Core 2.66 GHz, 2GB RAM, 40GB

Web Server : Intel Core2 Duo 2.8GHz, 1GB RAM, 20GB, CentOS, httpd web server, Moodle 1.9+

MySQL Server : Intel Core2 Duo 2.93 GHz, 2GB RAM, 40GB, CentOS, MySQL 5+

I have tested the time response performance by opening many pages simultaneously (in 1 web browser) & measured how long it took to load all pages. Everything seems right until 300 pages time response test. Exceeding 300 pages, the cluster begins to down (with some pages failed to load). In 500 pages time response test, server failed to load within 100 seconds.

What I wonder is, in case of testing a single server without clustering (database & webserver in 1 CPU), server successfully processed 500 pages without failure although it took very long time (much longer than in cluster) to finish loading all 500 pages.

Single Server : Intel Core2 Duo 2.8 GHz, CentOS, httpd web server, MySQL 5+, Moodle

I conclude the problem is caused by MySQL bottleneck. I've tried to test it again & again using different my.cnf configurations, one of the latest conf I made is listed below & it still failed to load 500 pages simultaneously.

[root@datacenter ~]# nano /etc/my.cnf

[mysqld]
back_log = 75
skip-innodb
max_connections = 100000
key_buffer = 128M
myisam_sort_buffer_size = 16M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 4M
table_cache = 8192
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 16M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
skip-name-resolve
thread_concurrency = 4

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M

I don't believe that Moodle has a limitation in how many pages can be loaded simultaneously. Based on anyone's experiences, I would like to know what to change in that configuration so that more than 500 Moodle pages can be loaded simultaneously. Any recommendations will be appreciated. Thank you very much. I'm sorry for my bad English too.

 

Best regards,

 

Pranata

Average of ratings: -
In reply to Pranata Ari

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Taylor Judd -

There are many good threads and articles here on database performance and moodle.

http://docs.moodle.org/en/Performance#Database_performance

http://docs.moodle.org/en/Performance_FAQ

But since your testing my first suggestion is try switching your default engine from myisam to INNODB.  The default engine of myisam often has issues with table locking wheras innodb uses row based locking. You could be seeing table contention and this is your current bottle next.

Also depending on the size of your install you may have to tune the varriabl for either myisam or innodb a good amount with only 2 GB dedicated to the database.

Average of ratings: -
In reply to Taylor Judd

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Pranata Ari -

Thanks for your reply sir. Yes I'm using MyISAM as default storage engine in my MySQL server. I'm a bit afraid of changing MyISAM to InnoDB because I don't know whether changing storage engine can cause serious problem in my Moodle installation & its contents (or not? ). Changing to InnoDB will be taken (final option maybe) if tuning my.cnf config can't result anything better.

Average of ratings: -
In reply to Pranata Ari

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
You might also want to give it a try with Postgres rather than MySQL.

If you believe the hype, it's locking algorithms are better than MySQL.
Average of ratings: -
In reply to Howard Miller

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Pranata Ari -

In my installation, all 3 Moodle servers aren't only sharing the same Moodledata folder (with NFS), they're also sharing the same database (e.g. clustermoodle), so clustermoodle is used by 3 Moodle servers. If I separate their database (each Moodle has its own database although within the same database server/CPU), can this option solve my MySQL bottleneck problem?

Thanks before

Average of ratings: -
In reply to Pranata Ari

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
....but then they wouldn't be a cluster, surely?
Average of ratings: -
In reply to Howard Miller

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Pranata Ari -
Sorry for my late reply sir. Why separating those databases wouldn't be called as clustering? The database server would be the same, databases would be created in just one machine (1 DB Server). Upon the DB server, I then create one database for a Moodle server (web server), so there would be 3 databases for 3 Moodle servers. I have made some modifications in my.cnf configuration file & it still got me in trouble when I tested more than 300 pages to load simultaneously.
Average of ratings: -
In reply to Pranata Ari

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

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

I can't give a straight answer to your question. There are couple of things which need further clarification.

> I use 1 server as load balancer, 3 webservers (where I put Moodle on) & 1 MySQL server. All 3 Moodle servers mount the same data directory using NFS & connect to 1 MySQL database server. I build the cluster based on Linux Virtual Server (LVS) & deploy an LVS-NAT topology with round robin scheduling algorithm.

As explained in http://www.linuxvirtualserver.org/whatis.html ?

How does your setup compare to the ones mentioned in this thread http://moodle.org/mod/forum/discuss.php?d=57202 ?

> Load balancer : CentOS Intel Xeon Quad Core 2.66 GHz, 2GB RAM, 40GB
>
>Web Server : Intel Core2 Duo 2.8GHz, 1GB RAM, 20GB, CentOS, httpd web server, Moodle 1.9+
>
> MySQL Server : Intel Core2 Duo 2.93 GHz, 2GB RAM, 40GB, CentOS, MySQL 5+

Generally web-servers tend to be heavy RAM users where as db-servers need lot of CPU and a good disk subsystem. Your web-servers definitely need more RAM. Even more RAM won't harm the db-server. You need to benchmark it for CPU and disk.

> I have tested the time response performance by opening many pages simultaneously (in 1 web browser) & measured how long it took to load all pages.

How exactly do you run the test, using a tool like JMeter?

> Everything seems right until 300 pages time response test. Exceeding 300 pages, the cluster begins to down (with some pages failed to load). In 500 pages time response test, server failed to load within 100 seconds.

It is important to know which resource ran out at 300 pages. You need to monitor them.

> What I wonder is, in case of testing a single server without clustering (database & webserver in 1 CPU), server successfully processed 500 pages without failure although it took very long time (much longer than in cluster) to finish loading all 500 pages.
>
> Single Server : Intel Core2 Duo 2.8 GHz, CentOS, httpd web server, MySQL 5+, Moodle

That is bad news.

> I conclude the problem is caused by MySQL bottleneck.

On what evidence?
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Need recommendations for my.cnf configuration to support >=500 simultaneous Moodle accesses

by Pranata Ari -
Sorry for my late reply sir:

// As explained in http://www.linuxvirtualserver.org/whatis.html ?
Actually mine like here: http://www.linuxvirtualserver.org/VS-NAT.html

// How does your setup compare to the ones mentioned in this thread http://moodle.org/mod/forum/discuss.php?d=57202 ?
A little different from those diagrams, I don't provide a database server backup. So I only have 1 database server here.

// Generally web-servers tend to be heavy RAM users where as db-servers need lot of CPU and a good disk subsystem. Your web-servers 
// definitely need more RAM. Even more RAM won't harm the db-server
// How exactly do you run the test, using a tool like JMeter?
With those specifications, my web servers used to be strong enough to handle more than 300 pages to load simultaneously with MySQL server configuration (my.cnf file) as written above until I tested sending 500 pages & experienced failure. I run page-loading (time response) test using a web browser (Firefox or Chrome) & also need some addons such as Reload All Tabs & Tabs Counter. How did I run the test? I opened tabs as many as I wanted (e.g. 300, that's what Tab Counter for) to address of my load balancer (single IP address accessible from external network) then I clicked Reload All Tabs in  browser. For a test using more than 1 web server, every tabs should show up pages from different web servers.

// It is important to know which resource ran out at 300 pages. You need to monitor them.
I don't really understand what you mean resource ran out but based on my previous test, it's a kind of overload in the database server (don't know which one, a result from lack of memory maybe?).

// That is bad news.
Oh please ...?

// On what evidence?
Don't know exactly what goes wrong, web server crash or database server bottleneck. I have said that I've ever run a test on a single server (web server + DB server in a CPU with specs above)  to inspect how it handles 500 simultaneous user access. In my Moodle cluster test, a DB server handles 3 web (Moodle) servers. Then I also found out that a database server can support simultaneous user access on Moodle until 50 x its RAM (GB).
Average of ratings: -