cloudstack-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Carles Figuerola <carle...@gmail.com>
Subject Re: database tag query getting delayed indefinitely
Date Thu, 30 Apr 2015 14:40:43 GMT
Yes, I was trying different configurations and it has worked when using
mysql-5.5 on ubuntu and mysql-5.6 on centos. Maybe it did get fixed on ACS
4.5.1 as my test environment was with CentOS 6.6 and the default mysql
version which is mysql-server-5.1.73-3.el6_5.x86_64. I tried reinstalling
different version on the 4.4 branch and all of them exhibited this problem
in this mysql version.

Thanks,

Carles

On Tue, Apr 28, 2015 at 11:32 AM, ilya <ilya.mailing.lists@gmail.com> wrote:

> No such issues when i ran this query against my test env, however i dont
> have the same tags in db so i received blank response.. My test env is ACS
> 4.5.1 with MySQL 5.1.73 on CentOS 6.6+
>
> Try dumping your DB and importing it to another host with newer mysql and
> run there. Perhaps mysql bug?
>
>
> On 4/22/15 9:17 PM, Carles Figuerola wrote:
>
>> Hi all,
>>
>> While setting a new ACS installation using centos6.6 and 4.4.2 (I have
>> several fully functioning ones running ubuntu12.04 + 4.3.0) I'm finding
>> out
>> that filtering VMs with tags hangs the mysql database.
>>
>> Whenever an API query like this is run:
>>
>>
>> apiKey=REDACTED&command=listVirtualMachines&isrecursive=true&listall=true&response=json&tags[0].key=chef_env&tags[0].value=fqa1&zoneid=30c4a514-402c-4413-a3fc-c65450a98f55&signature=REDACTED
>>
>> a query in the database like this is seen:
>>
>> SELECT DISTINCT(user_vm_view.id) FROM user_vm_view WHERE
>> user_vm_view.account_type != 5  AND
>> user_vm_view.state != 'Destroyed'  AND
>> user_vm_view.display_vm = 1  AND
>> ( ( user_vm_view.tag_key = _binary'chef_env'  AND
>> user_vm_view.tag_value = _binary'fqa1' ) ) AND
>> user_vm_view.removed IS NULL
>> ORDER BY user_vm_view.id ASC  LIMIT 0, 500
>> ;
>>
>> This should return 2 items, and doing this query manually with around 5 of
>> the where constrictions returns the values in less than a second. Running
>> the command above might take from 4 minutes to several days and it takes
>> 100% of a core.
>>
>> Running a listall VMs without any kind of restrictions shows the 4 user
>> VMs
>> in milliseconds.
>>
>> Is there anything specific to tags that might slow down this kind of
>> query?
>>
>> Thanks
>>
>> Carles Figuerola
>>
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message