0

I am currently testing a product with a couple of my friends before releasing to the public. There's now 10 of us who use the application daily. Yesterday, we noticed a performance error on the MySQL server, which currently runs on the smallest DigitalOcean droplet, with 1 GB Memory, 1 vCPU, 25 GB SSD Disk and 1 TB Transfer. Our plan is to increase this as soon as we go public, but we were hoping it would be sufficient for testing purposes.

The first thing I looked at was the MySQL error.log file. The output can be found here. This output was captured right after the server crashed for the first time, and I still don't know why.

I also had a look at the config file, which can be found on this link.

A couple more interesting things:

mysql> show global status like '%onn%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_connects                              | 19                  |
| Connection_errors_accept                      | 0                   |
| Connection_errors_internal                    | 0                   |
| Connection_errors_max_connections             | 0                   |
| Connection_errors_peer_address                | 0                   |
| Connection_errors_select                      | 0                   |
| Connection_errors_tcpwrap                     | 0                   |
| Connections                                   | 121                 |
| Locked_connects                               | 0                   |
| Max_used_connections                          | 10                  |
| Max_used_connections_time                     | 2019-06-27 16:52:29 |
| Performance_schema_session_connect_attrs_lost | 0                   |
| Ssl_client_connects                           | 0                   |
| Ssl_connect_renegotiates                      | 0                   |
| Ssl_finished_connects                         | 0                   |
| Threads_connected                             | 3                   |
+-----------------------------------------------+---------------------+
16 rows in set (0.01 sec)

Note: this was captured right after I restarted the server.

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1400  |
| table_open_cache           | 2000  |
| table_open_cache_instances | 16    |
+----------------------------+-------+
3 rows in set (0.01 sec)

Is there anything I can or should to do increase performance? Is there something I am doing wrong? This is the first time I am handling a MySQL server in combination with NodeJS, whereas my previous experience with MySQL was only with webhosting. If you need anything else, feel free to ask, I will be happy to provide anything I can find.

Per request from Wilson, I have uploaded a couple more files to pastebin which can be found here:

My specific concerns go towards, of course memory/cpu usage which caused the server to crash twice with almost no users (like 5 at most) and how I can prevent this and/or allocate the right resources to the right processes etc., but also about the timeout error in the error.log file. Of course if there are other suggestions to optimize my system, I would be happy to. I have this server run on a DigitalOcean droplet with 1 GB memory, 1 vCPU, 25 GB SSD Disk and 1 TB transfer - dedicated for MySQL so nothing else runs on there. Will upgrade to more memory/cpu/disk/transfer when we scale.

At this point I am a little afraid because I suspect a lot of changes would need to be made to make this small server be performant enough. Looking forward to your suggestions.

  • Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis. – Wilson Hauck Jun 28 at 23:46
  • Hi Wilson, I have adjusted a couple of things today and restarted the server. I will provide all requested data tomorrow. Also, I have now just installed MySQLTuner and it also recommends to recheck when the server has been up for +24hrs. Thanks for requesting/looking into it already! I'll keep you posted. – PennyWise Jun 29 at 0:04
0

First of all, you need to tweak your mysql database so that it doesn't use all your server's memory and dies. A good tool for that is using MySQLTuner, it can provide you with good recommendations.

What you should also do is to perform some load testing to see how your application will handle in a real world scenario. You can use several tools for testing, one of them is Jmeter from the Apache Foundation which is free. You can test for example what happens if 100 users concurrently visit and use your application, see what breaks and fix it.

0

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section,

innodb_io_capacity=1900  # from 200 to use additional SSD IOPS
innodb_buffer_pool_size=256M  # from 512M to support your present 1.2M of data tables
innodb_flush_method=O_DIRECT  # from 0 to typical Linux flush_method
query_cache_size=0  # from 128M to conserve RAM, QC is OFF, keep it OFF, please

From Linux Secure Shell Command prompt,

ulimit -n 65536     would increase Open Files above 1024 and is a dynamic request

To make this change persistent across OS restart, review this url

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

DO NOT SET your Open File limit to 500,000 or 1 Million as in the example at this url.

Please share your code that completes the CONNECT, processing, DISCONNECT (CLOSE) logic to analyze 'got an error reading communication' message cause.

Disclaimer: I am the content author of website mentioned in My Profile, Network profile where we have free Utility Scripts.

  • Hi Wilson, will edit/add these in my config file now. Will also check to increase the open files to 65536 right after. For my connect, processing and disconnect logic: I have a lot of node.js endpoints, but I'll add an example to pastebin which can be found here: pastebin.com/HsB3GmMB - as you can see, I use a connection pool and release every connection immediately after the query. – PennyWise Jul 1 at 10:06
  • Also, right now, MySQLTuner recommends the following: innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. Should I do that too? – PennyWise Jul 1 at 12:05
  • @PennyWise Please IGNORE the MST innodb_log_file_size suggestion. With additional users, you will need the 48M file size capacity and in time will likely expand this value, depending on additional workload. Let's get in touch via my contact info, please. – Wilson Hauck Jul 1 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.