Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Hi,
I use User stats on my website to log the ips where people come from. But the user stats module uses some really slow queries, killing my website. That's why I needed to stop it. An example of a slow query which took 11 seconds is posted below! It is because of the DISTINCT method used in the query. This should and can be done differently.
# Time: 130113 9:19:49
# User@Host: admin_adk[admin_adk] @ localhost []
# Query_time: 11.153548 Lock_time: 0.000182 Rows_sent: 11 Rows_examined: 10731565
SET timestamp=1358065189;
SELECT DISTINCT user_stats_ips.ip_address AS user_stats_ips_ip_address, user_stats_ips.first_seen_timestamp AS user_stats_ips_first_seen_timestamp, users.uid AS uid, (SELECT COUNT(usi.uid)
FROM (SELECT DISTINCT(uid), ip_address FROM user_stats_ips) usi
WHERE usi.ip_address = user_stats_ips.ip_address) AS user_stats_ips_ip_user_count, (SELECT COUNT(usi.ip_address)
FROM (SELECT DISTINCT(uid), ip_address FROM user_stats_ips) usi
WHERE usi.uid = user_stats_ips.uid) AS user_stats_ips_user_ip_count
FROM
users users
LEFT JOIN user_stats_ips user_stats_ips ON users.uid = user_stats_ips.uid
WHERE (( (users.uid = '1' ) ))
ORDER BY user_stats_ips_user_ip_count DESC;
The former query is build on user/1/ip_tracking . All pages showing tables of ip information are very slow:
admin/reports/ip_tracking/by_ip/11.123.12.123
admin/reports/ip_tracking/by_user/someusername
Comments
Comment #0.0
GBurg CreditAttribution: GBurg commentedtypo's
Comment #1
Liam McDermott CreditAttribution: Liam McDermott commentedThanks for the issue report.
That looks like a query built by Views, and while those pages are slow to load, they aren't viewed by many users (just administrators) and don't need to be viewed very often. Therefore I'm thinking that the claim that User Stats is 'killing' your site is hyperbole at this point. :)
Maybe this is just a bad example? Are you having trouble with the other queries User Stats is running on page load?
Comment #2
GBurg CreditAttribution: GBurg commentedwell, I have 4 moderators, who can use the ip stats quite alot to find multis for my game. If they do so (in the 15 minutes that they are busy with it) they do kill my website :) The website has 60k visitors a day and many registered visitors (half of them are registered vistors) so there is alot of logging going on...
Also look at the query time: 11.15 seconds (for 1 query!)
Rows_examined: 10.731.565
On those numbers, a Distinct is given unnneccessary server load. There must be a better way to handle those queries :)
EDIT: Only the queries with Distinct show up. I can tell, because they are logged in my slow query log... It has nothing to do with other stuff on the page.
Comment #3
Liam McDermott CreditAttribution: Liam McDermott commentedThat's fair enough, especially considering it's bringing the site to a crawl while the queries are running, good point. It's good to hear that the normal
user_stats_get_stats()
queries aren't appearing in the slow query log, thanks for clarifying.I don't doubt there's a better way, either. The Views integration was never done very well, and it's on my radar to completely re-do it.
Comment #3.0
Liam McDermott CreditAttribution: Liam McDermott commentedAdded info where this query is used