UAPI Create Database extremely slow (1000+ databases)

[yanosuke]

We have been having increasing issues when creating databases through the WHM UAPI.

An empty cPanel account can easily go through the database creation process in a second or two. However, a cPanel account with well over 1000 databases can seriously struggle here. If the process of creating a database, creating a database user, and assigning said user to the database goes through successfully, it can take roughly 30 seconds. That's only when the request doesn't timeout. Any subsequent requests to create a database will take far longer than 30 seconds. In fact, the API call for just creating a database did not finish after 60 seconds and our script timed out, directly after a 'successful' prior request.

It takes a long time to receive a response back from the API, even if a database gets created. Too long for any reasonable timeout setting.

Any other API calls return responses almost immediately, so I believe it's related to any processes involved during database creation, database user creation, and assignment of the user to the database. I have searched the forums and found another used that had a similar issue, however, that particular post was dated in 2013 without a solution.
We have gone through many different optimizations with IT, but have not been able to find a viable solution.

At the moment, creating new cPanel accounts and hosting databases there is not an option, as the 'host database' that houses client credentials is only accessible from the 'host cPanel account'.

Has anyone else had these issues and found a working solution?

 

[cPanelTJ]

Hi @yanosuke ,

1,000 databases within a single cPanel account is quite a large number. Could you help us understand your use case and what you are attempting to achieve? How many domains do you have under this cPanel account?

 

[yanosuke]

Sure, we are hosting a SaaS platform within a single cPanel account. When starting out, this was chosen because it would allow a new client to have their own subdomain (client.example.com) matching the main domain of the cPanel account (example.com). In addition to this, the new client would have their own database to store any information that's required for the services of that account. (There's a good 20 or 30 tables in that database to keep track of orders, inventory, expenses, etc for that client).

That current cPanel account only hosts one domain, the main domain at example.com, but has a separate subdomain for each of our clients. Because of this, the number of databases would match the number of subdomains very closely. Talking to IT, a suggestion was made to use 1 cPanel account per client, however, in my opinion this seems impractical and unrealistic. Especially considering the limit of cPanel accounts for the given license options.

In testing, and thanks to all the awesome help and support from our server's hosting company's IT staff, we have found that a second cPanel account does not suffer from the issues that the first account has due to the incredibly small number of databases on that second account. Although it was not advised as a solution, we will setting up a second cPanel account to host any new databases for the time being while a good solution can be formed.

Starting out as a very small SaaS (without the intention of becoming a commercial SaaS), we are now learning about certain limitations that were never of concern. Would there be a different setup that makes more sense, is more scalable, would still allow a central authentication database, and allow client subdomains for the main domain?

Thank you very much for your time.

 

[ankeshanand]

As You are describing the issue, It seems like the Server Load Averages are pretty much high. For best performance, it should be below 5 always. Creating too Many Database is never a Problem.
My Current Cloud Server has 32 Cores with Load Averages of 1.90 and It works like a Charm. Though, It has around 2200 Databases. In Your Cases, Try Limiting User Queries in MySQL or Use LVE Manager by CloudLinux to Limit MySQL Abusers. Alternatively, You can also try Upgrading to Larger Servers with 8-16 Cores(Intel i7 or Intel Xeon E3)

 

[cPRex]

@ankeshanand - in this case, all his databases are under one cPanel user, so the limits you mention likely would not apply.

 

[ankeshanand]

@ankeshanand - in this case, all his databases are under one cPanel user, so the limits you mention likely would not apply.

@cPRex LVE Manager has an Option MySQL Governor which can Limit MySQL Speed and MySQL IO for any or All Users. So, That will be effective!
and Limit max_user_connections from my.cnf could also be useful in this case. It would also limit MySQL User Connections which would directly impact the Load Averages greatly.

 

[cPRex]

For sure - if the issue was caused by load then it could be effective, but I'm wondering if the slowness is just due to the API call needing to read all the databases and whatnot to perform its actions.

 

[ankeshanand]

For sure - if the issue was caused by load then it could be effective, but I'm wondering if the slowness is just due to the API call needing to read all the databases and whatnot to perform its actions.

@cPRex
We have not heard back from him but this most probably looks like a Load Problem and My Answer also is based only for Load Averages Problem. In Case of WHMAPI Slowness problem, It might be Some DNS Problem(resolv.conf) (Maybe)

 

[yanosuke]

I am very sorry for the late response, we've been quite busy. I tend to agree with cPRex here and that the system needs to go through a lot for the one cPanel account. We have found that creating a new database on a second cPanel account processes nearly instantly, so this is the way to go. We have a fantastic IT team that's done numerous optimizations to the database server and the only thing that helped is using additional cPanel accounts. This will increase the cost as additional license are now a thing, but it does have it boons.