database connection is failed or database is not running properly

database connection is failed or database is not running properly

by Abu Ali -
Number of replies: 5

Hi dear Moodle experts;

We are using the services of Bluehost web hosting. I have purchased a dedicated server with the following spec.

1.      4 cores @ 3.3 GHz

2.      1TB (Mirrored) Storage

3.      16 GB RAM

4.      15 TB Bandwidth

5.      Linux OS

Fully managed by the Bluehost company.

I had installed Moodle 3.7 full package downloaded from moodle.org. On the main root, I have installed moodle for undergraduate students and I have created another folder in main and I have installed another moodle 3.7 for master students. It means we are running two Moodles. For undergraduate 3000 students and masters are around 400 students. The same way I have created different databased for undergraduate and masters.

One day I conduct the workshop of my staff on Moodle to work on a realtime quiz. We have a staff of 80 members. Just only 50 staff login and click on the realtime quiz, my moodle gave me an error.

aa


 Then I opened default my.cnf file and I found the following settings

default-storage-engine=MyISAM

interactive_timeout=300

key_cache_block_size=4096

max_heap_table_size=32M

max_join_size=1000000000

max_allowed_packet=33554432

open_files_limit=10000

query_cache_size=32M

thread_cache_size=1000

tmp_table_size=32M

wait_timeout=7800

max_connections=300

performance-schema=0

Then I installed the mysqltuner to tune the mysql and it suggests my some changes and when I did the changes it breaks up the server and then I contact with the Bluehost team. I told them this is the recommendation from mysql tuner

Reccomendationè 17102019

Variables to adjust:

    join_buffer_size (> 256.0K, or always use indexes with JOINs)

    performance_schema = ON enable PFS

    innodb_buffer_pool_size (>= 1.1G) if possible.

    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

 

Then they did the changes in the my.cnf as following

[mysqld]

performance-schema=0

 

performance-schema=0

interactive_timeout=250

key_cache_block_size=4096

max_heap_table_size=32M

max_join_size=1000000000

max_allowed_packet=268435456

open_files_limit=10000

query_cache_size=0M

query_cache_limit =2M

thread_cache_size=100

tmp_table_size=32M

wait_timeout=6800

max_user_connections=400

myisam_recover_options=FORCE

innodb_file_per_table=1

innodb_flush_log_at_trx_commit=0

innodb_purge_threads=1

innodb_support_xa=0

innodb_thread_concurrency=8

innodb_log_file_size=256M

innodb_buffer_pool_size=1G

innodb_buffer_pool_instances=1

 

Now I have installed 3.9 moodle on the main root and now I am planning to conduct the quiz of 100 students.

 

My question is that whether the above setting is correct  or I should do something more it can handle atleast 100 students at a time for a quiz.

Please look the above settings if I need to be delete some lines or add more lines then help me. Right now the report from mysql tuner is following

 

Variables to adjust:

    join_buffer_size (> 256.0K, or always use indexes with JOINs)

    table_open_cache (> 2000)

    performance_schema = ON enable PFS

    innodb_buffer_pool_size (>= 1.8G) if possible.

    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

 

Thans a lot


Average of ratings: -
In reply to Abu Ali

Re: database connection is failed or database is not running properly

by James Steerpike -

You may be fully managed by Bluehost but I doubt if you have anyone knowledge  in Moodle on your team.  Your decision to run two different Moodles for undergraduate  and masters rather than two different  courses is not something I would expect someone familiar  with Moodle to recommend.

Your setup couldn't cope with 50 quiz users at once,  not an exceptional  load, yet you need to handle nearly 4000 users.

I think at that level of size you need expert help. I would suggest  a Moodle partner. The only alternative is to start small and build your expertise.

Average of ratings: -
In reply to Abu Ali

Re: database connection is failed or database is not running properly

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Confirming what James wrote. The fact is, your Moodle (which instance? undergrad? Masters? an added complexitiy) can not handle an 80 user realtime quiz. The first step is a check against Performance recommendations, which you must have done already.

You can't go deeper by looking at those tons of configurations parameters. The method is always the same: You need to monitor the server closely when it gets overloaded, identify the component which hits its limits - CPU, RAM, disk I/O, network. Then treat it in a targeted way. It could mean a configuration change, could also mean adding resources. You can see many examples in the numerous discussions in this forum.

Who will be doing that - somebody from your team (you!), a technician from Bluehost or a third party - that is a different question.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: database connection is failed or database is not running properly

by Abu Ali -
Thanks Mr. Visvanath;
I have closely observed that My.Cnf has some problems. Here too much parameters has mentioned as i have shown you in the above. My concern is that which are the recommended parameters that must be updated in the cnf file. i will be very thankful to you and others if you will share me your cnf because i want to know that which parameters i should be changed or which one is not changed?
i will be appreciated if you will tell me any tool to monitor the activities during the server loaded time that will more help me to figure out the problem.

thanks
Average of ratings: -
In reply to Abu Ali

Re: database connection is failed or database is not running properly

by James Steerpike -
I would expect a Moodle site to have peaks where at least 10% of the users are active. About 400 in your case. We don't know how many users can sit a quiz at once but you report less than 50.
That suggests you need to increase power or effiency by a factor of 10 and I don't think these configuration changes can do that.
You have a relatively expensive server but the performance is far below what I would expect. Perhaps telling us  your installation method and how you set up two instances of Moodle may help.
Average of ratings: -