Lutz Posted January 26, 2020 Share Posted January 26, 2020 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? Link to comment Share on other sites More sharing options...
flydev Posted January 26, 2020 Share Posted January 26, 2020 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 ?) 1 Link to comment Share on other sites More sharing options...
Lutz Posted January 26, 2020 Author Share Posted January 26, 2020 @flydev ?? Thank you very much. Of course, host an port number are different, but the settings are triple checked. Link to comment Share on other sites More sharing options...
elabx Posted January 26, 2020 Share Posted January 26, 2020 I've seen there are some issues with MySQL 8, be aware of that: https://github.com/processwire/processwire-issues/issues/973 1 Link to comment Share on other sites More sharing options...
dragan Posted January 26, 2020 Share Posted January 26, 2020 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? 1 Link to comment Share on other sites More sharing options...
Lutz Posted January 26, 2020 Author Share Posted January 26, 2020 Thanks @dragan and @elabx. I fixed the timeout error while testing with a non-PW script, will begin testing with ProcessWire soon. Link to comment Share on other sites More sharing options...
Lutz Posted January 26, 2020 Author Share Posted January 26, 2020 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. Link to comment Share on other sites More sharing options...
kongondo Posted July 9, 2020 Share Posted July 9, 2020 Cross referencing a similar topic with, possibly, an answer to your question @Lutz 1 Link to comment Share on other sites More sharing options...
Lutz Posted July 9, 2020 Author Share Posted July 9, 2020 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. 1 Link to comment Share on other sites More sharing options...
Melakh Posted July 10, 2020 Share Posted July 10, 2020 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. Link to comment Share on other sites More sharing options...
Jonathan Lahijani Posted November 25, 2020 Share Posted November 25, 2020 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. Link to comment Share on other sites More sharing options...
adrian Posted November 25, 2020 Share Posted November 25, 2020 @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? Link to comment Share on other sites More sharing options...
RyanJ Posted February 25, 2021 Share Posted February 25, 2021 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 Link to comment Share on other sites More sharing options...
RyanJ Posted February 25, 2021 Share Posted February 25, 2021 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now