Public bug reported:
mydumper 0.9.1
OS centos6.6 X86_64
mysql 5.6.25-log
mydumper -h $HOST -u $USER -p $PASSWORD -P $PORT -G -E -R -c -x
'^(mysql|db1|db2)'
mysql schema is MyISAM
Describe
Began to execute regular backup script, after a few minutes application error
"Cannot get a connection,pool error Timeout or idle object", and execute
command show processlist have a lot of “Waiting for a table flush” and several
“Waiting for global read”, check the backup process
56793, 39481: backup 127.0.0.1 NULL Query 3671 Waiting for table flush flush
TABLES WITH READ LOCK
Apparently are blocked by long queries,use cat list.txt |awk '{if($6>3671)
print $0}',grep larger than 3671
46109 app1 *.*.*.*:25545 db_name Query 13626 Sending data /*
dynamic native SQL query */ SELECT ...
executed 13626/3600=3.7h,from the process id and the status, that is blocked by
this query
Question:Why doesn't terminate the backup process before the backup?
Normal logic is to show processlist check whether there is a long
query, if you have it aborting dump to avoids stalling whole server with
flush.
source code:
/* We check SHOW PROCESSLIST, and if there're queries
larger than preset value, we terminate the process.
This avoids stalling whole server with flush */
if (mysql_query(conn, "SHOW PROCESSLIST")) {
g_warning("Could not check PROCESSLIST, no long query guard
enabled: %s", mysql_error(conn));
} else {
MYSQL_RES *res = mysql_store_result(conn);
MYSQL_ROW row;
/* Just in case PROCESSLIST output column order changes */
MYSQL_FIELD *fields = mysql_fetch_fields(res);
guint i;
int tcol=-1, ccol=-1, icol=-1;
for(i=0; i<mysql_num_fields(res); i++) {
if (!strcasecmp(fields[i].name,"Command")) ccol=i;
else if (!strcasecmp(fields[i].name,"Time")) tcol=i;
else if (!strcasecmp(fields[i].name,"Id")) icol=i;
}
if ((tcol < 0) || (ccol < 0) || (icol < 0)) {
g_critical("Error obtaining information from
processlist");
exit(EXIT_FAILURE);
}
while ((row=mysql_fetch_row(res))) {
if (row[ccol] && strcmp(row[ccol],"Query"))
continue;
if (row[tcol] && atoi(row[tcol])>longquery) {
if (killqueries) {
if
(mysql_query(conn,p3=g_strdup_printf("KILL %lu",atol(row[icol]))))
g_warning("Could not KILL slow
query: %s",mysql_error(conn));
else
g_warning("Killed a query that
was running for %ss",row[tcol]);
g_free(p3);
} else {
g_critical("There are queries in
PROCESSLIST running longer than %us, aborting dump,\n\t"
"use --long-query-guard to
change the guard value, kill queries (--kill-long-queries) or use \n\tdifferent
server for dump", longquery);
exit(EXIT_FAILURE);
}
}
}
mysql_free_result(res);
}
** Affects: mydumper
Importance: Undecided
Status: New
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1587390
Title:
mydumper occurred Waiting for table flush
To manage notifications about this bug go to:
https://bugs.launchpad.net/mydumper/+bug/1587390/+subscriptions
--
Mailing list: https://launchpad.net/~enterprise-support
Post to : [email protected]
Unsubscribe : https://launchpad.net/~enterprise-support
More help : https://help.launchpad.net/ListHelp
