[MediaWiki-l] MySQL frozen after template change

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[MediaWiki-l] MySQL frozen after template change

Eduardo Elias Camponez
Hi,

I have this problem where my Mediawiki gets frozen after I edit a Template that is used in a lot of pages. I understand from here [0] that the problem could be because of lots of inserts made in the jobs table. From `top`, MySQL is used a lot for a while which froze the site during few minutes.

Because of that I started using Redis for Jobqueue in order to avoid that. However the problem is still happening. I'm not sure how to make sure Redis is being used. Although I have made some tests turning redis on and off, I'm not 100% sure is properly working. I'm assuming it is.

Is is possible to know for sure what (if not the inserts) is causing the freeze?

How do I make sure that Redis and no MySQL is being used for the JobQueue.

Thank you,

Eduardo  


[0] https://www.mediawiki.org/wiki/Redis#MediaWiki_&_Wikimedia_use_cases_for_Redis

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: MySQL frozen after template change

Brian Wolff
When you say "frozen", you mean the site takes a long time to load?

I wouldn't necessarily assume that the reason was high insert rate into job
table. That's an issue that Wikimedia experienced, but Wikimedia is very
big and highly optimized, and performance bottlenecks for Wikimedia don't
necessarily correspond to the performance bottlenecks that ordinary
installs might experience.

My gut reaction is that maybe your install is being overloaded not from the
queueing of all the jobs but just from the execution of all of them.
Possibly combined with caches being cleared resulting in lots of activity
next view (If this was the case, top should also show a lot of time spent
in the php process too).

