Posted by mrfelton on June 7, 2009 at 1:21pm
| Project: | Statistics Advanced Settings |
| Version: | 6.x-1.5 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
Looking into my MySQL slow query log, I see numerous entries by statistics_advanced_settings, like these:
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1685' OR uid > 0) AND sid = 's3s6hvh2vriegq5ndjn1g6e4h7' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:04
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134675
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/3671' OR uid > 0) AND sid = 'v6ck4gu98dv5v8uqcqdlisarq0' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:07
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 134675
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1597' OR uid > 0) AND sid = 'm9j5gigep7li4v2tnsskgdqt15' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:14:42
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134694
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/1817' OR uid > 0) AND sid = 'unkjgt8v74ncgkn1ju9m9ad8m1' ORDER BY timestamp DESC LIMIT 0, 1;
# Time: 090607 14:15:04
# User@Host: concern[concern] @ [192.168.1.90]
# Query_time: 1 Lock_time: 0 Rows_sent: 0 Rows_examined: 134706
SELECT uid, timestamp FROM accesslog WHERE (path = 'node/3740' OR uid > 0) AND sid = 'u7t4t3dm190jpmkdp221j7lia6' ORDER BY timestamp DESC LIMIT 0, 1;It seems that the statistics module only indexes accesslog_timestamp, uid and aid columns of the accesslog table. Is there any way your module could add an extra indexes to that (probably not such a good idea to mess with a core module like that) or, provide your own database table for faster lookups? Alternatively, is there a setting that I can tweak within statistics_advanced_settings that would reduce the number of these unindexed queries, or make the queries more efficient?
Comments
#1
Recently I had begun to notice that while my site remained quick and speedy for logged in users, anon users were getting on average 4-5 second delays in page generation. After all day troubleshooting it's become very clear to me that this module was the culprit. One by one I disabled my 3rd party modules to find the source, only after disabling this one was it clear. I'm not an expert, so I can't give reasoning, only hazard a guess that it has something to do with SQL queries.
#2
I tried to add indexes on existing tables to avoid any potential filesorts, so I'm open to patches on how to improve it further.
#3
We are experiencing the same exact problem (as Dave pointed out) with VERY VERY crappy performance for anonymous users(non-login users, aka site visitors). For logged in users, it runs okay.
Dave, did you find any solutions to speed it up? Or has anyone else have any suggestions besides the ones Dave suggested?
Tom, what's the status on this bug? Without solving it, this module is practically unusable! I will look into it if no one has already done so but I certainly don't want to duplicate the effort if the solution had already been found.
#4
It appears that we have two ways to work around the slow running query (still need to fully understand what those queries were doing though).
One is to turn off the repeat page view check, so that those queries won't execute. It's a just one line of simple change to turn off the default value. See patch file. The drawback is that you might have duplicated counts in the stats. In our case, it doesn't really matter much, but YMMV.
The other is to the add an index on the {accesslog} table for sid column, which is the column to store session ids. You will need to write an update hook to alter the table to do that. This works, but we are not sure if this is the best course of action as session ids are temporary data (hashed string), index on that column would slow query inserts.
Lastly, I think the query should really be re-examined and figure out if there is anything we can make it better.
#5
It turned out there is admin setting hook that this module added to Access Log setting page. It's called "Only allow unique content views to increment a content's view counter", unchecked that, it will bypas the slow performed query. No patch needed.
#6
Can we revive this idea?
I realize "only allow unique" can turned off, but the reason for that option is to get significantly more accurate read counts. I'd like to not have this query slowing things down but I don't want to display inflated read counts in order to do it.
If anyone knows how to manually add an index to the database, I wouldn't be averse to doing that as a workaround. It's just not something I've got any experience in doing.
#7
Agreed. This module was exactly what i was hoping for to limit the view access counter visibility to certain content types.
This happened to me on a site that lives on a VPS with 3GB RAM and 16 CPU's which has never before had performance issues.
I installed this on a site with about 2250 nodes on it and it nearly immediately brought the server to a halt; checking with SSH my available RAM when down to 0MB. Perhaps this is something people should at least be informed of when they're readinhg the modules overview page.
for now i'll just use CSS overrides.