By gymosphere on

My site is down now due to exceeded CPU quota. I search the slow query log and error log, found that this query takes a solid 9 seconds to execute!

SELECT t.word AS realword, i.word FROM search_total t LEFT JOIN search_index i ON t.word = i.word WHERE i.word IS NULL

I emptied search_index and search_total table, repaired and optimized it. The problem persists. This little query keeps showing on my slow_query log.

I would like to know:
1) Where can I find this query? which module uses it?
2) If it is indeed internal scripts that is wrong, who can I make it more efficient?
3) What is it used for? If it is not an essential query, how can I disable it?

Please help me.
My site has been down for almost 10 hours and I still could not find the problem. I have password protected my site's directory and no one should be able to access my site other than me.

Thanks.

Comments

nevets’s picture

nevets commented

The query comes from search.module and looks for entries in search_total that are not in search_index so that it can delete the extra entries from search_total. I have test site with no data in either of the tables an the query runs in under .004 seconds. I ran cron and with 393 entries in search_total and 591 entires in search_index the query ran in about the same amount of time. I then deleted the entries from search_index and the query time was a little under .02 seconds.

I am running 4.7.3 and word is an index field in both tables (what I would expect you to find).

After cleaning the tables, cron will re-populate them and I am guessing with more entries than my test site. But unless you are constantly changing or deleting content I would expect the number of miss-matched entries to be small.

One thing you can do is run the query by hand (using myPhpAdmin or your favorite tool) and see how long it runs and how many records it returns.

gymosphere’s picture

gymosphere commented

My discrepancy was large. I had about 700,000 rows in search_index and only about 49,000 rows in search_total.
Anyway, I emptied them. Now if I run the query, it returns zero result. But my site is still down. I am trying to disable the search of my site, but I can't even navigate to administer >> settings.

dormael’s picture

dormael commented

How many nodes and comments do you have?

Drupal on mysql needs big temporary table with many nodes and comments on list pages like search or tracker modules.
If you don't have enough temporary memory table configuration, these tables will be created on the disk.
It shows very slow result and high cpu load.

I set the following variables for this case.

max_heap_table_size = 128M
tmp_table_size = 128M
max_tmp_tables = 5
innodb_buffer_pool_size = 1024M

And changed table type from MyISAM to InnoDB.
Use Google search, replaced tracker module to lighter(custom, not yet finished) version.
We have 73917 nodes and 348604 comments now.

http://drupal.org/node/85850

Sorry for my poor english.
From South Korea.