As many of you know, posting can be very slow on Cemetech, while everything else completes in a very reasonable amount of time, helping us have fast page loads. Wherein lies the culprit? For a long time, I have suspected something with the search database getting updated, and comicIDIOT and I discussed a good way to check. I logged the time for the add_search_words() call to complete for a few recent posts:


Code:


As you can see, unacceptably high numbers. I'm debating a bunch of possible solutions, including marking some of the words with frequencies around 5000-10000 and higher as common, non-indexable words (which would more than halve the size of the word to post relation table) or turning off some portions of the search. For now, I'll delve deeper into that call and see what I find. Any suggestions?

I can't make any useful suggestions, this is out of my knowledge bank, but I would like to say that my average posting waiting time lately has been around 30 seconds per post.

Perhaps you could set it up to: use another database and/or index in the background, that way the times won't actually bother anything. How do you currently save words to search for? Could there maybe be a problem with topic renaming, post deleting, etc?

Slow queries can come from a number of places. It may be worth checking/repairing/optimising the tables to see if that helps for starters, as I have had issues where extremely slow queries were caused by corrupt indices.

I have found that the MySQL query optimiser seems to go a bit crazy on large tables (picking fast indices on medium-sized tables on my developer machine and picking slow indices or no indices at all on the very large tables on the production site). It might be worth checking what the queries are doing (prefix the query with EXPLAIN to see relevant information) to verify that the optimiser is doing a good job and applying a USE INDEX hint if it isn't (or adding indices if they are missing). I turned a >6 second query to <0.2 seconds one merely by adding USE INDEX (PRIMARY) to a SELECT query and significantly reduced the load on the database in the process, but you will need to be able to identify the bottlenecks first.

You may be able to access a slow queries log from your host if there is a history of problems. If not, periodically querying SHOW PROCESSLIST will show which queries are currently being processed (along with a time); you may be able to identify particularly problematic queries this way.

There is a slow posting time? I have no noticed... it is been rather fast for me.

Qazz: It's been a problem for years.
Catherine: There are two tables. One maps words to numbers, giving each unique word a unique ID number. Some words are marked as "common" and are therefore not indexed. The second table contains indexed word-to-post mappings. Each word can appear in many posts, and each post can match many words. The second table is two columns, both numbers.
Benryves: Some excellent suggestions; I'll get right on them. Interestingly, I did check/analyze/repair/optimize, just for starters, and I then pulled the new posting time log. Can you guess where I performed those steps? I don't understand the difference, though, or rather if this was the fix, what exactly it did.


Code:


Edit: Bah, I spoke too soon. This very post:


Code:

oh, it has been a problem for years? ah, I thought that it was a recent thing, then. However, my posts usually take 2 or 5 seconds to go through, never 30 D:

The worst time that I had today was a 70-second post. :/ Anyway, I have added more debugging logging to the add_search_words() function. It looks like it first merges synonyms and "stopwords", then figures out which words it will be inserting, removing the common words, does the insertion, then checks to see what new words should be marked as common (based on the criteria that they appear in at least 40% of the posts, which I think is way too high).

*bump* I collected a few hundred posts' worth of statistics over the past two days or so; the following graph summarizes the numbers. The x-axis is the time for a given post to complete the add_search_words() function. The y-axis indicate the completion time of the four difference phases that make up each add_search_words() call. These are:

(1) Phase 1: Open stopword/synonym files, clean title and body of post using those words. Collect words to be inserted into database in arrays.
(2) Phase 2: Perform one SQL query to fetch out IDs of all words already in database, then one SQL query per additional words not yet with an ID number in the database to insert them.
(3) Phase 3: Insert (this_post_id, word_id) into the word-to-post match table for all words in the post marked as indexable.
(4) Phase 4: Remove "common words" from the database, where a common word appears in 40% or more of posts AND is also in this current post.



I find it interesting to note that Phases 2, 3, and 4 all take a long time if the complete process is long; none of them seems to take up much more than the others, which tells me that any of the queries being performed for these long posts is itself slow. Any thoughts?

Does that graph take edits into account?

Well, you could always give up on the crappy built in searching and replace it with a custom google search (or whatever other engine floats your boat). Then it's indexed asynchronously and gives better results anyway.

comicIDIOT: It does not.
Kllrnohj: Is there a way to properly wrap it in the template?

I think this still works: http://www.google.com/cse/

But... ads and stuff. Plus with Google cutting every fringe project, I wouldn't be surprised if this went away. I did do some customization to see how it would look, though...

?



I wouldn't really call search a fringe project :p But even if it is cut there are alternatives that are basically the same thing. I think Bing has one, others probably do as well.

*bump*

Could we re-touch on this topic? The past few days have been extremely slow, both for viewing and actually posting. Are you still keeping track of how long it takes to post, and what sections are taking the longest? If so, and if it is still the search thing, then something needs to be done about it. I personally like having Search be part of Cemetech and not having it rely on Google or Bing, but if it causes reading and posting times to go above 1 minute, which seems to be the case from me and a few others, then it can't be helped, imo.

There aren't that many outliers currently on the search database time logging, which makes me worry we might be under a low-scale accidental or intentional DDoS from people who might not want Cemetech to be swift and successful.


Code:

Is there any information to get that info from your host?

There is, but there's a bit more sleuthing that I can do first on my end. Are we continuing to see these problems?