Kuasar, It looks like you have the innodb_buffer_pool_size set to the default. Can you try and increase this to 70% of your available memory? I think you mentioned you have 4GB assigned. If it's a dedicated server/VM for MySQL, can you edit your my.cnf and change the innodb_buffer_pool_size = 2.8G and do a restart on MySQL?
-Si On Fri, Aug 25, 2023 at 11:34 AM Kuasar <ricardo.per...@kuasar.co.invalid> wrote: > Hey guys > > Rohit that sounds great about the performance improvement. Regarding the > questions > > root admin, domain admin and/or user accounts > > Yes, for all users/projects/accounts etc > > multiple management servers > > 2 management servers, but our front is pointing to only one > > +-------------------------+-----------+ > | Variable_name | Value | > +-------------------------+-----------+ > | innodb_buffer_pool_size | 134217728 | > +-------------------------+-----------+ > > > BR, > > Ricardo Pertuz > > > On 25 Aug 2023, 10:56 AM -0500, Rohit Yadav <rohit.ya...@shapeblue.com>, > wrote: > > Hi Ricardo, > > > > Thanks for sharing, in addition to what Simon has asked; can you also > share if this behaviour is consistent across different types of account > (root admin, domain admin and/or user accounts) and domains, was there any > change in the infrastructure after which this was observed? > > > > We've introduced a small optimisation with > https://github.com/apache/cloudstack/pull/7911 that you've tested, this > is coming in 4.18.1.0 and later which calls the listVirtualMachinesMetrics > API with a limited details view, this is used to render the instance list > page in the UI. I think most users could see 5-10x improvements in the > instance page load times. > > > > > > Regards. > > > > ________________________________ > > From: Simon Weller <siwelle...@gmail.com> > > Sent: Friday, August 25, 2023 19:18 > > To: users@cloudstack.apache.org <users@cloudstack.apache.org> > > Subject: Re: Query to Cloudstack API is degraded > > > > Kuasar, > > > > How big is your installation, and what do you have your MySQL > > innodb_buffer_pool_size set to? > > > > Are you running a single, or multiple management servers? > > > > -Si > > > > > > On Fri, Aug 25, 2023 at 8:43 AM Kuasar <ricardo.per...@kuasar.co > .invalid> > > wrote: > > > > > Hi Rohit, > > > > > > Thanks for you prompt reply. > > > > > > Could you say try with a MySQL 8.x? > > > > > > We are having this problem in production which is bigger and changing > the > > > db engine right now will take us weeks > > > > > > you could try Ubuntu 22.04/20.04 > > > > > > Yes, we are using Ubuntu 20.04 > > > > > > Can you measure (say using dev tools of your browser) > > > > > > List-vms always takes around 5.6s checking on the timing tab of the > > > browser and > > > > > > http://<cloudstack mgmt host domain or > IP>:8080/client/#/vm?details=min > > > http://<cloudstack mgmt host domain or > > > IP>:8080/client/#/vm?details=servoff,tmpl,nics > > > > > > Only 1 second, much better > > > > > > > > > BR, > > > > > > Ricardo Pertuz > > > > > > > > > On 25 Aug 2023, 6:41 AM -0500, Rohit Yadav <rohit.ya...@shapeblue.com > >, > > > wrote: > > > > Hi Ricardo, > > > > > > > > Thanks for sharing. I can't say without more details on how to > reproduce > > > the env/condition, or look at the DB server. It might be statistics or > > > something else, you could try to log mysql queries and see if they're > > > slow(ing down). > > > > > > > > Could you say try with a MySQL 8.x server and use a different host > > > server (for example, there is an issue reportedly affecting EL9 - > > > https://github.com/apache/cloudstack/issues/7910; you could try Ubuntu > > > 22.04/20.04 or EL8 to rule out the DB server). > > > > > > > > Can you measure (say using dev tools of your browser) the time it > takes > > > to render the list VM API call (specifically the API would be called > > > listVirtualMachinesMetrics API), share the outcomes with us. > > > > > > > > Can you try the following URLs for the list instances page in the UI, > > > see if any makes difference in loading times for you: > > > > > > > > > > > > http://<cloudstack mgmt host domain or > IP>:8080/client/#/vm?details=min > > > > http://<cloudstack mgmt host domain or > > > IP>:8080/client/#/vm?details=servoff,tmpl,nics > > > > > > > > Compared to the following that you've by default: > > > > > > > > > > > > http://<cloudstack mgmt host domain or IP>8080/client/#/vm > > > > > > > > I've raised a PR to explore an idea further: > > > > https://github.com/apache/cloudstack/pull/7911 > > > > > > > > > > > > Regards. > > > > > > > > ________________________________ > > > > From: Kuasar <ricardo.per...@kuasar.co.INVALID> > > > > Sent: Thursday, August 24, 2023 19:21 > > > > To: users@cloudstack.apache.org <users@cloudstack.apache.org>; > > > users@cloudstack.apache.org <users@cloudstack.apache.org> > > > > Subject: Re: Query to Cloudstack API is degraded > > > > > > > > Hi Rohit, > > > > > > > > Thanks for asking, my reply below > > > > > > > > - which APIs you think have degraded -> > > > > listing instances > > > > > > > > - how have you concluded that they are degrading? -> > > > > we have an upper app that has a configured timeout and it wasn’t > firing, > > > we had to change the value, and it is visible as well, it takes 5.6s > > > bringing 5 vms. > > > > > > > > - are those list API or other async/action oriented APIs? -> > > > > yes, it it async > > > > > > > > - which version of CloudStack and MySQL server is this, other details > > > about your env (hypervisor, scale/size of resources etc). > > > > ACS 4.18.0. —> 4GB 2Cores > > > > Mariadb 10.5.x -> 4GB 2Cores > > > > Hyp KVM > > > > > > > > - does restarting mgmt server help? any other information on how to > > > reproduce this? > > > > no change > > > > > > > > We are guessing it is related to database performance/index as it not > > > happening in QA > > > > > > > > > > > > Atte, > > > > > > > > Ricardo Pertuz > > > > > > > > > > > > On 24 Aug 2023, 6:33 AM -0500, Rohit Yadav < > rohit.ya...@shapeblue.com>, > > > wrote: > > > > > Hi Ricardo, > > > > > > > > > > Can you share more details, such as; > > > > > > > > > > - which APIs you think have degraded > > > > > - how have you concluded that they are degrading? > > > > > - are those list API or other async/action oriented APIs? > > > > > - which version of CloudStack and MySQL server is this, other > details > > > about your env (hypervisor, scale/size of resources etc) > > > > > - does restarting mgmt server help? any other information on how to > > > reproduce this? > > > > > > > > > > > > > > > Regards. > > > > > > > > > > ________________________________ > > > > > From: Kuasar <ricardo.per...@kuasar.co.INVALID> > > > > > Sent: Wednesday, August 23, 2023 22:00 > > > > > To: users@cloudstack.apache.org <users@cloudstack.apache.org> > > > > > Subject: Query to Cloudstack API is degraded > > > > > > > > > > During the last weeks we have been experiencing a degrading > > > performance in API queries to Cloudstack, is there a procedure you > > > recommend to check the possible bottleneck?, we are checking database > > > indices. > > > > > > > > > > > > > > > BR, > > > > > > > > > > Ricardo Pertuz > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >