Print Page - MySQL tuning

Title: MySQL tuning
Post by: badmonkey on May 16, 2016, 11:45:48 am

Hi ladies and gents! Hope everyone is well. At least for a Monday. :D

The question about MySQL tuning centers around the Stats page. On a moderately busy forum (~15-20k page views per day, ~200 posts per day) everything is fast except the Stats page. It may take 20 to 30 secs to load. My suspicions are tuning is the/an issue. What do you guys think? Is that on the right track, or do you think there is some other issue? What are the more critical points in regards to producing stats (table joins, etc)?

Thanks for your thoughts! 8)

Title: Re: MySQL tuning
Post by: emanuele on May 16, 2016, 12:25:53 pm

Hi!

hmm... the stats page is a kind of query-monster with lots of table scan, table sort, all all kind of Bad Thing^TM.
How many messages in total does the forum have? And how many members?

If you can replicate the database on your localhost, it could be useful to add

Code: [Select]

$db_show_debug = true;

to the Settings.php and check if there is any query that takes more than average.

Title: Re: MySQL tuning
Post by: radu81 on May 16, 2016, 04:14:31 pm

Quote from: badmonkey – May 16, 2016, 11:45:48 amOn a moderately busy forum (~15-20k page views per day, ~200 posts per day) everything is fast except the Stats page.

Those stats are similar to mine, about 100-150 posts/day and 15-20.000 pageviews/day from ElkArte stats. Analytics stats are much lower since in ElkArte are also counted the bots, but I did not noticed any slowdown, my stats page loads in less than 1 second
http://www.skodaclub.it/index.php?action=stats

Title: Re: MySQL tuning
Post by: Spuds on May 16, 2016, 05:32:45 pm

That seems like excessive time for sure ... do what eman said and see if there is a query that is driving that behavior. Maybe a missing index or key or an addon or ..... but if the rest of the forum is quick then I don't think it would be tuning as much as it is a problem with a query.

Title: Re: MySQL tuning
Post by: Flavio93Zena on May 16, 2016, 05:41:48 pm

http://www.exporigs.com/index.php?action=stats
Page created in 0.021 seconds with 21 queries.

Note: you are still running 1.0.5, you are 2 stable versions back (stable non-dev version is 1.0.7, otherwise 1.1 but it's being developed).

Title: Re: MySQL tuning
Post by: emanuele on May 16, 2016, 06:00:27 pm

Considering he wrote 200 posts per day and the site you linked is ~10 a month, I think it's not the one badmonkey is talking about. ;)

Title: Re: MySQL tuning
Post by: Flavio93Zena on May 17, 2016, 07:45:24 am

Good point O:-)

Title: Re: MySQL tuning
Post by: badmonkey on May 17, 2016, 02:15:34 pm

Quote from: emanuele – May 16, 2016, 12:25:53 pmHi!

hmm... the stats page is a kind of query-monster with lots of table scan, table sort, all all kind of Bad Thing^TM.
How many messages in total does the forum have? And how many members?

If you can replicate the database on your localhost, it could be useful to add
Code: [Select]
$db_show_debug = true;
to the Settings.php and check if there is any query that takes more than average.

I get his behavior on a couple of forums, actually. One has 112,820 Posts in 1,866 Topics by 447 Members, the other 1,101,896 Posts in 83,568 Topics by 19,282 Members.

Quote from: Spuds – May 16, 2016, 05:32:45 pmThat seems like excessive time for sure ... do what eman said and see if there is a query that is driving that behavior. Maybe a missing index or key or an addon or ..... but if the rest of the forum is quick then I don't think it would be tuning as much as it is a problem with a query.

That makes sense.

Quote from: emanuele – May 16, 2016, 06:00:27 pmConsidering he wrote 200 posts per day and the site you linked is ~10 a month, I think it's not the one badmonkey is talking about. ;)

Correct.

Thanks guys, that's given a direction to pursue. Stay tuned for breaking news. Or more questions! ;D

ElkArte Community

General => Chit Chat => Topic started by: badmonkey on May 16, 2016, 11:45:48 am