You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by GitBox <gi...@apache.org> on 2022/11/09 12:12:14 UTC
[GitHub] [cloudstack] s-seitz opened a new issue, #6888: Slow mysql query encountered when browsing the Network Tab
s-seitz opened a new issue, #6888:
URL: https://github.com/apache/cloudstack/issues/6888
<!--
Verify first that your issue/request is not already reported on GitHub.
Also test if the latest release and main branch are affected too.
Always add information AFTER of these HTML comments, but no need to delete the comments.
-->
When using a broader IP Range (e.g multiple /16), a particular query is getting slow:
~~~
SELECT COUNT(user_ip_address.public_ip_address) FROM user_ip_address INNER JOIN vlan ON user_ip_address.vlan_db_id=vlan.id WHERE vlan.vlan_type = 'DirectAttached' AND user_ip_address.data_center_id = 1 AND user_ip_address.state != 'Free' AND user_ip_address.removed IS NULL;
~~~
##### ISSUE TYPE
* Improvement Request
##### COMPONENT NAME
API, Mysql Backend
-->
##### CLOUDSTACK VERSION
4.17.1.0
##### CONFIGURATION
Advanced Networking, VLAN isolation
##### OS / ENVIRONMENT
Ubuntu 22.04 LTS
##### SUMMARY
When using a broader IP Range (e.g multiple /16), a particular query is getting slow:
~~~
SELECT COUNT(user_ip_address.public_ip_address) FROM user_ip_address INNER JOIN vlan ON user_ip_address.vlan_db_id=vlan.id WHERE vlan.vlan_type = 'DirectAttached' AND user_ip_address.data_center_id = 1 AND user_ip_address.state != 'Free' AND user_ip_address.removed IS NULL;
~~~
This could be resolved by adding a (missing) key:
```
ALTER TABLE `user_ip_address` ADD KEY `fk_user_ip_address__state` (`state`);
```
##### STEPS TO REPRODUCE
Having a few /16 IPv4 Ranges assigned,
Enable mysql slowlog with a timeout of e.g. 2 seconds, and watch it happen.
##### EXPECTED RESULTS
Having the result faster.
##### ACTUAL RESULTS
Waiting for the result for about 2 seconds. (At least for my setup)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [cloudstack] rohityadavcloud commented on issue #6888: Slow mysql query encountered when browsing the Network Tab
Posted by GitBox <gi...@apache.org>.
rohityadavcloud commented on issue #6888:
URL: https://github.com/apache/cloudstack/issues/6888#issuecomment-1329263425
Is this critical/blocker issue @DaanHoogland ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [cloudstack] DaanHoogland commented on issue #6888: Slow mysql query encountered when browsing the Network Tab
Posted by GitBox <gi...@apache.org>.
DaanHoogland commented on issue #6888:
URL: https://github.com/apache/cloudstack/issues/6888#issuecomment-1365933941
@s-seitz I am assuming you tested this to resolve your issue?
If not please try with PR #7028
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [cloudstack] weizhouapache closed issue #6888: Slow mysql query encountered when browsing the Network Tab
Posted by GitBox <gi...@apache.org>.
weizhouapache closed issue #6888: Slow mysql query encountered when browsing the Network Tab
URL: https://github.com/apache/cloudstack/issues/6888
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [cloudstack] rohityadavcloud commented on issue #6888: Slow mysql query encountered when browsing the Network Tab
Posted by GitBox <gi...@apache.org>.
rohityadavcloud commented on issue #6888:
URL: https://github.com/apache/cloudstack/issues/6888#issuecomment-1330230722
Okay let's keep this in 4.17.2.0 but I would treat it with a lower priority among others issues; in case we don't have a PR/fix within reasonable time I'll move it to 4.18. I'm looking at cutting RC this or next week.
@s-seitz @DaanHoogland feel free to raise a PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [cloudstack] DaanHoogland commented on issue #6888: Slow mysql query encountered when browsing the Network Tab
Posted by GitBox <gi...@apache.org>.
DaanHoogland commented on issue #6888:
URL: https://github.com/apache/cloudstack/issues/6888#issuecomment-1329549452
@rohityadavcloud , for anybody using /16 nets it is a blocker and I don´t know a workaround. I would say it is critical. Any reason it would not be?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org