database is overloaded or otherwise not running properly

database is overloaded or otherwise not running properly

by Deepak Kumar -
Number of replies: 11

Hello Everyone

On my moodle site I am getting error :  the database is overloaded . 
Sometime site works when I refresh 2-3 times. I am having huge attempt data for users. is that causing error?

I have checked my my php setting  and in my php.ini file I made the changes
mysql.allow_persistent = Off
   
Can anyone suggest me solution?


Thanks In advance.

Deepak

Attachment error.png
Attachment setting.png
Average of ratings: -
In reply to Deepak Kumar

Re: database is overloaded or otherwise not running properly

by Ken Task -
Picture of Particularly helpful Moodlers

What does this mean ... 'huge attempt data for users'?   Many repeated attempts to login?  From 'valid' IP addresses ... ie, known to be used by users?  And you see that via apache access log?

What does your apache (assuming apache) error logs show/say?

The overloaded error reported could be true as from the php clip shared, shows the DB server on same as the web service (looks like).   That true ... DB server on same box as web service?

How much memory is MySQL able to use?

Command line access via ssh and running top might show memory situation on site.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: database is overloaded or otherwise not running properly

by Deepak Kumar -

Thanks for your reply Ken

I am getting this error in log file

[06-Aug-2018 04:11:32 CST6CDT] Cannot find session record 1ed2e2f37df04ea337825a89faced863 for user 79412, creating new session.
[06-Aug-2018 04:11:41 CST6CDT] Cannot find session record c39a49953707c00f53cddfcd0488e4c7 for user 123071, creating new session.
[06-Aug-2018 04:11:47 CST6CDT] Cannot find session record 40a028e52acd1ce136873828e75bfbd6 for user 97990, creating new session.
[06-Aug-2018 04:13:20 CST6CDT] Cannot find session record bc67287e41d6269840aabc78c44dbd82 for user 117015, creating new session.
[06-Aug-2018 04:14:35 CST6CDT] Cannot find session record 00c47fe08112d04912cb819251b9b0e2 for user 71772, creating new session.
[06-Aug-2018 04:15:03 CST6CDT] Cannot find session record d57d57e3cba6bd2d5effdb6b7973d42e for user 88583, creating new session.
[06-Aug-2018 04:22:50 CST6CDT] Cannot find session record 3ab14c8eaa98b718afddf1214513d075 for user 101763, creating new session.
[06-Aug-2018 04:23:13 CST6CDT] Cannot find session record 827ae0d6903e7a84c0a31c99ed09590f for user 119610, creating new session.
[06-Aug-2018 04:34:32 CST6CDT] Cannot find session record 2e927a07fd7cdfbc046f02a82074d432 for user 92874, creating new session.
[06-Aug-2018 05:02:37 CST6CDT] Cannot find session record 701a9f21c5e60db9164f6645ab051139 for user 71119, creating new session.
[06-Aug-2018 05:38:34 CST6CDT] Cannot find session record a6b7ed23c68491d1d6b8afc29cdb1b9f for user 83472, creating new session.
[06-Aug-2018 05:39:41 CST6CDT] Cannot find session record 534879a791751eef04eba723b8ac0395 for user 99729, creating new session.
[06-Aug-2018 05:45:00 CST6CDT] Cannot find session record a173e34ecab5a8e7b1cc14a5777878a3 for user 111744, creating new session.
[06-Aug-2018 05:51:41 CST6CDT] Cannot find session record 1192916473818869c9895f68364e4da7 for user 99772, creating new session.
[06-Aug-2018 05:56:04 CST6CDT] Cannot find session record b1311a4320f019ccefdc180690fa69ec for user 77260, creating new session.
[06-Aug-2018 06:34:48 CST6CDT] Cannot find session record 809fb80ed2b79907585dc381f2cdecc3 for user 90408, creating new session.
[06-Aug-2018 06:47:15 CST6CDT] Cannot find session record c2820bea83c524114a522d9ffaa8c0ea for user 93322, creating new session.
[06-Aug-2018 07:01:41 CST6CDT] Cannot find session record c6d3e5278ca1879cd530393cb40e9fa8 for user 86000, creating new session.

