SQL error "too many active connections"

[Knubbi]

I have managed server hosting with 1&1 and Processwire error logs throws a lot of SQL errors:

Quote

Exception: SQLSTATE[HY000] [1203] User o210991 already has more than 'max_user_connections' active connections (in /homepages/5/d28717428/htdocs/bm/pw/wire/core/ProcessWire.php line 457

1&1 is not willing to allow more connections in the MySQL configuration which would force us to leave 1&1 after more than 20 years.

Questions:

A) Who/What is user "o210991" in above error message? All errors are caused by the same "user". We do not use any user accounts (except the admin/web editor).

B) Is there any way in Processwire to reduce the number of connections?

C) If not, do you have any recommendation for a fast German/EU based hosting suited for Processwire?

Your help would be very much appreciated.

[elabx]

1 minute ago, Knubbi said:

B) Is there any way in Processwire to reduce the number of connections?

Have you tried ProCache?? Depending on the type of website (is it mostly viewes by guests?) could work wonders because it sets up Apache to grab cache files from the system instead of hitting the database (thus saving that connection). 

2 minutes ago, Knubbi said:

A) Who/What is user "o210991" in above error message?

That seems to be the database user ProcessWire uses to make it's queries. 

And sorry but I've got no recommendations for any european servers, way out of my geography :D I do remember seeing some forum threads about hosting solutions, try to search for that here on the forums. 

[Knubbi]

Thanks for your quick reply. Yes, we are using ProCache for many (but not all) pages. The website is visited by guests "only" (only very few editor accesses).

[Lutz]

