[dsmythe]

mysql> select * from pligg_misc_data;
+---------------+-------+
| name | data |
+---------------+-------+
| pligg_version | 9.9.5 |
+---------------+-------+
1 row in set (0.00 sec)


The following query:

SELECT count(*) FROM pligg_pageviews,pligg_links WHERE `pv_type`='story' AND link_id IN(SELECT DISTINCT link_id FROM pligg_links WHERE link_id=pv_page_id);

Causes MySQL 5.0.67 to leak memory like crazy. The tables are not large in this example.

mysql> select count(*) from pligg_pageviews;
+----------+
| count(*) |
+----------+
| 26253 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from pligg_links;
+----------+
| count(*) |
+----------+
| 1480 |
+----------+
1 row in set (0.00 sec)

I work for a hosting provider as a database administrator and am coming across this more and more frequently. The query causes upwards of 10 Gigs of memory to be consumed, and MySQL does not always properly de-allocate it.

Here is the memory consumption from running this query on a slave server with no additional traffic:

[root@slaveMySQLserver ~]# while [ 1 ] ; do ps auwx | grep 19920 | grep -v grep | awk '{print $6}' ; sleep 1 ; done
2685792
2685792 <--- Statically consuming 2.6 G
2685792
2685792
2685792
2685792
2685792
2685792
2685792
2963008 <--- Query begins
3274536
3586984
3897832
4209776
4522900
4835444
5147948
5460068
5773800
6088656
6402508
6717052
7031000
7344664
7658304
7973936
8287788
8601960
8916628
9231536 <--- Query finishes executing @ 9G

This must be fixed. A possible solution is changing the query from:

mysql> SELECT count(*) FROM pligg_pageviews,pligg_links WHERE `pv_type`='story' AND link_id IN(SELECT DISTINCT link_id FROM pligg_links WHERE link_id=pv_page_id);
+----------+
| count(*) |
+----------+
| 11329 |
+----------+
1 row in set (25.85 sec)


To:

mysql> SELECT count(*) FROM pligg_pageviews WHERE pv_type = 'story';
+----------+
| count(*) |
+----------+
| 11329 |
+----------+
1 row in set (0.05 sec)

It seems that under normal circumstances, the subquery would return all possible link_id's, therefore is not needed.

Whether this is a bug with MySQL ( it certainly seems like it ) or not, the fact of the matter is that this will completely destroy and crash MySQL servers if multiple of these queries occur at the same time.

I will be opening a bug report with MySQL shortly. In the meantime, I would hope that this would be corrected by your next release.

[dsmythe]

It appears this SQL is being generated in:

modules/page_statistics/page_statistics_main.php - Line 12

$rows = $db->get_var("SELECT count(*) FROM ".table_pageviews.",".table_links." WHERE `pv_type`='story' AND link_id IN(SELECT DISTINCT link_id FROM ".table_links." WHERE link_id=pv_page_id)");

Proposed solution would be:

$rows = $db->get_var("SELECT count(*) FROM ".table_pageviews." WHERE `pv_type`='story'");

[dsmythe]

More investigation has made me realize that this is a module for Pligg, Page Statistics, version 0.2.

Anyone know the developers of that module?

[Yankidank]

Originally this was developed by a random forum member, for now we will advise that users don't use it. I know that it's not packaged with our SVN trunk anymore. Modules are designed so they can be easily removed, so this isn't a big deal as long as we alert users to the problem. Thank you for the warning.

[sweimh]

I'm just guessing here, but I think the subquery was includes because the links and pageview table do not automatically sync. Still, a weird query. Would this make it any better? (don't have access to a pligg setup to test)

SELECT count(*)
FROM pligg_pageviews view, pligg_links links
WHERE view.`pv_type`='story' AND links.`link_id` = view.pv_page_id