Exception SQLSTATE[HY000] [2002], SSL encryped database connections

[Lutz 31]

I'm testing DigitalOcean's database cluster service, MySQL server version is 8.0.17.

The tables were migrated from a MySQL 5.7 database, the database user is altered to support mysql_native_password (instead of caching_sha2_password).

In config.php, $config->dbOptions is set to an array with
\PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-certificate.crt'.

I get the following error message when I try to access the website:

Error: Exception: SQLSTATE[HY000] [2002] Connection timed out (in /path/to/wire/core/ProcessWire.php line 460).

Does somebody use ProcessWire with such a configuration (MySQL 8, SSL) or has any idea how the problem could be solved?

[flydev 👊🏻 1,859]

Quote

Connection timed out

I think that your connection settings are wrong. You should triple check it. 

I don't know how the DigitalOcean cluster work, but I must assume that the host is different that localhost or 127.0.0.1. Also, are you sure you configured the right port number (if different in a cluster config) ?

The error message must indicate that something is misconfigured. In the case it was your credentials that was wrong, you will must get a Connection Refused error not a timeout. (not sure how to formulate this last sentence 😅)

 

[Lutz 31]

@flydev 👊🏻 Thank you very much. Of course, host an port number are different, but the settings are triple checked.

[elabx 865]

I've seen there are some issues with MySQL 8, be aware of that: 

https://github.com/processwire/processwire-issues/issues/973

[dragan 1,729]

19 hours ago, Lutz said:

I'm testing DigitalOcean's database cluster service

Did you try to install the same mySQL version elsewhere (e.g. locally) and see how it goes? You could at least narrow down the issue (mySQL, or some DO-specific settings). Did you try access the DB directly?

[Lutz 31]

Thanks @dragan and @elabx. I fixed the timeout error while testing with a non-PW script, will begin testing with ProcessWire soon.

[Lutz 31]

Tested with ProcessWire 3.0.149, no errors so far. However, it's terrible slow, especially admin, worst when using ListerPro. For example, there's a page with 5 children, the lister shows 5 results, execution time > 5,000 ms. If you open the config of that lister, execution time is > 8,900 ms. Go to Pages (pages tree), execution time > 2,000 ms (for just a few test pages).

Cluster insights shows load average 1-minute peaks above 3 (1 vCPU cluster), for just one user, a few clicks. 5-minute peaks are around 0.9.

CPU usage is ok./low (~ 7.5 %), memory usage ~ 80 % (1 GB RAM). Of course, this combination (PW + DO database cluster 1 vCPU/1 GB RAM) cannot be used at the moment. I try to continue researching.

[kongondo 7,381]

Cross referencing a similar topic with, possibly, an answer to your question @Lutz

 

[Lutz 31]

Hi @kongondo, thanks for referencing this newer topic. Connection problems can be solved, but these are only the tip of the iceberg, I fear. I gave up a bigger PW project because of the performance problems we got with MySQL 8.

The main reason for these problems seems to be the MySQL Server Team's decision to remove support for the query cache in MySQL 8. To quote Matt Lord: "MySQL 8.0 will not support query cache, and users upgrading will be encouraged to use either Server-side Query Rewrite or ProxySQL as a man-in-the-middle cache." (https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/)

The vast majority of the PW users will not be able to use such configurations, with server-side Query Rewrite or ProxySQL, and i doubt that such a configuration could be an equivalent replacement in any case, but I hadn't an opportunity to test it.

[Melakh 0]

Interesting to read your reply, per the other thread I just managed to get PW working with SSL connection to MySQL 8 and have seen the performance absolutely tank. I'm running a tiny site though, so would not expect to see such a shift from lack of query cache, wondering if there might be anything else going on.

[Jonathan Lahijani 1,161]

For what it's worth, I have the following working without any issues:

• website on a fresh digital ocean ubuntu 20.04 droplet with lamp stack (installed using sudo tasksel install lamp-server)
  • processwire 3.0.168
  • php 7.4.3
  • mysql 8.0.22
• database on digital ocean database cluster -- set this up just now
  • specs: 1 GB RAM / 1vCPU / 10 GB Disk / Primary only / SFO2 - MySQL 8
  • mysql 8.x (8.0.20 based on the logs)
  • (connecting using the default 'doadmin' user account, which should be using caching_sha2_password, not mysql_native_password); no connection issues

...

I'm clicking around going to various pages and it doesn't feel "fast".  Not slow, but not fast.

I then upgraded to the 4gb/2cpu database cluster and it's still pretty slow.  I just switched back to the local mysql server and it's way faster.

Keep in mind both my droplet and my db cluster were in the same region (SFO2).  Looks like I won't be using Digital Ocean's database cluster at least until it's faster or I figure out how to make it faster (if it's possible).  Not sure why it's so much slower.

Related: https://bensmann.no/running-wordpress-with-digital-oceans-managed-mysql-databases/

Quote

Increased latency

This perhaps, is the most noteworthy one. On my site, I generally experienced 100 to 200ms TTFB (Time to first byte) depending on which page you load. This means, from the moment a request was sendt from the browser, it would take somewhere around that time for the first information to return to the browser. However, by moving MySQL of my server this increased, sometimes by up to 150ms, even though the MySQL cluster was in the same region. Using the private network worsened the latency even further.

But one thing is critical to keep in mind. This is simply the cost of a having a setup that is far more scaleable. Not only that, once your site gets hammered with requests, the database cluster will most likely perform and scale better. Especially when caching options such as Varnish aren’t an alternative.

 

[adrian 12,069]

@Jonathan Lahijani - are they on the same VPC Network? I am a big DO user, but haven't ever used their database cluster so I don't have any ideas about the speed but just wondered if maybe that would help. Actually, maybe it's not possible for a database cluster to be on the same VPC network?

[RyanJ 78]

I am also seeing serious performance issues when migrating to MySQL 8. I am not sure if this is particularly related to Digital Ocean. I think @Lutz has solid concerns. 

Just some stats on my testing. Two servers, both running on nignx. Box 1 is on a shared hosting environment and Box 2 is on DO. Using TRACY, you can see the difference below in execution time on same pages. The sites are replicas of each other. The execution time is from a front-end page that is using the matrix repeater to render layout items. Even a page with one matrix repeater has as significant difference Box 1 (440.8ms) - Box 2 (1039.4ms). 

Interestingly enough, pages not using the matrix are faster on both frontend and backend on Box 2 (125.6ms) - Box 1 (249.5ms). Admin pages with matrix items are also significantly slower on load.

Box 1 - Frontend
PHP 7.3, MySQL 5.7

Box 2 - Frontend
PHP 7.4, MySQL 8

Box 1 - Admin

Box 2 - Admin

[RyanJ 78]

An update to this. As @adrian suggested, I tried out one of the db clusters at DO.

I updated the storage engine from MyISAM (I could not import the MyISAM version anyway) to innoDB and did a fresh import.  From the image above you can compare. 

Box 2 - Frontend
PHP 7.4, MySQL 8

Box 2 - Admin

 

This I can work with. Cheers!