I have valid over 50k valid users and  when their question attempt data in mdl_question_attempts is 1.1 Gb and in mdl_question_attempt_steps is 914 MB  

I dont want their data to delete

 

Average of ratings: -
In reply to Deepak Kumar

Re: database is overloaded or otherwise not running properly

by Ken Task -
Picture of Particularly helpful Moodlers

First, we are in the developers forum ... discussing something not related to development.   Thread might be moved to hardware and performance or other.  2 cents worth there.

Let's take first things first ... the errors then large tables/desire to reduce tables.

Taking last line in error:

[06-Aug-2018 07:01:41 CST6CDT] Cannot find session record c6d3e5278ca1879cd530393cb40e9fa8 for user 86000, creating new session.
Looks like you are using DB for sessions and that's creating some havoc?

Think some time ago Moodle recommended NOT using DB for sessions.  DB server became overtaxed.

If not using some service like memcached or redis for sessions, in moodledata/sessions/ files would be created for user sessions.

Think the number of users dictates use of a service.

Latest docs:

https://docs.moodle.org/35/en/Session_handling

'spirit of sharing', Ken



Average of ratings: Useful (2)
In reply to Ken Task

Re: database is overloaded or otherwise not running properly

by Ken Task -
Picture of Particularly helpful Moodlers

and a follow up thought ... there are task now that are part of the main cron job.

One of those is cleaning up sessions.

Could 'run now' in Moodle Admin interface or there is a command line option in code/admin/tool/task/cli/

Think I'd do the CLI to see what might be happening there.

And one follow up / related question ... how often are you running the main cron at code/admin/cli/cron.php?

Think the recommendation these days is to have that run (the cron.php) once every minute.

'spirit of sharing', Ken


Average of ratings: -
In reply to Deepak Kumar

Re: database is overloaded or otherwise not running properly

by Ken Task -
Picture of Particularly helpful Moodlers

This part:

I have valid over 50k valid users and  when their question attempt data in mdl_question_attempts is 1.1 Gb and in mdl_question_attempt_steps is 914 MB  

I dont want their data to delete

Quizzes have always been heavy processing.  Yes, during live classes with the number of users you have messing with either of those tables not advised.

I take it you have a dedicated DB server.  Might wanna install MySQLtuner.pl on the dedicated DB server and run it.

https://github.com/major/MySQLTuner-perl

You might find DB server in need of some tuning.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: database is overloaded or otherwise not running properly

by Deepak Kumar -

Hi Ken 

Thank you so much for your support.

I added the  session  handling code in config file , so seesion data will not store in DB. Its working fine now.

Deepak 

Average of ratings: Useful (1)
In reply to Deepak Kumar

Re: database is overloaded or otherwise not running properly

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
If you say that the site works perfectly for a single user but gives this error when many join, you have a performance problem. Check the documentation of the Hardware and performance forum: https://moodle.org/mod/forum/post.php?forum=94.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: database is overloaded or otherwise not running properly

by Deepak Kumar -

Thanks for your reply Visvanath 

When I load to any page on site it showing me this error , when I refresh 2-3 times site works.  

Average of ratings: -
In reply to Deepak Kumar

Re: database is overloaded or otherwise not running properly

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Even when you are the only user?

Is that user the 'admin'? If so, try as a normal user.

Did you read the documentation I have linked above? Then you must have seen:
Include as much background information as possible about your hardware - operating system, web server, database server, PHP scripting framework, etc. If your machine is virtualized, also mention the host hardware, operating system, virtualization technology used and resources assigned to the virtual machine. If you use a hosting service, mention either the hosting provider and the package or resources as advertised.

5 min to read and reply is a bit suspicious.
;-P

Average of ratings: -
In reply to Visvanath Ratnaweera

Re: database is overloaded or otherwise not running properly

by Deepak Kumar -

Yes even when only user and I am logged in with admin

Average of ratings: -