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.
Wow, thanks for the tip. However, Mine was on line 43, so people, just look for the word INSERT.
Thanks.
Did it as suggested, but it is on line 43 on my file.
But don’t know how much improvement has been made.
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.
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.
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
[...] I can’t take credit for this one but I thoguh I would share it. Richard at Boakes.com pointed out a feature in MySql which can really improve performance o [...]
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]
[...] tracked, here Add post view counts to your WordPress page, here TurboCharging StatTraq, here All of these hacks are quite useful, especially blocking your own IP from being tracked, which allows for [...]
[...] The rest were Stattraq (a wordpress statistics plugin) related. I followed this Turbocharging Stattraq hint which consisted of replacing “INSERT INTO” at line 43 in the /plugins/stattraq.php file with “INSERT DELAYED INTO”. [...]
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!”
Works splendidly! GJ!!
[...] UPDATE: Via Paperback Writer, I also found another nice speedup for StatTraq – that will make your pages load faster for viewers. At boakes.org, he suggests you delay the insertion of the ‘hit’. This is a great tweak. Simply change your stattraq.php plugin file by finding the ‘INSERT INTO’ database SQL and change it to INSERT DELAYED INTO. This tell the script to hit the database and not to wait for confirmation of a successful insert, which is good if you care about the data, but these are hit stats. If a DB problem arose, losing a couple hit ticks would be fine. [...]
[...] Richard at Boakes.com gives an interesting idea that is supposed to speed up your stattraq database manipulation by 90%! [...]