Large amount of mysql joins performed without indexes after forum access is installed

I don't propose anything. I posted on here because I am looking for help. I think I may have found an issue with this module that maybe someone can fix?

Adding an index to the name column of ACL and then changing it to an int has resolved the issue. Thank you. Should I post this in the ACL module area?

we were having a lot of users complain that it was very slow to submit or reply to forum items. as well the mysql slow log query and application mysqltuner were reporting issues with non indexed joins.
I added the index, restarted mysql and it still reported joining issues. I then changed it to int and restart mysql and no more reports of mysql join index issues.

When I made this adjustment, it did seem to improve the performance of the site. We have had an ongoing issue though, and I am not sure if it is related to the un-indexed inner joins. On an ongoing bases the site seems to get slower by the day after people have used it. Eventually if I restart the mysql services or the server it seems to speed things up. I wanted to fix this issue as it was reported as an issue by the mysqltuner software, and I had a feeling that it was contributing to the overall slow performance. As well I have another server with about 40 drupal websites on it, and none of those drupal websites report issues with joins being performed without indexes. So it really makes me believe there is something wrong with this site in particular.

Here is a more specific issue that has been happening:
When someone clicks "Save" to submit and save new content to the site, it takes almost 10-20 seconds for the post to actually go through.

I restarted the server this morning though, and this issue went away.

Currently the mysql slow query log is still reporting the odd join that is performed without indexes, and I am still trying to trace where that is happening.

I will see if I can run some more tests when I have some more time and provide you with more information and numbers.

We are also running the domain access module to run affiliate sites, which is why I began to run the mysqltuner tool in the first place. The domain access module put such a load of queries that the page loading times got really really slow. After running the mysqltuner and making recommended adjustments it was about 300 times faster. (Although we still have this issue of bogging down over time). The sites in question are biglines.com and kickinghorsemagazine.com

salvis, do you know how I can easily identify joins that are performed without indexes from my slow query log? The mysqltuner is still reporting that joins are happening without indexes (Not as many as before). I am just not sure what to look for in the log specifically, was hoping that I could run something like tail -f -n 100 /var/lib/mysql/mysqlslow.log | grep -i INDEX to pull out the queries that are done on stuff not indexed. The following query below I noticed was still coming up about forum_access, and this seems to happen when I submit to the forum.

SELECT DISTINCT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count
            FROM node n
            INNER JOIN node_comment_statistics l ON n.nid = l.nid
            INNER JOIN term_node r ON n.vid = r.vid
             INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 0 AND na.realm = 'domain_id') OR (na.gid = 1 AND na.realm = 'forum_access'))) AND (  n.status = 1
            )GROUP BY r.tid;

Ok. I have updated to the dev module of acl that was dated 11/25/2010

Drupal database update
user warning: Duplicate key name 'name' query: ALTER TABLE acl ADD INDEX name (name) in /home/biglines/public_html/includes/database.mysql-common.inc on line 434.

Updates were attempted. If you see no failures below, you may proceed happily to the administration pages. Otherwise, you may need to update your database manually. All errors have been logged.

    * Main page
    * Administration pages

The following queries were executed
acl module
Update #6002

    * ALTER TABLE {acl} ADD `number` INT DEFAULT NULL
    * Failed: ALTER TABLE {acl} ADD INDEX name (name)
    * ALTER TABLE {acl} ADD INDEX number (number)

Ok thank you. I still haven't been able to properly identify the indexes that are still happening on our website without joins. So it is difficult for me to tell if the ACL module was what was causing performance issues on the site. But I do believe that what you have fixed would most likely really improve the performance of the ACL and Forum Access modules.

We are just working on transferring the website to another server, and the backups need to be re-configured. I should be able to apply this in about a week.

Currently we are running ACL 6.x-1.2, and Forum Access 6.x-1.5. Should we be running ACL 6.x-1.x-dev?

Salvis,

I just went to upgrade ACL. It gives me an error, but I wasn't sure if this is what I should be expecting. Could you verify?

Drupal database update

    * user warning: Duplicate column name 'number' query: ALTER TABLE acl ADD `number` INT DEFAULT NULL in /home/biglines/public_html/includes/database.mysql-common.inc on line 298.
    * user warning: Duplicate key name 'name' query: ALTER TABLE acl ADD INDEX name (name) in /home/biglines/public_html/includes/database.mysql-common.inc on line 434.
    * user warning: Duplicate key name 'number' query: ALTER TABLE acl ADD INDEX number (number) in /home/biglines/public_html/includes/database.mysql-common.inc on line 434.

Updates were attempted. If you see no failures below, you may proceed happily to the administration pages. Otherwise, you may need to update your database manually. All errors have been logged.

    * Main page
    * Administration pages

The following queries were executed
acl module
Update #6002

    * Failed: ALTER TABLE {acl} ADD `number` INT DEFAULT NULL
    * Failed: ALTER TABLE {acl} ADD INDEX name (name)
    * Failed: ALTER TABLE {acl} ADD INDEX number (number)

Hi Salvis,

I also just installed the Forum Access 6.x-1.x-dev and followed your instructions from #20. The {acl.name} was indeed set to "INT" still. So I modified it back to VARCHAR.

Everything seems to be running correctly on the site. Maybe you could just verify if the error above was expected, and then we can proceed to testing performance.

Are there any specific commands you would like to execute on our server to test the performance?