Thank you guys for help !
@Peter
01/21/2020 07:19AM
We already checked our queries using slow_log, we analyzed the traffic during few days and I believe that we fixed not optimal queries already.
We also have a script to monitor memory, he activate another one with tcpdump on board to save all query when we have memory jump.
#!/bin/bash
/usr/sbin/tcpdump -i network_interface_number -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
{
if (defined $q) { print "$q\n"; }
$q=$_;
} else {
$_ =~ s/^[ \t]+//; $q.=" $_";
}
}'
so we use that to view query in meanwhile ( when slow_log is off )
But of course we will check once again using slow_log like you suggest and I will let you know.
But I wondering about the numbers of queries after every mysql restart.
Whenever we did a restart i see on the graph a very huge numbers of queries, later we are back to normal numbers.
Please take a look on the weekly graph here ->
https://prnt.sc/rarsjn
This jump is our restart.
Here is monthly graph, where we can see more like this ->
https://prntscr.com/raswht
each one of them was after mysql restart.
and this how we are look on a daily basis with numbers of queries ->
https://prntscr.com/rat0d8
The question if that this big jump is normal behavior ? Cuz maybe this is the reason why we have such big numbers in statistic.
current view with ram usage ->
https://prntscr.com/rat2fc
In last 24h everything looks stable, which is also strange, cuz code and traffic looks like usually.
I didn't mentioned before, but we also make mysqldump once in a hour using command like below:
"mysqldump --single-transaction --routines --max_allowed_packet=30M --quick dbName >destinationPath.sql"
@Edwin
We used 100% innoDB in the past, but we switch few tables to MyISAM because of issue with semaphore.
He locked the tables for to long time and causing delay on production server - myISAM resolved that.
But maybe we should back to this topic and figure out different solution, but you think that will help in this case ?
Regarding upgrading mysql I planned to do that, this analyze looks very nice !