Some weird things are happening - not sure if it is the same issue:

This is one of my email notifications:
"This e-mail from The Mk1 Golf Owners Club was generated automatically and sent to you due to your notification settings. You should not reply directly.

There has been a new post by ans4r. This notification was sent due to your notification settings.

You may view the post from the following link:
http://vwgolfmk1.org.uk/forum/index.php?page=topicview&type=findpost&id=1366818#post_1366818.

The post is as follows:

ans4r said

he's the guy moaning on the facebook page!


If you do not want to receive these e-mails then you will find an unmonitor button on the topic. You can change your default notification settings by editing your profile."

If I click the link in it it just says "to many redirects"

Cheers
Ade

Looking again this morning I can see that MySQL has about 300% CPU and has done for a significant while according to top.

I have restarted MySQL and the CPU usage has returned to normal.

Still not sure what is going on though !!

Cheers
Ade

Also do you think that there would be any advantage in upgrading to a later MySQL and potentially using the innodb engine instead. I know Chris did a lot of tweaks for us in the early days, was this myisam specific ?

Hi,

I just tried to post to this thread and it took over a minute to complete:
http://vwgolfmk1.org.uk/forum/index.php?page=topicview&id=website-issues_2%2Fis-there-a-problem-with&redirected=1#post_1366862

