We're updating the issue view to help you get more done. 

perfomance problem in id search

Description

Searching an object in CA via the oject ID sometimes causes massive performance problems that can not be exactly reproduced.

/var/log/mysql/mysql-slow.log shows the SQL statement for this action:

  1. Query_time: 65.325917 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 45812789
    INSERT IGNORE INTO ca_sql_search_search_final
    SELECT swi.row_id, SUM(swi.boost)
    FROM ca_sql_search_word_index swi INNER JOIN
    ca_sql_search_words AS sw ON sw.word_id = swi.word_id WHERE
    ((sw.word LIKE 'lca_000845278%'))
    AND
    swi.table_num = 57
    GROUP BY swi.row_id;

Any idea what to do here?

Thanks,

Christoph

Environment

Linux nut 3.2.0-4-amd64 #1 SMP Debian 3.2.68-1+deb7u4 x86_64 GNU/Linux

Activity

Show:
User known
October 14, 2015, 5:43 AM

When you run this query directly in mysql command line, how long does it take?

Christoph Christoph
October 14, 2015, 5:58 AM

SELECT: 0.12 sec
DESC SELECT: 0.0 sec
SHOW KEYS FROM ca_sql_search_words: 0.17 sec
SHOW KEYS FROM ca_sql_search_word_index: 0.46 sec

The problem is that sometimes, and I cannot retrace when, the select needs a very long time as shown in /var/log/mysql/mysql-slow.log above.

Christoph Christoph
October 15, 2015, 6:55 AM

Here the last two long running querys today:

  1. Time: 151015 13:27:43

  2. User@Host: raubgut[raubgut] @ localhost []

  3. Query_time: 21.081185 Lock_time: 0.000090 Rows_sent: 0 Rows_examined: 37975882
    SET timestamp=1444908463;
    INSERT IGNORE INTO ca_sql_search_search_final
    SELECT swi.row_id, SUM(swi.boost)
    FROM ca_sql_search_word_index swi
    INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id
    WHERE
    ((sw.word LIKE 'lca_000006356%'))
    AND
    swi.table_num = 20


GROUP BY swi.row_id;

  1. Time: 151015 13:36:19

  2. User@Host: raubgut[raubgut] @ localhost []

  3. Query_time: 17.686543 Lock_time: 0.000075 Rows_sent: 0 Rows_examined: 37975897
    SET timestamp=1444908979;
    INSERT IGNORE INTO ca_sql_search_search_final
    SELECT swi.row_id, SUM(swi.boost)
    FROM ca_sql_search_word_index swi
    INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id
    WHERE
    ((sw.word LIKE 'lca_000006356%'))
    AND
    swi.table_num = 20


GROUP BY swi.row_id;

User known
October 15, 2015, 7:20 AM

What version of mysql are you running?

Christoph Christoph
October 15, 2015, 8:13 AM

Server version: 5.5.44-0+deb7u1-log on
Linux nut 3.2.0-4-amd64 #1 SMP Debian 3.2.68-1+deb7u4 x86_64 GNU/Linux

Assignee

User known

Reporter

Christoph Christoph

Labels

None

Components

Affects versions

Priority

Major
Configure