However, its always best to measure performance problems before trying to
optimize the situation. So I guess some questions:
* How is your job queue setup (are you running the CLI runJobs.php script
or just the default. What is $wgJobRunRate? If running from the CLI script
you may want to consider setting --maxjobs and sleeping between invocations
of the runner script, to slow things down).
* Try and figure out why MySQL is being slow. A good place to start here is
enabling the slow query log -
https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html which will tell
you what queries are slow (This is certainly not the be all and end all of
mysql performance, and I am definitely not an expert on optimizing DBs, but
it seems like a decent place to start)
* Try and figure out why MW is slow. Its a little annoying to setup, but if
you can setup profiling ( https://www.mediawiki.org/wiki/Manual:Profiling)
that can tell you what mediawiki is doing, which can confirm if its just
waiting on db, or if its spending its time doing something else.

Hope that helps
--
Brian
On Wed, Mar 6, 2019 at 8:08 PM Eduardo Elias Camponez <
[hidden email]> wrote:

> Hi,
>
> I have this problem where my Mediawiki gets frozen after I edit a Template
> that is used in a lot of pages. I understand from here [0] that the problem
> could be because of lots of inserts made in the jobs table. From `top`,
> MySQL is used a lot for a while which froze the site during few minutes.
>
> Because of that I started using Redis for Jobqueue in order to avoid that.
> However the problem is still happening. I'm not sure how to make sure Redis
> is being used. Although I have made some tests turning redis on and off,
> I'm not 100% sure is properly working. I'm assuming it is.
>
> Is is possible to know for sure what (if not the inserts) is causing the
> freeze?
>
> How do I make sure that Redis and no MySQL is being used for the JobQueue.
>
> Thank you,
>
> Eduardo
>
>
> [0]
> https://www.mediawiki.org/wiki/Redis#MediaWiki_&_Wikimedia_use_cases_for_Redis
>
> _______________________________________________
> MediaWiki-l mailing list
> To unsubscribe, go to:
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>
_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: MySQL frozen after template change

Eduardo Elias Camponez
Thanks for the response.

On 3/6/19 8:33 PM, Brian Wolff wrote:
> When you say "frozen", you mean the site takes a long time to load?

So long that my proxy times out.

>
> I wouldn't necessarily assume that the reason was high insert rate into job
> table. That's an issue that Wikimedia experienced, but Wikimedia is very
> big and highly optimized, and performance bottlenecks for Wikimedia don't
> necessarily correspond to the performance bottlenecks that ordinary
> installs might experience.
>
> My gut reaction is that maybe your install is being overloaded not from the
> queueing of all the jobs but just from the execution of all of them.
> Possibly combined with caches being cleared resulting in lots of activity
> next view (If this was the case, top should also show a lot of time spent
> in the php process too).

Usually mysqld is the on the very top, but there are some (about 5) php there also.  

>
> However, its always best to measure performance problems before trying to
> optimize the situation. So I guess some questions:
> * How is your job queue setup (are you running the CLI runJobs.php script
> or just the default. What is $wgJobRunRate? If running from the CLI script
> you may want to consider setting --maxjobs and sleeping between invocations
> of the runner script, to slow things down).

I don't run the runJobs.php, I let it queued and run as the default.

I had my JobRunRate set to 3 (I'm start to think the problem is here). I will lower it down to see if 1 or even less would help.

> * Try and figure out why MySQL is being slow. A good place to start here is
> enabling the slow query log -
> https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html which will tell
> you what queries are slow (This is certainly not the be all and end all of
> mysql performance, and I am definitely not an expert on optimizing DBs, but
> it seems like a decent place to start)
> * Try and figure out why MW is slow. Its a little annoying to setup, but if
> you can setup profiling ( https://www.mediawiki.org/wiki/Manual:Profiling)
> that can tell you what mediawiki is doing, which can confirm if its just
> waiting on db, or if its spending its time doing something else.
>
> Hope that helps


Thanks for the tips. I will look into that too.


Cheers,

Eduardo

_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: MySQL frozen after template change

Brian Wolff
Try also setting $wgRunJobsAsync = true; at bottom of LocalSettings.php (It
should make jobs run after the request and not during. However it doesn't
work in some installs, so if after enabling it templates don't work at all,
you should remove it).

If you can, setting up php runJobs.php --wait could probably improve
things, as then the jobs will run in the background, and not the same time
as viewing the wiki. See https://www.mediawiki.org/wiki/Manual:RunJobs.php
for details.
--
Brian

On Wed, Mar 6, 2019 at 9:10 PM Eduardo Elias Camponez <
[hidden email]> wrote:

> Thanks for the response.
>
> On 3/6/19 8:33 PM, Brian Wolff wrote:
> > When you say "frozen", you mean the site takes a long time to load?
>
> So long that my proxy times out.
>
> >
> > I wouldn't necessarily assume that the reason was high insert rate into
> job
> > table. That's an issue that Wikimedia experienced, but Wikimedia is very
> > big and highly optimized, and performance bottlenecks for Wikimedia don't
> > necessarily correspond to the performance bottlenecks that ordinary
> > installs might experience.
> >
> > My gut reaction is that maybe your install is being overloaded not from
> the
> > queueing of all the jobs but just from the execution of all of them.
> > Possibly combined with caches being cleared resulting in lots of activity
> > next view (If this was the case, top should also show a lot of time spent
> > in the php process too).
>
> Usually mysqld is the on the very top, but there are some (about 5) php
> there also.
>
> >
> > However, its always best to measure performance problems before trying to
> > optimize the situation. So I guess some questions:
> > * How is your job queue setup (are you running the CLI runJobs.php script
> > or just the default. What is $wgJobRunRate? If running from the CLI
> script
> > you may want to consider setting --maxjobs and sleeping between
> invocations
> > of the runner script, to slow things down).
>
> I don't run the runJobs.php, I let it queued and run as the default.
>
> I had my JobRunRate set to 3 (I'm start to think the problem is here). I
> will lower it down to see if 1 or even less would help.
>
> > * Try and figure out why MySQL is being slow. A good place to start here
> is
> > enabling the slow query log -
> > https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html which will
> tell
> > you what queries are slow (This is certainly not the be all and end all
> of
> > mysql performance, and I am definitely not an expert on optimizing DBs,
> but
> > it seems like a decent place to start)
> > * Try and figure out why MW is slow. Its a little annoying to setup, but
> if
> > you can setup profiling (
> https://www.mediawiki.org/wiki/Manual:Profiling)
> > that can tell you what mediawiki is doing, which can confirm if its just
> > waiting on db, or if its spending its time doing something else.
> >
> > Hope that helps
>
>
> Thanks for the tips. I will look into that too.
>
>
> Cheers,
>
> Eduardo
>
> _______________________________________________
> MediaWiki-l mailing list
> To unsubscribe, go to:
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>
_______________________________________________
MediaWiki-l mailing list
To unsubscribe, go to:
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l