| Author |
 Topic  |
|
Ocean
Starting Member
5 Posts |
 Posted - 07 February 2008 : 18:56:24
|
Please excuse me if someone has already posted this!
While fiddling with the code and learning about caching on MySQL (should also apply to other DB's), we identified a 4 character change than will half the disk access of your database.
In forums.asp
defDate = DateToStr(dateadd("d",-(nDays),strForumTimeAdjust)),8)
Change to
defDate = Left(DateToStr(dateadd("d",-(nDays),strForumTimeAdjust)),8) & "0000"
All it does it trim the hours/minutes off and default them to midnight.
For each user, it was generating a unique SQL statement which the DB engine cannot cache:
SELECT ___ WHERE T_LAST_POST > '200801021233'
SELECT ___ WHERE T_LAST_POST > '200801021234'
SELECT ___ WHERE T_LAST_POST > '200801021236'
SELECT ___ WHERE T_LAST_POST > '200801021239'
etc...
after the change it becomes
SELECT ___ WHERE T_LAST_POST > '200801020000'
SELECT ___ WHERE T_LAST_POST > '200801020000'
SELECT ___ WHERE T_LAST_POST > '200801020000'
SELECT ___ WHERE T_LAST_POST > '200801020000'
..until a change of day.
It's more than good enough for the query to be accurate to the day, rather than to the minute.
As a result, MySQL caches the keys and the performance increase is very tidy!
Hope this is of some use to somebody and am not duplicating someone else.
Enjoy!
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 February 2008 : 19:26:52
|
| Interesting tip - have you got any stats for your speed increases? Are they noticeable? |
 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 08 February 2008 : 01:57:55
|
however, this will completely change the behaviour of forum.asp so be warned
if your db has problems like this you should either look for a more enterprise level database like MS SQL or change the hardware that your MySQL db is running on, because if not caching a handful of queries makes that much difference then your server is sreiously under powered |
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 08 February 2008 : 04:54:06
|
| Huw, I'm not familiar with this piece of code ; what will change in terms of functionality when applying this change? |
portfolio - linkshrinker - oxle - twitter |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 08 February 2008 : 05:48:07
|
| I was also wondering about the people that visit more than once a day... I just woke up and am eating breakfast now, but if the above changes were in place, and assuming I'm understanding this correctly before I've had my coffee (big assumption), would I not see this as a "new topic" (or at least as having had new posts) again when I come back on my lunch break later today regardless of it having any new posts? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 February 2008 : 06:01:23
|
| Well, the original code was there for a reason and the reason was to have a page that's customized for each user. Of course, the proposed changes do end that, so where this is a viable option or not depends on how you want the forum to behave. I wouldn't want this on a forum of mine. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 08 February 2008 : 06:10:41
|
That date is used to select how many days of topics you want to see on forum.asp.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 08 February 2008 : 07:51:29
|
The problem is, though, if someone has it set to show topics from the last day, there's not going to be much to see at a minute past midnight.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 08 February 2008 : 08:50:58
|
quote:
Originally posted by MarcelG
Huw, I'm not familiar with this piece of code ; what will change in terms of functionality when applying this change?
It will not return the same set of topics to forum.asp
I am not saying it is wrong, just that you should expect it to behave totally differently if you make the change. what you will in effect do is change it so that say for instace if you picked to view topics from 'the last day' what it does now is show you all topics posted in the last 24 hrs, if you make the change it will show you the topics posted since midnight yesterday which is not the same thing. |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 08 February 2008 : 15:48:51
|
Ah, that makes sense. for some odd reason I missed the part about it being in forum.asp... guess that's what I get for posting before the first cup of coffee.  |
|
|
|
Ocean
Starting Member
5 Posts |
Posted - 11 February 2008 : 01:17:20
|
It works for us, and nobody has complained. We churn out around a million page views/month.
Our server runs faster, and our database cache hit ratio has tripled.
If you have a busy forum (>1,000,000 page views/month), and would like more performance for no dollars, be bold and make the change.
If everything is fine, then no change is necessary.
quote:
if your db has problems like this you should either look for a more enterprise level database like MS SQL
This comment intrigues me - HuwR I'd be really keen on seeing some stats in regards to this, for if what you say is true, we'd seriously investigate migration - thanks.
quote:
change the hardware that your MySQL db is running on, because if not caching a handful of queries makes that much difference then your server is sreiously under powered
Our server was starting to chug. A four character change has now avoided thousands of $ change over/upgrades. A key for us is remembering it's only a forum, not bank statements, and the definition of "Last 1 days" (which I don't think anybody even uses) is pretty open to interpretation.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 11 February 2008 : 01:57:25
|
quote:
This comment intrigues me - HuwR I'd be really keen on seeing some stats in regards to this, for if what you say is true, we'd seriously investigate migration - thanks.
there are plenty of comparisons around which show that MSSQL is a more resiliant and powerful database than MySQL.
Like I said, if caching one query has such a profound effect on your databases performance then there is something not quite right about the spec of your database server. |
|
|
|
Ocean
Starting Member
5 Posts |
Posted - 11 February 2008 : 05:13:58
|
Thanks for your comments guys - it's been an educational experience visiting here to share knowledge.
Hope you all have a fantastic day! |
|
|
|
Topic |
|
Topic Locked
