Ok, so I noticed one slow query, relating to CometChat, which would have locked up some things. I CCd you in on my email to them, but I fixed it for you myself.

I noticed a forum search was slow to load up (about 6 seconds) and would have locked up f_topics and f_posts writes while it ran. I think probably relates to the key_buffer_size setting. I think initially it was a bit slow as the necessary indexes weren't in the key buffer (i.e. only on disk), then it got faster.

According to MySQL, here is the state of your key buffer:
| Key_blocks_unused | 4321 |
| Key_blocks_used | 479958 |

I had made it a lot larger before, but as your DB grows, the need for a larger key buffer does too. However I would not worry too much about this for now.

I looked at your slow query log and saw too slow query patterns happening a lot...

1)

(SELECT COUNT(*) FROM (SELECT 1 FROM cms_seedy_pages r JOIN cms_seo_meta m ON (m.meta_for_type='seedy_page' AND m.meta_for_id=r.id) JOIN cms_translate tm ON tm.id=m.meta_keywords AND tm.language='EN' WHERE MATCH (tm.text_original) AGAINST ('jetta for sale') LIMIT 1000) counter) LIMIT 1;

2)

SELECT top.*,t.text_parsed AS _trans_post,l_time FROM cms_f_posts pos LEFT JOIN cms_f_topics top ON top.id=pos.p_topic_id LEFT JOIN cms_f_read_logs l ON (top.id=l.l_topic_id AND l.l_member_id=10407)
LEFT JOIN cms_translate t ON language='EN' AND top.t_cache_first_post=t.id WHERE ((p_poster=10407) AND ((t_forum_id=1 OR t_forum_id=1 OR t_forum_id=1 OR t_forum_id=1 OR t_forum_id=100 OR t_forum_id=100 OR t_forum_id=100 OR
t_forum_id=100 OR t_forum_id=101 OR t_forum_id=101 OR t_forum_id=101 OR t_forum_id=101 OR t_forum_id=103 OR t_forum_id=103 OR t_forum_id=103 OR t_forum_id=103 OR t_forum_id=104 OR t_forum_id=104 OR t_forum_id=104 OR t_forum_id=104 OR t_forum_id=105 OR t_forum_id=105 OR t_forum_id=105 OR t_forum_id=105 OR t_forum_id=106 OR t_forum_id=106 OR t_forum_id=106 OR t_forum_id=106 OR t_forum_id=107 OR
t_forum_id=107 OR t_forum_id=107 OR t_forum_id=107 OR t_forum_id=108 OR t_forum_id=108 OR t_forum_id=108 OR t_forum_id=108 OR t_forum_id=109 OR t_forum_id=110 OR t_forum_id=110 OR
t_forum_id=110 OR t_forum_id=110 OR t_forum_id=111 OR t_forum_id=111 OR t_forum_id=111 OR t_forum_id=111 OR t_forum_id=112 OR t_forum_id=112 OR t_forum_id=112 OR t_forum_id=112 OR t_forum_id=113 OR t_forum_id=113 OR
t_forum_id=113 OR t_forum_id=113 OR t_forum_id=114 OR t_forum_id=114 OR t_forum_id=114 OR t_forum_id=114 OR t_forum_id=116 OR t_forum_id=116 OR t_forum_id=116 OR t_forum_id=116 OR t_forum_id=118 OR
t_forum_id=118 OR t_forum_id=118 OR t_forum_id=118 OR t_forum_id=119 OR t_forum_id=119 OR t_forum_id=119 OR t_forum_id=119 OR t_forum_id=120 OR t_forum_id=121 OR t_forum_id=121 OR t_forum_id=121 OR
t_forum_id=121 OR t_forum_id=122 OR t_forum_id=124 OR t_forum_id=124 OR t_forum_id=124 OR t_forum_id=124 OR t_forum_id=125 OR t_forum_id=125 OR t_forum_id=125 OR t_forum_id=125 OR t_forum_id=137 OR t_forum_id=144 OR
t_forum_id=145 OR t_forum_id=145 OR t_forum_id=145 OR t_forum_id=145 OR t_forum_id=146 OR t_forum_id=146 OR t_forum_id=146 OR t_forum_id=146 OR t_forum_id=147 OR t_forum_id=147 OR t_forum_id=147 OR t_forum_id=147 OR
t_forum_id=148 OR t_forum_id=148 OR t_forum_id=148 OR t_forum_id=148 OR t_forum_id=149 OR t_forum_id=149 OR t_forum_id=149 OR t_forum_id=149 OR t_forum_id=150 OR t_forum_id=150 OR t_forum_id=157 OR t_forum_id=158 OR
t_forum_id=159 OR t_forum_id=159 OR t_forum_id=159 OR t_forum_id=159 OR t_forum_id=4 OR t_forum_id=5 OR t_forum_id=6 OR t_forum_id=6 OR t_forum_id=6 OR t_forum_id=6 OR t_forum_id=69 OR t_forum_id=69 OR t_forum_id=69 OR
t_forum_id=69 OR t_forum_id=7 OR t_forum_id=70 OR t_forum_id=70 OR t_forum_id=70 OR t_forum_id=70 OR t_forum_id=71 OR t_forum_id=71 OR t_forum_id=71 OR t_forum_id=71 OR t_forum_id=72 OR
t_forum_id=72 OR t_forum_id=72 OR t_forum_id=72 OR t_forum_id=73 OR t_forum_id=73 OR t_forum_id=73 OR t_forum_id=73 OR t_forum_id=74 OR t_forum_id=74 OR t_forum_id=74 OR t_forum_id=74 OR t_forum_id=75 OR
t_forum_id=75 OR t_forum_id=75 OR t_forum_id=75 OR t_forum_id=76 OR t_forum_id=76 OR t_forum_id=76 OR t_forum_id=76 OR t_forum_id=77 OR t_forum_id=77 OR t_forum_id=77 OR t_forum_id=77 OR t_forum_id=78
)) ) AND t_forum_id IS NOT NULL GROUP BY top.id ORDER BY t_cache_last_time DESC LIMIT 0,30;


