Skip to main content ↑ ↓
MySQL tuning Started by badmonkey · · Read 7038 times 0 Members and 1 Guest are viewing this topic. previous topic - next topic

MySQL tuning

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)
 

Re: MySQL tuning

Reply #1 –

Hi!

hmm... the stats page is a kind of query-monster with lots of table scan, table sort, all all kind of Bad ThingTM.
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.
Bugs creator.
Features destroyer.
Template killer.

Re: MySQL tuning

Reply #2 –

Quote from: badmonkey – On 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
sorry for my bad english

Re: MySQL tuning

Reply #3 –

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.

Re: MySQL tuning

Reply #4 –

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).
~ SimplePortal Support Team ~

Re: MySQL tuning

Reply #5 –

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. ;)
Bugs creator.
Features destroyer.
Template killer.

Re: MySQL tuning

Reply #6 –

Good point O:-)
~ SimplePortal Support Team ~

Re: MySQL tuning

Reply #7 –

Quote from: emanuele – Hi!

hmm... the stats page is a kind of query-monster with lots of table scan, table sort, all all kind of Bad ThingTM.
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 – 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.

That makes sense.


Quote from: emanuele – 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. ;)


Correct.

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