MySQL Forums
Forum List  »  Performance

Re: High memory used by MySQL
Posted by: Damian Kolany
Date: March 03, 2020 06:12AM

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 !

Options: ReplyQuote


Subject
Views
Written By
Posted
704
February 12, 2020 02:45AM
419
February 12, 2020 11:41AM
351
February 13, 2020 09:24AM
328
February 13, 2020 11:49AM
304
February 14, 2020 09:05AM
301
February 14, 2020 11:06AM
296
March 02, 2020 04:12AM
257
March 02, 2020 04:17PM
245
March 02, 2020 08:35PM
276
March 02, 2020 08:36PM
Re: High memory used by MySQL
258
March 03, 2020 06:12AM
231
March 03, 2020 11:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.