Tags: WordPress
Turbocharging StatTraq
April 22nd, 2005, by Rich.
If you use StatTraq and WordPress, or if you use any kind of open-source statistics package on your website, you may benefit from this. Whilst reading around the intricacies of MySQL yesterday I discovered something so blindingly obvious that I think there must be a conspiracy to not talk about it.
To my surprise it appears that by adding a solitary word to my StatTraq installation I have managed to remove ninety-something percent of the time it was adding to my page views.
Previously, every time somebody looked at a page on my site the server would write an entry to the database recording their request, it wrote one when you requested this page; it also has to query a different table in the database to get the page content that you’re reading now.
The discovery I stumbled upon, the source of the massive speedup, is that I’ve added the keyword “DELAYED” to the statement that inserts the log entry into the statistics table. This instructs the database that the stats program does not care about waiting to know that the log was definitely, entirely, and completely written. Instead the Database immediately returns a happy message so the web server can get on with generating and sending the page.
So, if you’re using StatTraq and wish to hack it yourself, open up /plugins/stattraq.php and edit line 52 so that where it reads “INSERT INTO” the edited version reads “INSERT DELAYED INTO”.
One word, 90%+ speed increase. If only every thing could be so positive we’d have a cure for the common cold by bedtime.
April 22nd, 2005 at 11:50 am
Wow, thanks for the tip. However, Mine was on line 43, so people, just look for the word INSERT. :)
May 9th, 2005 at 10:34 am
Thanks.
Did it as suggested, but it is on line 43 on my file.
But don’t know how much improvement has been made.
May 31st, 2005 at 12:38 pm
What’s a real shame is there is no equivalent UPDATE DELAYED command for MySQL, though there is a feature request for it listed as MySQL bug 849.
May 31st, 2005 at 1:16 pm
Absolutely. That woudl make sweeps for spam a whole lot more convenient. The alternative would be any kind of threading for PHP so heavy work could at least be offloaded to a background task.
June 24th, 2005 at 2:14 am
Thanks for the tip! I have just updated to the 1.0b version of StatTraq and see that this was not implemented in the new version.
I also added some new Visitor Counter case statements so that reports are not just for the physical hour, day, and month but for the last 60 minutes, 24 hours, and 30/31 days.
my StatTraq Notes
July 5th, 2005 at 7:12 am
July 13th, 2005 at 5:41 am
hehe ,i am using the last version of stattraq,but i have found that the “counter number” in the sidebar is different from the “user hit” in the /wp-srattraq/index.php,so i want the sidebar shows the “user hit” in the sidebar! what can i do for it?
so sorry that my spelling english is not so good!i come from chinese.
[this message has been moved to the mostwanted page, here]
September 11th, 2005 at 12:49 pm
February 13th, 2006 at 2:36 am
June 1st, 2006 at 3:00 pm
I tried this method out however the increase I received was negligable at best. After some research I found this (on the mysql manual) -
“Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!”