The process list was:
Id User Host db Command Time State Info
4 DELAYED localhost vwgolfm_cms_live Delayed insert 1 Waiting for INSERT
5 DELAYED localhost vwgolfm_cms_live Delayed insert 1 Waiting for INSERT
11974 DELAYED localhost eximstats Delayed insert 268 Waiting for INSERT
12742 vwgolfm_cms localhost vwgolfm_cms_live Query 131 Sending data SELECT ((SELECT COUNT(*) FROM (SELECT 1 FROM cms_f_posts r JOIN cms_f_topics s ON r.p_topic_id=s.id JOIN cms_translate t1 ON t1.id=r.p_post AND t1.language='EN' WHERE MATCH (t1.text_original) AGAINST ('Search forum (60 days)') AND (s.t_forum_id=86) AND p_time>1382435961 AND t_forum_id IS NOT NULL AND (p_intended_solely_for IS NULL OR (p_intended_solely_for=25180 OR p_poster=25180)) AND p_validated=1 LIMIT 1000) counter)+(SELECT COUNT(*) FROM (SELECT 1 FROM cms_f_posts r JOIN cms_f_topics s ON r.p_topic_id=s.id WHERE MATCH (r.p_title) AGAINST ('Search forum (60 days)') AND (r.p_title IS NOT NULL) AND (s.t_forum_id=86) AND p_time>1382435961 AND t_forum_id IS NOT NULL AND (p_intended_solely_for IS NULL OR (p_intended_solely_for=25180 OR p_poster=25180)) AND p_validated=1 LIMIT 1000) counter)+(SELECT COUNT(*) FROM (SELECT 1 FROM cms_f_posts r JOIN cms_f_topics s ON r.p_topic_id=s.id WHERE MATCH (s.t_description) AGAINST ('Search forum (60 days)') AND (s.t_description IS NOT NULL) AND (s.t_forum_id=86) AND p_time>1382435961 AND t_forum_id IS NOT NULL AND (p_intended_solely_for IS NULL OR (p_intended_solely_for=25180 OR p_poster=25180)) AND p_validated=1 LIMIT 1000) counter)) LIMIT 1
12752 vwgolfm_cms localhost vwgolfm_cms_live Query 125 Locked UPDATE cms_translate SET source_user=27676, text_original='[b]SOLD[/b]?', text_parsed='return unserialize(\\"a:6:{i:0;a:2:{i:0;a:5:{i:0;s:20:\\\\\\"tcpfunc_COMCODE_BOLD\\\\\\";i:1;a:2:{s:5:\\\\\\"_GUID\\\\\\";s:32:\\\\\\"acbc4fds910703f81b619sf74ac24c91\\\\\\";s:7:\\\\\\"CONTENT\\\\\\";O:12:\\\\\\"Tempcode\\\\\\":6:{s:18:\\\\\\"code_to_preexecute\\\\\\";s:70:\\\\\\"\\\\$TPL_FUNCS[\\'string_attach_52b5667fcf1ac6.85265179\\']=\\\\\\"echo \\\\\\\\\\\\\\"SOLD\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\\\";s:9:\\\\\\"seq_parts\\\\\\";a:1:{i:0;a:5:{i:0;s:37:\\\\\\"string_attach_52b5667fcf1ac6.85265179\\\\\\";i:1;a:0:{}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;s:0:\\\\\\"\\\\\\";}}s:19:\\\\\\"preprocessable_bits\\\\\\";a:0:{}s:11:\\\\\\"last_attach\\\\\\";s:0:\\\\\\"\\\\\\";s:9:\\\\\\"pure_lang\\\\\\";N;s:8:\\\\\\"codename\\\\\\";s:7:\\\\\\"(mixed)\\\\\\";}}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;N;}i:1;a:5:{i:0;s:37:\\\\\\"string_attach_52b5667fcf3752.39928797\\\\\\";i:1;a:0:{}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;s:0:\\\\\\"\\\\\\";}}i:1;a:0:{}i:2;s:7:\\\\\\"(mixed)\\\\\\";i:3;s:0:\\\\\\"\\\\\\";i:4;N;i:5;s:297:\\\\\\"\\\\$TPL_FUNCS[\\'tcpfunc_COMCODE_BOLD\\']=\\\\\\"eval(\\\\\\\\\\\\$RESET_VAR_CODE); echo \\\\\\\\\\\\\\"<strong class=\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"comcode_bold\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\">\\\\\\\\\\\\\\",\\\\\\\\n\toutput_tempcode_parameter( isset (\\\\\\\\\\\\$bound_CONTENT)?\\\\\\\\\\\\$bound_CONTENT:NULL,\\\\\\\\\\\\\\"CONTENT\\\\\\\\\\\\\\",\\\\\\\\\\\\\\"COMCODE_BOLD\\\\\\\\\\\\\\"),\\\\\\\\n\t\\\\\\\\\\\\\\"</strong>\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\$TPL_FUNCS[\\'string_attach_52b5667fcf3752.39928797\\']=\\\\\\"echo \\\\\\\\\\\\\\"?\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\\\";}\\");\\n' WHERE (id=1381960542 AND language='EN')
12773 vwgolfm_cms localhost vwgolfm_cms_live Query 108 Locked INSERT INTO cms_translate (source_user, broken, importance_level, text_original, text_parsed, language) VALUES (6, 0, 4, 'Hi again Matt\\n\\nApologies if you\\'ve not heard anything back yet. The Chairman (Dano) had messaged me on the 4th December with the info to come back to you on, but for some reason I didn\\'t pick it up so sorry about that.\\n\\nThe feedback I have is that they have decided not to finalise a package to allow you to sell these on the forum as it will clash with some club merchandise that is planned to be released shortly. \\n\\nIt\\'s not the first time we\\'ve had to say no, there\\'s quite a lot of gear in the pipeline to be released.\\n\\nGood luck with your venture though and I hope it works out well for you.\\n\\nMerry Christmas\\n\\nAndy', 'return unserialize(\\"a:6:{i:0;a:1:{i:0;a:5:{i:0;s:37:\\\\\\"string_attach_52b5669066cb37.56498806\\\\\\";i:1;a:0:{}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;s:0:\\\\\\"\\\\\\";}}i:1;a:0:{}i:2;s:7:\\\\\\"(mixed)\\\\\\";i:3;s:0:\\\\\\"\\\\\\";i:4;N;i:5;s:950:\\\\\\"\\\\$TPL_FUNCS[\\'string_attach_52b5669066cb37.56498806\\']=\\\\\\"echo \\\\\\\\\\\\\\"Hi again Matt\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Apologies if you&#039;ve not heard anything back yet. The Chairman (Dano) had messaged me on the 4th December with the info to come back to you on, but for some reason I didn&#039;t pick it up so sorry about that.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"The feedback I have is that they have decided not to finalise a package to allow you to sell these on the forum as it will clash with some club merchandise that is planned to be released shortly. \\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"It&#039;s not the first time we&#039;ve had to say no, there&#039;s quite a lot of gear in the pipeline to be released.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Good luck with your venture though and I hope it works out well for you.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Merry Christmas\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Andy\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\\\";}\\");\\n', 'EN')
12783 vwgolfm_cms localhost vwgolfm_cms_live Query 101 Locked INSERT INTO cms_translate (source_user, broken, importance_level, text_original, text_parsed, language) VALUES (456, 0, 4, 'Thanks - I am getting that for that post as well - not sure why.\\n\\nI have reset a few things and asked Composr to take a look at what is going on.', 'return unserialize(\\"a:6:{i:0;a:1:{i:0;a:5:{i:0;s:37:\\\\\\"string_attach_52b566974fc3e8.96662150\\\\\\";i:1;a:0:{}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;s:0:\\\\\\"\\\\\\";}}i:1;a:0:{}i:2;s:7:\\\\\\"(mixed)\\\\\\";i:3;s:0:\\\\\\"\\\\\\";i:4;N;i:5;s:247:\\\\\\"\\\\$TPL_FUNCS[\\'string_attach_52b566974fc3e8.96662150\\']=\\\\\\"echo \\\\\\\\\\\\\\"Thanks - I am getting that for that post as well - not sure why.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"I have reset a few things and asked Composr to take a look at what is going on.\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\\\";}\\");\\n', 'EN')
12849 vwgolfm_cms localhost vwgolfm_cms_live Query 34 Locked INSERT INTO cms_translate (source_user, broken, importance_level, text_original, text_parsed, language) VALUES (6, 0, 4, 'Hi again Matt\\n\\nApologies if you\\'ve not heard anything back yet. The Chairman (Dano) had messaged me on the 4th December with the info to come back to you on, but for some reason I didn\\'t pick it up so sorry about that.\\n\\nThe feedback I have is that they have decided not to finalise a package to allow you to sell these on the forum as it will clash with some club merchandise that is planned to be released shortly. \\n\\nIt\\'s not the first time we\\'ve had to say no, there\\'s quite a lot of gear in the pipeline to be released.\\n\\nGood luck with your venture though and I hope it works out well for you.\\n\\nMerry Christmas\\n\\nAndy', 'return unserialize(\\"a:6:{i:0;a:1:{i:0;a:5:{i:0;s:37:\\\\\\"string_attach_52b566da5ee8b2.25491201\\\\\\";i:1;a:0:{}i:2;i:1;i:3;s:0:\\\\\\"\\\\\\";i:4;s:0:\\\\\\"\\\\\\";}}i:1;a:0:{}i:2;s:7:\\\\\\"(mixed)\\\\\\";i:3;s:0:\\\\\\"\\\\\\";i:4;N;i:5;s:950:\\\\\\"\\\\$TPL_FUNCS[\\'string_attach_52b566da5ee8b2.25491201\\']=\\\\\\"echo \\\\\\\\\\\\\\"Hi again Matt\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Apologies if you&#039;ve not heard anything back yet. The Chairman (Dano) had messaged me on the 4th December with the info to come back to you on, but for some reason I didn&#039;t pick it up so sorry about that.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"The feedback I have is that they have decided not to finalise a package to allow you to sell these on the forum as it will clash with some club merchandise that is planned to be released shortly. \\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"It&#039;s not the first time we&#039;ve had to say no, there&#039;s quite a lot of gear in the pipeline to be released.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Good luck with your venture though and I hope it works out well for you.\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Merry Christmas\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"
\\\\\\\\\\\\\\";echo \\\\\\\\\\\\\\"Andy\\\\\\\\\\\\\\";\\\\\\";\\\\n\\\\\\";}\\");\\n', 'EN')
12904 vwgolfm_cms localhost vwgolfm_cms_live Sleep 0 NULL
12905 vwgolfm_cms localhost vwgolfm_cms_live Sleep 1 NULL
12910 root localhost NULL Query 0 NULL show full processlist

What is going on ?

Several users are reporting double posts - I guess that is because they are not waiting for post completion and hitting submit again.

We are getting cannot display because of too many redirects.

CPU usage because of mysqld is riding high sometimes.

The only changes that have been made recently to the site were Chris's addition of the @ notifications - could that have anything to do with it ?

Help !!

Cheers
Ade

I will take a look at this today.

Hi,

I have optimised the url moniker generation problem in the initial report. Now the inefficient LIKE '%...' query clause does not need to happen.

I will try and look at other things mentioned here tomorrow.

Cheers Chris appreciate it.

The redirect loop was caused by a new URL moniker not generating, after a previous was deprecated. This will have been due to a topic rename but MySQL being terminated before the new URL moniker generated and saved.

I have put in a workaround as obviously Composr should detect if all URL monikers are deprecated rather than getting into a loop.

My optimisations largely were to mitigate the bottlenecks of MyISAM (i.e. the fact that locks lock the whole table [both read locks and write locks], so slow queries are a big no-no and minimising writes is also advantageous). So on that basis, InnoDB shouldn't cause a problem.

That said:
a) If there are slow queries, we will do our best to fix them (as I have just done here for example)
b) InnoDB only supports fulltext search since 5.6. I have not tried it myself yet, but I did some quick Googling and it looks like it has some complexities in setup and potentially performance.

For clarity...

The URL moniker slow down would probably have suddenly popped up due to increased seasonal usage combined with a growing number of forum topics.

The second query dump shows a search on the search placeholder label. Composr will now return no results for such searches, as it should essentially be regarded the same as a blank search.

I also note this is running very slow. If I change the join to a LEFT JOIN it runs fast. I am looking into it.

Okay, fulltext search is being slow for common queries, period.

It might therefore be a good idea to go to innodb, because then if a query takes ages at least it won't lock out (=pile up) table writes while doing it.



Here's a performance example...

SELECT COUNT(*) FROM cms_translate t1 WHERE MATCH (t1.text_original) AGAINST ('mercedes') LIMIT 10
--------------

+----------+
| COUNT(*) |
+----------+
| 343 |
+----------+
1 row in set (1.65 sec)




SELECT COUNT(*) FROM cms_translate t1 WHERE MATCH (t1.text_original) AGAINST ('corsa') LIMIT 10
--------------

+----------+
| COUNT(*) |
+----------+
| 795 |
+----------+
1 row in set (2.09 sec)



SELECT COUNT(*) FROM cms_translate t1 WHERE MATCH (t1.text_original) AGAINST ('vauxhall') LIMIT 10
--------------

(I cancelled after 10 seconds)