I have read Dries Buytaert post http://buytaert.net/playing-with-new-relic-on-acquia-hosting about most time consuming operations on mysql.
I have analyzed my mysql.bin log after 2 weeks load on web-site and get the following:
- CAPTCHA - 36% of queries
Count: 696.77k (12.12%)
Query: UPDATE captcha_sessions SET token='S' WHERE csid=N
Count: 696.11k (12.11%)
Query: UPDATE captcha_sessions SET timestamp=N, solution='S' WHERE csid=N
Count: 695.75k (12.10%)
Query: INSERT INTO captcha_sessions (uid, sid, ip_address, timestamp, form_id, solution, STATUS, attempts) VALUES (N, 'S', 'S', N, 'S', 'S', N, N)
- Statistics - count node visits - 28% of queries
Count: 1.60M (27.84%)
Query: UPDATE node_counter SET daycount = daycount + 1, totalcount = totalcount + 1, timestamp = N WHERE nid = N
- Sessions - 18% of queries
Count: 835.58k (14.53%)
Query: UPDATE sessions SET uid = N, cache = N, hostname = 'S', session = 'S', timestamp = N WHERE sid = 'S'
Count: 213.20k (3.71%)
Query: INSERT INTO sessions (sid, uid, cache, hostname, session, timestamp) VALUES ('S', N, N, 'S', 'S', N)
- Database logging - 10% of queries
Count: 597.70k (10.39%)
Query: INSERT INTO watchdog (uid, type, message, VARIABLES, severity, link, location, referer, hostname, timestamp) VALUES (N, 'S', 'S', 'S', N, 'S', 'S', 'S', 'S', N)
After that I was wondered that 4 modules uses 36% + 28% + 18% + 10% = 92% of UPDATE,INSERT,DELETE operations! This is 10! times less writes the rest of my 50+ modules.
Accesslog I have turned off (using awstats), all cache was in Cacherouter (using memcached), so if Dries tried to benchmark config with cache in memcached, accesslog off - from all of the queries it remains just sessions + url_alias. But he did not install CAPTCHA module!
-
I'm using image CAPTCHA for anonymous visitors (99%) and comment form shown below page. When i knew it cost so much to deliver captcha to every user, i decided to display comments on separate page, because i can't find module that have ajax load of comment form. I tried Dialog API, but it crashes IE6 browser, and not always work - may be someone could suggest another module to use?
-
Statistics - there are 3 ways - Google Analytics API, to load page views from google on cron (but it hearts on many hits of pages), AWStats or similar module, that parses web-server logs and module for drupal to get this info to node_counter table, and the last choice to store someway node_counter in memcache (other caching module) and flush this data to mysql by cron. The last one is what i am looking for - is it possible? I want this module to display popular content on site.
-
Sessions - this was simple, i changed cacherouter to memcache.
-
Database logging - turned off this module and enabled syslog.
The most consuming module remains CAPTCHA, I even tried to install AJAX module, but there are patches only for CAPTCHA-6.x-1.0 version (2 years ago).
Also, when form loaded on demand by AJAX, it costs nothing to all other visitors, that do not want comment (90-99% of all), and remove any problems when caching pages with CAPTCHA, I wonder if this module do not exist. Please help!
And i didn't find any statistics module.
Comments
Have you considered using the
Have you considered using the reCAPTCHA system to offload most of the work to a third-party service? Or taking a non-captcha approach such as Mollom?
The Boise Drupal Guy!
Thank you for reply,
Thank you for reply, Garrett.
I don't know whether Mollom recognize well other languages than English. On some production sites there was much of spam on Russian language that uses Mollom. Also Mollom is a paid service. Another approach is to use akismet, but anyway all content on site is published by editors, and we have moderator to approve comments, so i need simple CAPTCHA to stop spam from bots, to reduce work for moderators. If it is possible, i prefer to not rely on third party services for CAPTCHA.
Also I look at reCAPTCHA - it's CAPTCHA is hard to recognize for me, suppose that this CAPTCHA would be recognized non-English speak user - this is a challenge.
Also third party service for CAPTCHA would not speed up page load. Page will be loaded slower - queries statistics that i posted have big count, but executed fast - i'm just trying to figure out is it possible to store these tables somewhere else but mysql, or to reduce this count. With CAPTCHA it's possible if comment form will be loaded on demand.
When I stored cache tables in MySQL, it works may be the same speed as memcached, but cache data used the same query cache as other selects and traffic between MySQL and localhost was about 5181 Mb in hour, it seems to me very huge, mysql-bin.log was grow to about 8Gb daily. After I moved all cache tables in memcached traffic became about 1234 Mb between MySQL and localhost.
Now I put sessions table to memcache and switched off Database logging - turned on syslog, CAPTCHA is shown with comment form on reply page, traffic = 513 Mb.
For 16 hours of weekend (yesterday) mysqlbin.log = 190 Mb.
Site has 180k nodes, 40k images, 35k comments. Maybe some overload was related to switching drupal from previous CMS - reindexing.
SELECT sum( totalcount )FROM
node_countershows 3149086, site is launched 22.11.2010 - 30 days ago, so it's about 100k views per day, actually this is not all "real visitors" - bots etc., but anyway page was loaded with that count.
When you use the Image
When you use the Image Captcha with the captcha module, each protected form adds an additional request to the server to generate/load the image. This request results in Drupal firing up again, so it does have a performance impact. You don't get this extra request with other options such as recaptcha or math.
Also Mollom does work with all languages:
http://mollom.com/
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Mollom is generally more
Mollom is generally more effective at blocking spam than reCAPTCHA, which at the end of the day is what matters. We fixed the Russian spam problem a number of weeks ago. Plenty of Mollom users that use Molom on non-English websites.
I appreciate you for reply,
I appreciate you for reply, Dries. Thank you for drupal.
I will try mollom.
Could anybody suggest solution to replace core statistics module? I need it to display daily popular content on site.
Pixel Ping - Ideas from boost_stats.php
Good thread: http://groups.drupal.org/node/94609
Thank you, mikeytown2. I will
Thank you, mikeytown2.
I will look at Pixel Ping. Actually, core statistics also works well for me, if it could store table node_counter in memcached like sessions.
Is Pixel ping method compatible with views and other modules (to get data from node_counter table)? Or I should disable node count, but still enable statistics module to get views features?
After removing all other queries that alter info in table (i did not yet installed mollom - just put comments form with CAPTCHA on separate page right now), I get the following stats (2 days count):
Count : 205.72k (87.78%)
Query abstract: UPDATE node_counter SET daycount = daycount + N, totalcount = totalcount + N, timestamp = N WHERE nid = N;
Count : 5.40k (2.30%)
Query abstract: UPDATE system SET info = 'S', name = 'S', filename = 'S', bootstrap = N WHERE filename = 'S';
Other queries is below 1% of total. UPDATE node_counter query work fast, and I don't think it slows down my server, but this is the most volatile table remains. When I remove this update query, I would like to log everything like Dries did, and analyse general log instead of bin - to get most time consuming queries.
You can use Google Analytics
You can use Google Analytics for page view counting and then pull back all that data using http://benbuckman.net/tech/10/12/google-analytics-custom-variables. So the data is available for Views. This is also a pretty good solution for boost/varnish/cdn sites that never hit php for typical page views.
consider ga_importer as well
The ga_importer module is focused specifically on the task of pulling in GA data. It also is pulling in more than just pageviews, specifically: pageviews, timeOnPage, bounces, visits.
I'm working on views support as well (though slowly...).
I think eventually we'll see ga_customvars is useful for adding vars to the page and ga_importer will leverage that to efficiently import the data and store it in the node_counter and also in custom tables.
knaddison blog | Morris Animal Foundation
I have restarted mysql with
I have restarted mysql with logging all queries to slow_log on live site today.
Analyzed log was about 1G.
Total run: ~6 hours
Total queries: 3737511
Total time of all queries: 510.18 seconds.
Used http://www.mysqlperformanceblog.com/files/utils/mysql_slow_log_parser
some info about config:
key_buffer_size = 128Mtable_cache = 1024
sort_buffer_size = 32M
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_size = 256M
I did not warm up query cache.
1. The most time consuming module was xmlsitemap - here i posted http://drupal.org/node/1003058. Even after add FORCE INDEX it's time consuming, and it purges queries from cache when fetches all table url_alias. Now this module have 4 statements in top of all queries, sorted by total time desc.
2. Taxonomy - when count of nodes that belong to term >1000 in my case, FORCE INDEX (node_created) becomes faster, and there is no solution in drupal to speed up such "pagination" - posted here http://forums.mysql.com/read.php?24,399387,399528#msg-399528 . I have already set FORCE INDEX for several blocks, now it's time to FORCE for several taxonomies.
3. When I will remove problems with xmlsitemap and views by term, I will reduce total time about half (37%+7.6%+5%=49.6%), and relative time cost of queries will be doubled.
4. Path becomes most time consuming mysql query, it has about 16% count of all statements, and 6.8%x2=13.6% of all time, but average time = 0.000006, and it was total executed 34.65 seconds from 6 hours - so it costs nothing.
5. Statistics module to count visits remains the most heavy write (about 87% of all), but it's time execution about 0.6%x2=1.2% of all.
Below is the results:
1. Module: xmlsitemap
# Time cost: 37%
# 1553 Queries
# Total time: 189, Average time: 0.121692
# Taking 0.001761 to 0.405447 seconds to complete
# Rows analyzed 500 - 172721
SELECT x.loc, x.lastmod, x.changefreq, x.changecount, x.priority, x.language FROM xmlsitemap x FORCE INDEX (access_status_language_loc) WHERE x.access = XXX AND x.status = XXX ORDER BY x.language, x.loc LIMIT XXX, XXX;
2. Module: Views (default taxonomy/term/% altered to plain, not %/%)
# Time cost: 7.6%
# 2581 Queries
# Total time: 38.75, Average time: 0.015015
# Taking 0.000010 to 0.485829 seconds to complete
# Rows analyzed 0 - 169126
SELECT node.nid AS nid,
node.created AS node_created
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE (node.status <> XXX) AND (term_node.tid = XXX)
ORDER BY node_created DESC
LIMIT XXX, XXX;
3. Module: Core (path)
# Time cost: 6.8%
# 616122 Queries
# Total time: 34.65, Average time: 0.000006
# Taking 0.000007 to 0.100490 seconds to complete
# Rows analyzed 0 - 1
SELECT dst FROM url_alias WHERE src = 'XXX' AND language IN('XXX', 'XXX') ORDER BY language DESC, pid DESC;
4. Module: Views (count for taxonomy/term/%)
# Time cost: 5.0%
# 2549 Queries
# Total time: 25.39, Average time: 0.009960
# Taking 0.000010 to 0.429309 seconds to complete
# Rows analyzed 0 - 0
SELECT COUNT() FROM (SELECT node.nid AS nid
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE (node.status <> XXX) AND (term_node.tid = XXX)
) count_alias;
5. Module: Core (taxonomy)
# Time cost: 4.3%
# 78890 Queries
# Total time: 22.00, Average time: 0.000278
# Taking 0.000009 to 0.100999 seconds to complete
# Rows analyzed 0 - 52
SELECT t.tid, t. FROM term_data t INNER JOIN term_node r ON r.tid = t.tid WHERE t.vid = XXX AND r.vid = XXX ORDER BY weight;
6. Module: Core (taxonomy)
# Time cost: 3.8%
# 79165 Queries
# Total time: 19.21, Average time: 0.000242
# Taking 0.000009 to 0.548559 seconds to complete
# Rows analyzed 0 - 348
SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.vid = XXX ORDER BY v.weight, t.weight, t.name;
7. Module: xmlsitemap
# Time cost: 2.3%
# 75 Queries
# Total time: 11.77, Average time: 0.156987
# Taking 0.020073 to 0.298074 seconds to complete
# Rows analyzed 13500 - 171000
SELECT x.loc, x.lastmod, x.changefreq, x.changecount, x.priority, x.language FROM xmlsitemap x FORCE INDEX (access_status_language_loc) WHERE x.access = XXX AND x.status = XXX ORDER BY x.language, x.loc LIMIT XXX, XXX;
8. Module: Core (node)
# Time cost: 1.6%
# 79079 Queries
# Total time: 8.32, Average time: 0.000105
# Taking 0.000011 to 0.100472 seconds to complete
# Rows analyzed 0 - 1
SELECT n.nid, n.type, n.language, n.uid, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, n.tnid, n.translate, r.vid, r.uid AS revision_uid, r.title, r.body, r.teaser, r.log, r.timestamp AS revision_timestamp, r.format, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = XXX;
9. Module: Core (path)
# Time cost: 1.6%
# 67484 Queries
# Total time: 8.29, Average time: 0.000122
# Taking 0.000009 to 0.100621 seconds to complete
# Rows analyzed 0 - 1
SELECT src FROM url_alias WHERE dst = 'XXX' AND language IN('XXX', 'XXX') ORDER BY language DESC, pid DESC;
10. Module: Core (taxonomy)
# Time cost: 1.3%
# 10449 Queries
# Total time: 6.39, Average time: 0.000611
# Taking 0.000011 to 0.004762 seconds to complete
# Rows analyzed 0 - 347
SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.vid = XXX ORDER BY v.weight, t.weight, t.name LIMIT XXX, XXX;
11. Module: Core (locale)
# Time cost: 1.2%
# 332936 Queries
# Total time: 5.90, Average time: 0.000002
# Taking 0.000008 to 0.101014 seconds to complete
# Rows analyzed 0 - 37
SELECT s.lid, t.translation, s.version FROM locales_source s LEFT JOIN locales_target t ON s.lid = t.lid AND t.language = 'XXX' WHERE s.source = 'XXX' AND s.textgroup = 'XXX';
12. Module: xmlsitemap
# Time cost: 1.1%
# 6 Queries
# Total time: 5.82, Average time: 0.969825
# Taking 0.000014 to 1.199930 seconds to complete
# Rows analyzed 0 - 173133
SELECT n.nid FROM node n LEFT JOIN xmlsitemap x ON x.type = 'XXX' AND n.nid = x.id WHERE x.id IS NULL AND n.type IN ('XXX','XXX') ORDER BY n.nid DESC LIMIT XXX, XXX;
13. Module: xmlsitemap
# Time cost: 0.9%
# 10 Queries
# Total time: 4.50, Average time: 0.450712
# Taking 0.043540 to 0.904846 seconds to complete
# Rows analyzed 173813 - 346619
SELECT src, dst FROM url_alias WHERE language = 'XXX' ORDER BY pid;
14. Module: Core (comments)
# Time cost: 0.7%
# 100104 Queries
# Total time: 3.52, Average time: 0.000004
# Taking 0.000009 to 0.026968 seconds to complete
# Rows analyzed 0 - 1
SELECT last_comment_timestamp, last_comment_name, comment_count FROM node_comment_statistics WHERE nid = XXX;
...
15. Module: Core (statistics)
# Time cost: 0.6%
# 18631 Queries
# Total time: 2.89, Average time: 0.000155
# Taking 0.000075 to 0.008267 seconds to complete
# Rows analyzed 0 - 1
UPDATE node_counter SET daycount = daycount + XXX, totalcount = totalcount + XXX, timestamp = XXX WHERE nid = XXX;
Use math question instead of graphics
I suggest using the captcha math question instead.
Even a turtle reaches it´s goal...Just a direct comment to
Just a direct comment to @FlemmingLeer
I've had a few sites get cluttered with spam because of the Captcha Math...Not sure about the rest of the thread, but I've been using Mollom for the past few months and that has had a much better block rate with little overhead.