drupal mysql query – out of memory problem

Every time I access ‘admin/logs/referrers’ page I have this error:

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/morazzia/public_html/includes/database.mysql.inc on line 102

Warning: Out of memory (Needed 1048506 bytes) query: pager_query SELECT COUNT(DISTINCT(url)) FROM accesslog WHERE url <> ” AND url NOT LIKE ‘%www.morazzia.com%’ in /home/morazzia/public_html/includes/database.mysql.inc on line 121

I am on dedicated server running dual core 3.4Mhz with 2gb of memory. I have 10k visitors and 120.000 page views per day. I enable cash support on drupal with cron job (every 4 hours). I enable deval module. This is first couple of lines in deval log for referrers page:

Executed 122 queries in 100.85 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted.Page execution time was 1352.91 ms.ms # where query
63.67 1 pager_query SELECT url, COUNT(url) AS hits, MAX(timestamp) AS last FROM accesslog WHERE url NOT LIKE ‘%www.morazzia.com%’ AND url <> ” GROUP BY url ORDER BY hits DESC LIMIT 0, 30
1.08 1 forum_block SELECT n.nid, n.title, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.type = ‘forum’ AND n.status = 1 ORDER BY n.nid DESC LIMIT 0, 5
0.92 1 user_block SELECT COUNT(sid) AS count FROM sessions WHERE timestamp >= 1167293169 AND uid = 0
0.89 1 module_list SELECT name, filename, throttle, bootstrap FROM system WHERE type = ‘module’ AND status = 1 ORDER BY weight ASC, filename ASC
0.87 1 drupal_lookup_path SELECT dst FROM url_alias WHERE src = ‘http://www.baberoad.com/’
0.65 1 cache_get SELECT data, created, headers, expire FROM cache WHERE cid = ‘menu:1:en’
0.63 1 pager_query SELECT COUNT(DISTINCT(url)) FROM accesslog WHERE url <> ” AND url NOT LIKE ‘%www.morazzia.com%’
0.58 1 block_list SELECT * FROM blocks WHERE theme = ‘morazzia’ AND status = 1 ORDER BY region, weight, module


Problem solved. I edit my.conf file in /etc dir.

I add:

thread_cache_size = 286
query_cache_size = 16M
table_cache = 1024
tmp_table_size = 16M

Leave a Reply

Your email address will not be published. Required fields are marked *