MySQL / Web Server Bottleneck

aimee

11/28/22, 8:48 PM

I’m trying to determine a MySQL / Web server bottleneck.

I have three servers. A Web server running Nginx, a remote MySQL server with my Wordpress DB and another remote MySQL server storing our data.

The bottleneck I’m trying to find is between my second MySQL server storing our data and my Web server. We have a page that has three DataTables on it (three separate queries). It’s loading very slowly, if it does all. Occasionally I’ll get a gateway time out error.

I don’t think the queries themselves are the issue. From DataGrip all three average between 200-500ms. Currently the queries aren’t indexed as I’ve been told the plugin cannot take advantage of indexes, but I might try anyways.

Hardware and Setup:

My MySQL server is an AWS R6G.Large, 2 cores and 16gb ram, SSD of 150 IOPS and 128 MB throughput. innodb_page_size is 32, buffer_pool_size is 11000M, innodb_buffer_pool_instances is 10 and innodb_log_file_size is 1G Web server is an AWS C6G.Xlarge, 4 cores and 8gb ram, SSD of 150 IOPS and 128 MB throughput. Uses FPM and Opcache.

I’ve tried monitoring using TOP on both servers, but to be honest I’m not sure I have knowledge to properly utilize the information.

I’d really like to determine if it’s hardware or software, somehow, and if it’s hardware is there a way to isolate? I have no problem increasing hardware if that’s actually the problem.

I’m not sure if this is allowed on Stack / ServerFault, but I figure it might be easier to know what’s going on if I record my screen with TOP running on both servers. I added a video to my public Google Drive. The video has both my MySQL server (on the top) and Web server (Nginx, on the bottom). What I did was load the page (3sec mark in video) and recorded the outcome. The video is 1:05, which how long it took for the last table to appear. The video was recorded while my site was in maintenance, so no other IP / traffic could reach either server.

My google drive link:

https://drive.google.com/drive/folders/1NtdE1Z4875i1Xx2Wy2EXGgknt9yuY1IN?usp=sharing

Hopefully someone can help.

Aimee

0 + 5

mysql

nginx

Michael Hampton

11/28/22, 8:52 PM

The video is so low quality that it's almost impossible to make out anything. But it seems that mysqld is running at 100% CPU for the entire duration. This suggests that your queries are indeed the issue. Run mysqltuner.pl and post the output here.

aimee

11/28/22, 8:56 PM

@MichaelHampton, Weird. The quality is good on my desktop and mobile. I’m not familiar with mysqltuner. I’ll google it. Thanks

djdomi

11/29/22, 4:24 AM

@MichaelHampton Should I support you to fix your Internet? XD Nope sorry, the Quality is great, seems to google freaked your eyes. However, I think you mean [this GitHub project](https://github.com/major/MySQLTuner-perl)

djdomi

11/29/22, 4:25 AM

@aimee i think, you could just post the same with just a Text-Message because it would make it easier

Rick James

11/30/22, 7:20 PM

Are the servers _physically_ distant from each other? (I am looking for network latency.)

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: MySQL / Web Server Bottleneck

TH: คอขวดของ MySQL / เว็บเซิร์ฟเวอร์

RO: Gâtul de strângere a serverului MySQL/Web

RU: Узкое место MySQL/веб-сервера

VI: Nút thắt cổ chai MySQL / Máy chủ Web

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.