0

let me explain the history of this issue,

I am running Centos 7 on a dedicated server, when I installed it for the first time I did not make any partition because I do not know very well how to do it, and actually I think is not possible because I can not do any backup of the entire server in other machine.

So this is my disk:

[root@ns527667 ~]# lsblk
NAME   MAJ:MIN RM    SIZE RO TYPE MOUNTPOINT
sda      8:0    0    1.8T  0 disk 
└─sda1   8:1    0 1004.5K  0 part 
└─sda2   8:2    0   19.5G  0 part /
└─sda3   8:3    0    1.8T  0 part /home
└─sda4   8:4    0    511M  0 part [SWAP]

I installed MySQL server on / partition, but with the time all my DBs grew significantly, so I decided to move them to the /home partition, was nice. I forgot the space issue then. Before the move I was able to connect to my DB using localhost as DB host, but it starting to have a isssue with the connection, sometimes my apps lost connection, so I changed the DB host to 127.0.0.1 at that moment it fix the problem, but now sometimes it lost connection again. And some times the response time between query and result its high (0-5000ms aprox).

I run MySQLTunner and it suggested me to change some variables so I changed a lot of variables like:

SET GLOBAL max_connections = 500;
SET @@GLOBAL.long_query_time = 1;
SET GLOBAL tmp_table_size = 128000000;
SET GLOBAL max_heap_table_size = 128000000;
SET GLOBAL key_buffer_size = 32000000;
SET GLOBAL thread_cache_size = 20;
SET GLOBAL max_allowed_packet = 8000000;

Actually my connections rounds 150-200

I optimized or at least I think I did it on all my InnoDB

mysqlcheck --user=root --password="pass" -o --all-databases

This is my /etc/my.cnf

[mysqld]
datadir=/home/var/lib/mysql
socket=/home/var/lib/mysql/mysql.sock
interactive_timeout=86400
wait_timeout=86400
max_connections=1000
innodb_buffer_pool_size=256M
skip-name-resolve
slow-query-log = 1
slow-query-log-file = /home/var/lib/mysql/mysql-slow.log
long_query_time = 2
bind-address="127.0.0.1"
port=3306

symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/home/var/lib/mysql/mysql.sock
port=3306

I am using various java apps and they works very well in my personal machine, but in the server sometimes lost the connection with errors like this:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

or

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Now I do not know what to do..

Michael Hampton
  • 237,123
  • 42
  • 477
  • 940
Martini002
  • 11
  • What else runs on this server? How often does something connect to mysql? You said this happens even when you use the loopback. Have you looked at `netstat -aeenp` (as root) around the time this occurs? If so, how many sockets are open, closing, time wait, etc? Also, anything in the slow query logs? – Aaron Mar 06 '17 at 16:35
  • This is a Minecraft network with 6 servers, and a lot of plugins doing queries, here are the sockets [link](https://hastebin.com/tinecaruxu.swift) this issue occurs randomly I can not catch exactly when this going to happen. – Martini002 Mar 06 '17 at 16:43
  • I would look at the connections around the time you get the timeouts or high latency. Sar (if installed) will have some historical data, minus the time wait and close wait counts. You can also export the sar data into a tool called kSar to get visuals of most of the stats. – Aaron Mar 06 '17 at 16:59
  • This is most likely due to a performance issue with your MySQL server. It is simply getting too much traffic for it to handle and therefore it stops responsing to some requests. You might need to look into optimizing queries, optimizing database structures and if that doesn't lead into improvments, then you need to find a better performing setup for your MySQL. – Tero Kilkanen Mar 06 '17 at 18:05
  • My hardware resources looks good, I have CPU and RAM enough, how can I see the consumption of MySQL? actually my CPU is at 50% and RAM 70% – Martini002 Mar 06 '17 at 20:21
  • I will install Sar and give you the records about these times shortly – Martini002 Mar 06 '17 at 20:41
  • Aaron can you tell me which command can I use with sar for give you the info about connections? I tried severals but I think is not enough – Martini002 Mar 06 '17 at 21:22

0 Answers0