Slow query detected in OpenNebula Sunstone


(Mosharaf Hossain) #1

Hi,
I have configured OpenNebula sunstone with MySQL backend and running HA with 3 servers. Sometimes in Sunstone server log, I am getting the error “Slow query (3.53s) detected: DELETE FROM logdb WHERE timestamp > 0 AND log_index >= 0 AND fed_index = -1 AND log_index < ( SELECT MIN(i.log_index) FROM ( SELECT log_index FROM logdb WHERE fed_index = -1 AND timestamp > 0 AND log_index >= 0 ORDER BY log_index DESC LIMIT 500000 ) AS i) LIMIT 100000”. Also also getting that running HA service migration master Virtual IP frequently.

Regards
Mosharaf


(Sergio) #2

As the log shows, you are dealing with a slow query. This means that your database is not performing as expected, and it might be due to a slow storage backend or lack of resources.


(Mosharaf Hossain) #3

is there any recommendation of MySQL to run with OpenNebula?? Currently our storage/db cluster are working properly for another services. In my understating, normally services are working properly but whilst database size are increasing, problem are starting.

If you have any recommendation, please share to apply to check.


(Sergio) #4

Recommendations for OpenNebula’s database are not much different from other databases. You can tweak a lot of parameters but you will experience the biggest improvements by increasing the access speed to your data. By default mariadb only uses 128MB for innodb_buffer_pool_size. OpenNebula’s database is not big so you can easily fit it entirely inside RAM by tuning this value. If you are sharing the server with other databases, you will also have to take them into account.


(Jean Sébastien Frerot) #5

I Have the same issue:

Slow query (1.65s) detected: DELETE FROM logdb WHERE timestamp > 0 AND log_index >= 0 AND fed_index = -1 AND log_index < (  SELECT MIN(i.log_index) FROM (    SELECT log_index FROM logdb WHERE fed_index = -1 AND      timestamp > 0 AND log_index >= 0       ORDER BY log_index DESC LIMIT 500000  ) AS i) LIMIT 100000

But I have innodb_buffer_pool set to 2G. When the query runs, it uses mostly CPU, not disk IO.
an explain, show the following:

+----+-------------+------------+------------+-------------+-------------------------------------+-----------------------+---------+-------+--------+----------+-----------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                       | key                   | key_len | ref   | rows   | filtered | Extra                                               |
+----+-------------+------------+------------+-------------+-------------------------------------+-----------------------+---------+-------+--------+----------+-----------------------------------------------------+
|  1 | PRIMARY     | logdb      | NULL       | index_merge | PRIMARY,fed_index_idx,timestamp_idx | fed_index_idx,PRIMARY | 9,4     | NULL  |    134 |    50.00 | Using intersect(fed_index_idx,PRIMARY); Using where |
|  2 | SUBQUERY    | <derived3> | NULL       | ALL         | NULL                                | NULL                  | NULL    | NULL  |  68213 |   100.00 | NULL                                                |
|  3 | DERIVED     | logdb      | NULL       | ref         | PRIMARY,fed_index_idx,timestamp_idx | fed_index_idx         |  5      | const | 272853 |    25.00 | Using where                                         |
+----+-------------+------------+------------+-------------+-------------------------------------+-----------------------+---------+-------+--------+----------+-----------------------------------------------------+

Maybe there is a way to optimize the query ?