@Knubbi Is it a MySQL 5.5 or a 5.7 database? If you are using MySQL 5.7, you could try to migrate the tables to another MySQL 5.7 database. (If you create a new database, chances seems to be high there that it's created on another database server.)

We also have problems with those errors, which, however, are not (or not only) caused by ProcessWire. (I've seen such errors in an Dev environment when we had just two users online... Have a look at some MySQL server stats, you very likely will find some signs of a heavily overloaded server. Contrary to their information about dedicated ressources regarding managed servers, Ionos (1&1) uses MySQL 5.7 database servers for several accounts in parallel, not just one for your managed server account.)

If you are still using MyISAM, it also should be save to migrate to an MySQL 5.5 database instead. Whenever I compared server statistics, MySQL 5.5 servers at Ionos (1&1) had less problems than their MySQL 5.7 servers.

[Knubbi]

Please find status screenshot of PHPMyAdmin. I am clueless about the details. Can you see any showstopping issue?

 

[Lutz]

6 k failed attempts to connect to the MySQL server – this looks like stats of a MySQL 5.7 server, I know those numbers very well, and I think Ionos has a problem with their server configuration or with their entire MySQL 5.7 server concept.
I checked stats of Ionos MySQL 5.5 servers we use, for ProcessWire as well, 0 failed attempts. Maybe you should try to use MySQL 5.5 there, to get a fast workaround, but I think you also should inform the server support, because of course you should be able to use MySQL 5.7. And yes, it's good to know that I'm not the only one who has serious problems with their MySQL 5.7 servers, and that I'm not the only one who addresses this.

[wbmnfktr]

Seeing those numbers, stats and graphs your website is either a super-high-traffic one or the service you are paying for is more or less useless.

6+ days uptime, 1.7TB traffic, 5.6m connections - what are the numbers for website traffic, like visitors daily/weekly/monthly? What kind of website is that?

Are you able to check the server logs? Maybe there is someone or something pushing your server with fake requests, like scanning for security issues, sub-folders, default paths and such things.

If your project receives tons of traffic each and every day plus some peaks now and then - you really should consider switching hosting companies or consider a CDN (Cloudflare, KeyCDN - but be careful with GDPR-compliance).
Get your numbers (total traffic and page impressions for the last 12 months, strip it down to a monthly average) and talk to companies like Hetzner or Mittwald.

When 50-100k visitors per month on a cheap US hosting for $3/month are possible, your server should be able to handle much more than that.

 

[Lutz]

@wbmnfktr These are server stats, not just stats for the database in question. Ionos uses their MySQL 5.7 servers for many different databases in parallel. ?

[dragan]

@Knubbi I can't believe anyone is still considering 1&1 as a viable hosting option for... anything really.

On 12/16/2019 at 6:32 PM, Knubbi said:

1&1 is not willing to allow more connections in the MySQL configuration

According to this, you should be able to do so.

And according to this, you should grab your things and run for the hills.

I can't recommend any hoster in Germany (I live in Switzerland, and all clients insist on a .ch hosting company), but I'm sure you'll find something more professional, serious and stable than what you currently have. In the long run, the extra bucks you spend will be money well-spent. (but of course, there are black sheep everywhere in the industry, not just in the lower-price area)

[wbmnfktr]

1 hour ago, Lutz said:

These are server stats, not just stats for the database in question. Ionos uses their MySQL 5.7 servers for many different databases in parallel. ?

That should be illegal.

As @dragan said: you should look for a way more solid hosting company for your projects.

[Lutz]

Sorry, I don't agree. In particular, I'm not happy about the direction this thread is taking. A ProcessWire user needed help, I tried to give a hint regarding a quick fix and some information about special difficulties with the new Ionos MySQL 5.7 servers. Was it helpful? I don't know. However, is it helpful to denigrate one of the biggest hosting companies in Europe, with just those facile arguments? whtop e.g. shows some anonymous critics of Ionos in the US, not Germany/Europe, and I must say I'm not impressed at all.

To be clear, I'm frustrated about some problems I have with Ionos myself, but I think we should try to speak exactly about what's going wrong, about errors and their reasons, and how we could fix them, if it's possible. What I mean is, I don't like those complaints like Ionos is the worst hosting company ever or WordPress is hell on earth.

Knubbi has problems with a MySQL 5.7 server, which he uses as part of a managed server account. Such accounts gives you access to a fast, dedicated server, but without root access. You can use them like you would use a shared hosting account, but without sharing recources with other clients, there's just one account on one machine. If you use a MySQL 5.5 database, this also is true regarding the databases, the MySQL host is localhost and those database servers rarely have problems. However, Ionos was very late with offering MySQL 5.7 servers for those hosting accounts, and because of the side effects a complete migration of all databases to MySQL 5.7 would have, they decided to leave existing databases untouched, to offer still the creation of new MySQL 5.5 databases and to give their hosting account clients just an option to create MySQL 5.7 databases, if they needed them. And these MySQL 5.7 databases are, unlike the 5.5 ones, centrally managed, on other hosts.

So, I agree that this is a problematic decision, with regard to that it's no longer completely true to claim the resources of those accounts dedicated, but I think you can understand the reasons behind this decision, and they give you a warning about this fact before you can create a MySQL 5.7 database (instead of a MySQL 5.5 one). The real problem here is that they haven't made it so far to achieve a stability of those MySQL 5.7 servers that would be comparable to that of the 5.5 ones. I try to address this problem to this company, because I think it would be helpful for many customers like Knubbi when they find a working solution, and for some of my own customers as well.

Would he be better off using another hosting company? Well, perhaps. But I don't think this is as clear as ist seems, neither regarding every hosting client of Ionos in general nor regarding users of those managed server accounts in particular. Ionos isn't one of those little companies with just a small data center or some borrowed rack space, they have a big, well-developed infrastructure, and one of the advantages of using those managed hosting accounts is to get a reliable DDoS protection for free. Maybe you never need something like this, but I can tell you that there can be very good reasons to choose such a managed server account. Whenever you were under fire with a mission-critical website you are responsible for, you know what I mean.

Don't compare those accounts with shared hosting accounts. If he had well thought out reasons to use a Ionos managed server, he probably has to think about using a dedicated server or a cloud server instead. If his Internet presence requires mission-critical uptime, this means that he has to be experienced enough (and has to have enough time!) to make all the server admin work himself, or to hire two and a half DevOps to manage the servers. Or, of course, he has to find a more reliable and big enough hosting company with managed server accounts and without the problems Ionos obviously have.

@Knubbi  Well, if you will find such a company with comporable services, including some scalability and DDoS protection, please let me know. ?

[Knubbi]

Thanks for all the input.

@dragan: Managed servers don't provide root access -> max_connections can't be changed by user and IONOS refuse to increase it.

@Lutz: Thanks for all the insight. Indeed, issues seem to have started with changing from MySQL 5.5 -> 5.7.

I was not aware of the drastic underlying change you explained and I somehow feel being scammed by IONOS.

I pay an hefty extra for a dedicated server and they still share a core component. Apparently, given the data load, my particular metal seems to be shared with Amazon's frontend.

By the way, the "warning" about shared MySQL 5.7 is quite misleading. It says, "Please note that MySQL 5.7 databases are centrally managed and limited to 1 GB memory."

Centrally "managed" does not say that they share a server.

On the other hand, I hesitate to go back to 5.5 as 5.7 was advertised as a signififcant performance improvement.

Any idea, who this strange Processwire user in above errors could be? Is this a ProCache agent walking through the pages?

[pwired]

Quote

1&1 and SQL error "too many active connections"

FYI,
I have a client who sells a cash register app and once used 1&1 database hosting to manage his app and his client licenses.
He had the same database problem: sql error too many active connections. We always stayed under 150 active connections
so the sql error should not raise. Upgrading ram and updating the my.cnf file did not help. The problem was never solved
by 1&1 support and they even recommended us to leave 1&1. They didn't like their database used by the cash register app.
We moved to another hosting company and the problem was simply solved.

[Knubbi]

Just now, pwired said:

FYI,
I have a client who sells a cash register app and once used 1&1 database hosting to manage his app and his client licenses.
He had the same database problem: sql error too many active connections. We always stayed under 150 active connections
so the sql error should not raise. Upgrading ram and updating the my.cnf file did not help. The problem was never solved
by 1&1 support and they even recommended us to leave 1&1. They didn't like their database used by the cash register app.
We moved to another hosting company and the problem was simply solved.

Sad to hear. We are with 1&1/IONOS for 20 years without the slightest issue ever - until we switched to Processwire.

Is there anything within the reach of Processwire to limit the number of users?

Any recommendation for a solid Processwire suitable hosting? No reseller, no offshore, well managed and based in Germany (because GDPR)?

[Lutz]

@Knubbi The number of active connections refers to the server, not the individual database. Call the tech support and say that you need a written answer. I don't think that the issue originated from your switch to ProcessWire, but from switching to MySQL 5.7.

As I said, if you are using MyISAM, it should be safe to migrate to MySQL 5.5 for a quick fix. You told us that there are no user accounts despite that of the admin, therefore it can be assumed that there's a lot more read access than write access. If so, I don't think that you would have performance issues with MySQL 5.5. Under such conditions there's little benefit through using InnoDB.

Edit: Sorry, I had a second look on your first posting, I think I mixed up different things here... I had an issue with the number of concurrent connections on overloades Ionos MySQL 5.7 servers, but maybe in your case there's another error involved. Is your php error log activated and is there any other error logged? Do you have Tracy installed and can you check your code with debug mode on?