1- I have enabled the hidden 'alternate_search_join_type' option, which changes "JOIN cms_translate" to "LEFT JOIN cms_translate". MySQL seems to be slow on some servers with it on, and others with it off, and I always have to keep toggling it on big sites. I think I've changed it for yours before, but I can't remember the date/version/etc involved. I wish MySQL was smarter as this is a pretty straight-forward query. At some point we may want to do something radical to workaround MySQL's crappiness at fulltext search:
a) Implement Sphinx for search
b) Split up our cms_translate table
c) Copy searchable content into a big text blob directly held in main records so search doesn't need to use cms_translate
d) Do testing and write a migrator, to one of the very new MySQL database engines

I don't love 'a' because this isn't a very dynamic kind of solution, works best when respecting permissions aren't required, and it would require a lot of expert setup and root-access.
I quite like 'b' because it would also reduce general user confusion with cms_translate - but it would be very hard to do, and wouldn't solve the problem so much for very large databases. Still, it would stop one big table bloating up searches on other smaller tables, isolating the problem (which people could then solve via deleting old content for example, or forcing Google site search for that content type).
'c' would use up lots of DB space and confuse people / get out of sync.
'd' might work well except I don't have a lot of faith in MySQL now, with it's slow and fragmented development.

2- This seems to be a problem with the large number of 'OR' clauses, which is due to a user being in many usergroups each of which are affirming access to a particular forum. I optimised Composr to run a de-duplication in advance, which seems to have stopped MySQL deciding to not use an index (I think it reached some kind of limit in it's optimiser).

Many thanks for taking the time to go through this and makes some tweaks for our site.

Cheers
Ade

"I have enabled the hidden 'alternate_search_join_type' option, which changes "JOIN cms_translate" to "LEFT JOIN cms_translate". MySQL seems to be slow on some servers with it on, and others with it off, and I always have to keep toggling it on big sites. I think I've changed it for yours before, but I can't remember the date/version/etc involved."

Ok, this is what caused your new problems. Turning this option on in this case PREVENTED fulltext indexing running first, which optimised the query here because the index was uneffective (i.e. too many hits were slowing it down, yet other parts of the query would have restrained it), but slowed other queries (where the fulltext index would have been effective).

I have turned alternate_search_join_type off again, and am giving this more thought.

Hi Chris,

One of my users is reporting a bail out:

"I keep getting this when I click on my involed threads.
Any ideas why?

Critical error – bailing out

This is an error that has been elevated to critical error status because it occurred during the primary error mechanism reporting system itself (possibly due to it occuring within the standard output framework). It may be masking a secondary error that occurred before this, but was never output - if so, it is likely strongly related to this one, thus fixing this will fix the other.Unfortunately a query has failed [SELECT top.*,t.text_parsed AS _trans_post,l_time FROM cms_f_posts pos LEFT JOIN cms_f_topics top on top.id=pos.p_topic_id LEFT JOIN cms_f_read_logs l on (top.id=l.l_topic_id AND l.l_member_id=14135) WHERE ((p_poster=14135) AND t_validated=1 AND 1=1 AND top.t_forum_id<>4 AND top.t_forum_id<>5 AND top.t_forum_id<>7 AND top.t_forum_id<>9 AND top.t_forum_id<>68 AND top.t_forum_id<>88 AND top.t_forum_id<>89 AND top.t_forum_id<>90 AND top.t_forum_id<>92 AND top.t_forum_id<>93 AND top.t_forum_id<>98 AND top.t_forum_id<>109 AND top.t_forum_id<>120 AND top.t_forum_id<>122 AND top.t_forum_id<>123 AND top.t_forum_id<>126 AND top.t_forum_id<>127 AND top.t_forum_id<>129 AND top.t_forum_id<>130 AND top.t_forum_id<>131 AND top.t_forum_id<>132 AND top.t_forum_id<>133 AND top.t_forum_id<>134 AND top.t_forum_id<>135 AND top.t_forum_id<>136 AND top.t_forum_id<>137 AND top.t_forum_id<>138 AND top.t_forum_id<>139 AND top.t_forum_id<>140 AND top.t_forum_id<>141 AND top.t_forum_id<>142 AND top.t_forum_id<>143 AND top.t_forum_id<>144 AND top.t_forum_id<>150 AND top.t_forum_id<>157 AND top.t_forum_id<>158 AND top.t_forum_id<>160 AND top.t_forum_id<>161 AND top.t_forum_id<>162) AND t_forum_id IS NOT NULL GROUP BY top.id ORDER BY t_cache_last_time DESC LIMIT 0,30] [Unknown column 't.text_parsed' in 'field list'] (version: 9.0.5, PHP version: 5.3.20, URL: /forum/index.php?page=vforums&type=involved_topics)Details here are intended only for the website/system-administrator, not for regular website users.
» If you are a regular website user, please let the website staff deal with this problem.

Depending on the error, and only if the website installation finished, you may need to edit the installation options (the info.php file).
ocProducts maintains full documentation for all procedures and tools. These may be found on the Composr website. If you are unable to easily solve this problem, we may be contacted from our website and can help resolve it for you.
Composr is a CMS for building websites, developed by ocProducts."

Perhaps a side effect of the tweaks that you have done ?

Can you take a look when you get a moment please.

Cheers
Ade

Sorry, fixed.