# Slow query detected in OpenNebula Sunstone **URL:** \ **Category:** Installation & Configuration\ **Created:** [December 9, 2018, 5:35am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710 "2018-12-09T05:35:07Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![mosharaflink](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/mosharaflink/32/5953_2.png) [@mosharaflink](https://forum.opennebula.io/u/mosharaflink)\ **Post date:** [December 9, 2018, 5:35am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/1 "2018-12-09T05:35:07Z")
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 ---
**Author:** ![svega](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/svega/32/5504_2.png) [@svega](https://forum.opennebula.io/u/svega)\ **Post date:** [December 10, 2018, 3:28am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/2 "2018-12-10T03:28:39Z")
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. ---
**Author:** ![mosharaflink](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/mosharaflink/32/5953_2.png) [@mosharaflink](https://forum.opennebula.io/u/mosharaflink)\ **Post date:** [December 10, 2018, 10:40am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/3 "2018-12-10T10:40:37Z")
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. ---
**Author:** ![svega](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/svega/32/5504_2.png) [@svega](https://forum.opennebula.io/u/svega)\ **Post date:** [December 11, 2018, 2:53am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/4 "2018-12-11T02:53:25Z")
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. ---
**Author:** ![jsfrerot](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/jsfrerot/32/6690_2.png) [@jsfrerot](https://forum.opennebula.io/u/jsfrerot)\ **Post date:** [January 15, 2019, 7:13pm UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/5 "2019-01-15T19:13:19Z")
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 | | 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 ? ---
**Author:** ![MedicMomcilo](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/medicmomcilo/32/5678_2.png) [@MedicMomcilo](https://forum.opennebula.io/u/MedicMomcilo)\ **Post date:** [February 19, 2019, 9:15am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/6 "2019-02-19T09:15:07Z")
We’re facing similar issue with same symptoms. However, in our case it seems obvious that it’s swapping issue. oned is using almost 20GiB of RAM which is a bit surprising. RAM usage spiked in the last week of 2018 and is very high since (all 8GiB of RAM + 12GiB of swap). There were no changes or updates (other than security kernel update) on the frontend. (By the way, for sake of completeness, we did test with previous kernel and it is the same). Our deployment didn’t increase as much in size as RAM usage would suggest. As soon as frontend is booted, we see couple of hundreds of megabytes used per second. When RAM is fully used, we start seeing slow queries (which makes sense). However, it is unclear why would frontend use that much ram. We have 570 VMs and 33 hosts. Already tried increasing innodb\_buffer\_pool size, but that doesn’t affect anything as expected. Any troubleshooting hints are more than welcome. ---
**Author:** ![ruben](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/ruben/32/19_2.png) [@ruben](https://forum.opennebula.io/u/ruben)\ **Post date:** [February 19, 2019, 2:24pm UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/7 "2019-02-19T14:24:24Z")
Thanks guys, we are already improving the performance of the query, as it seems that the nested select in the delete command is not using the existing indexes. As for the memory usage I guess you 20G could be the vitrual size, you can get the resident size with, for example: ```auto ps -a -o pid,rss,vsz,cmd | grep oned ``` ---
**Author:** ![MedicMomcilo](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/medicmomcilo/32/5678_2.png) [@MedicMomcilo](https://forum.opennebula.io/u/MedicMomcilo)\ **Post date:** [February 21, 2019, 12:55pm UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/8 "2019-02-21T12:55:14Z")
@ruben Output of command `ps -ax -o pid,rss,vsz,cmd | grep oned` is: `25242 6830256 19803136 /usr/bin/oned -f` Not really sure what to read from this, but, this output might be more relevant: ```auto root@frontend:~# free -m total used free shared buff/cache available Mem: 7807 7592 120 0 94 30 Swap: 32767 11071 21696 root@frontend:~# systemctl stop opennebula root@frontend:~# free -m total used free shared buff/cache available Mem: 7807 772 6909 0 124 6834 Swap: 32767 726 32041 root@frontend:~# ``` ---
**Author:** ![ruben](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/ruben/32/19_2.png) [@ruben](https://forum.opennebula.io/u/ruben)\ **Post date:** [February 21, 2019, 2:22pm UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/9 "2019-02-21T14:22:28Z")
Yes this is 6GB of actual (resident) memory use of oned, not 20GB. About the leader migration, you can increase the election timeout. ---
**Author:** ![MedicMomcilo](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/medicmomcilo/32/5678_2.png) [@MedicMomcilo](https://forum.opennebula.io/u/MedicMomcilo)\ **Post date:** [February 25, 2019, 11:31am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/10 "2019-02-25T11:31:19Z")
@ruben Please take into account amount of RAM **and** swap, before and after stopping OpenNebula service. Difference is roughly at 15GiB for that specific output. I agree that oned is using 6GiB of RAM, but imho, that’s just because the server has no more. If it had 32GiB, I’m pretty sure it would shoot up higher. Any troubleshooting help would be highly appreciated as we’re starting to face issues with automated tasks since ONE can become rather slow (due to paging). ---
**Author:** ![MedicMomcilo](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/medicmomcilo/32/5678_2.png) [@MedicMomcilo](https://forum.opennebula.io/u/MedicMomcilo)\ **Post date:** [March 4, 2019, 12:08pm UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/11 "2019-03-04T12:08:15Z")
Issue prior to memory upgrade: > root@frontend:~# cat /proc/sys/vm/swappiness > 1 > root@frontend:~# free -m > total used free shared buff/cache available > Mem: 7807 7606 104 0 96 15 > Swap: 32767 13547 19220 > root@frontend:~# ps -ax -o pid,rss,vsz,cmd | grep oned > 4633 6728560 20001408 /usr/bin/oned -f > root@frontend:~# systemctl stop opennebula > root@frontend:~# free -m > total used free shared buff/cache available > Mem: 7807 965 6539 0 301 6586 > Swap: 32767 1778 30989 > root@frontend:~# Issue after memory upgrade: > root@frontend:~# uptime > 11:14:58 up 4 days, 16:39, 2 users, load average: 1.88, 1.55, 1.33 > root@frontend:~# cat /proc/sys/vm/swappiness > 1 > root@frontend:~# free -m > total used free shared buff/cache available > Mem: 31999 27978 751 0 3269 3561 > Swap: 32767 813 31954 > root@frontend:~# ps -ax -o pid,rss,vsz,cmd | grep oned > 2590 26059884 28377016 /usr/bin/oned -f > 17786 1092 13136 grep --color=auto oned > root@frontend:~# systemctl stop opennebula > root@frontend:~# free -m > total used free shared buff/cache available > Mem: 31999 2289 26090 0 3618 29250 > Swap: 32767 150 32617 > root@frontend:~# However, upgrading to OpenNebula 5.8 resolved the problem. Cheers! ---
**Author:** ![sarblaster](https://avatars.discourse-cdn.com/v4/letter/s/b9e5f3/32.png) [@sarblaster](https://forum.opennebula.io/u/sarblaster)\ **Post date:** [March 27, 2019, 11:30am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/12 "2019-03-27T11:30:52Z")
We are having the same issue at the moment with our OpenNebula cluster (not the memory issue, but the original post). We get a Slow Query error every 2 minutes: Wed Mar 27 12:21:13 2019 [Z0][SQL][W]: Slow query (15.05s) 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 100000 ) AS i) LIMIT 100000 and multiple times a day our raft cluster is failing over to one of the followers. We tried upping the timeout, after receiving that recommendation from support, but to no avail. Mosharaf, Jean, are you still having this issue? Have you found the root cause or at least a fix? ---
**Author:** ![cgonzalez](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/cgonzalez/32/7096_2.png) [@cgonzalez](https://forum.opennebula.io/u/cgonzalez)\ **Post date:** [March 27, 2019, 11:47am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/13 "2019-03-27T11:47:37Z")
This query have been improved in OpenNebula 5.8, and the execution time have been considerably reduced. I encourage you to try out this new version. ---
**Author:** ![sarblaster](https://avatars.discourse-cdn.com/v4/letter/s/b9e5f3/32.png) [@sarblaster](https://forum.opennebula.io/u/sarblaster)\ **Post date:** [April 10, 2019, 8:18am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/14 "2019-04-10T08:18:07Z")
We tweaked the database settings so the database is all in ram now and the query is already much faster. We don’t get leadership elections constantly now so the worst is averted. We’ll upgrade to 5.8 after some migrations we are busy with. ---
**Author:** ![mosharaflink](https://yyz1.discourse-cdn.com/flex031/user_avatar/forum.opennebula.io/mosharaflink/32/5953_2.png) [@mosharaflink](https://forum.opennebula.io/u/mosharaflink)\ **Post date:** [April 15, 2019, 4:30am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/15 "2019-04-15T04:30:29Z")
In some blog, I find the database issues has been solved in version 5.8. I am now planning to move on the new version and hope it will solve in there. Will update here after the upgrade. ---
**Author:** ![michaudg](https://avatars.discourse-cdn.com/v4/letter/m/e9a140/32.png) [@michaudg](https://forum.opennebula.io/u/michaudg)\ **Post date:** [June 18, 2019, 11:41am UTC](https://forum.opennebula.io/t/slow-query-detected-in-opennebula-sunstone/6710/16 "2019-06-18T11:41:12Z")
We had this problem too, HA leader was migrating every two minutes. We use One 5.8.2. We solve this by tweaking _innodb\_buffer\_pool\_size_ on each frontend: `innodb_buffer_pool_size = 2G` It’s been a week now and the leader has not migrated once. We still have _Slow query_ warnings in the log but the query time has gone from 4s to 0.5s.