This is great read on why should use utf8mb4 and not just utf8:
https://mathiasbynens.be/notes/mysql-utf8mb4
This may not be practical for all applications. For example, the other day I was setting up am instance of Zabbix. The tables to create the schema tables did not explicitly state which charset and collation to use so it was accepting the MySQL server defaults which were utf8mb4 . However, this was preventing many of the tables to be created because the primary keys could not be added with utf8mb4 because of key length restrictions. I had to force the tables to use utf8 so that I could create the tables. I felt this was safe because we were not going to be writing utf8mb4 data into the Zabbix motioning tables.
Thursday, December 29, 2016
Monday, December 19, 2016
Kill the long running query with events
I have a lot of clients where I use a variation of the event below to auto kill long running queries. It generally works quite well but when a system gets over loaded it has trouble keeping up. Sometimes I see systems running 4,000 queries and this event will be killing 100 queries per second but it isn't able to kill them fast enough. The queries will end of running 2~3 minutes before they finally get killed. This event is suppose to kill them when they get over 1 minute.
Having people manually kill long running queries in MySQL is typically not feasible. I once had a client where their NOC team would kill every long running SELECT statement manually. That company could have installed this event and have it only kill SELECT statements.
In general long running SELECT queries should not be allowed on a master-primary MySQL server. Long running SELECT queries should be reserved for replica read-only servers. You may also want to terminate any long running query even if it is an update. When performing migrations that do alters or you know that the query will run longer than one minute, you should either disable the event or modify the event to not kill queries under the user name you use for deployments.
Referenced site:
http://www.markleith.co.uk/2011/05/31/finding-and-killing-long-running-innodb-transactions-with-events/
Here is a copy of the event from the above site:
Here is another way this can be done:
Having people manually kill long running queries in MySQL is typically not feasible. I once had a client where their NOC team would kill every long running SELECT statement manually. That company could have installed this event and have it only kill SELECT statements.
In general long running SELECT queries should not be allowed on a master-primary MySQL server. Long running SELECT queries should be reserved for replica read-only servers. You may also want to terminate any long running query even if it is an update. When performing migrations that do alters or you know that the query will run longer than one minute, you should either disable the event or modify the event to not kill queries under the user name you use for deployments.
Referenced site:
http://www.markleith.co.uk/2011/05/31/finding-and-killing-long-running-innodb-transactions-with-events/
Here is a copy of the event from the above site:
CREATE
DATABASE
IF
NOT
EXISTS myadmin;
USE myadmin;
DROP
TABLE
IF EXISTS kill_long_transactions_log;
CREATE
TABLE
kill_long_transactions_log (
id
INT
AUTO_INCREMENT
PRIMARY
KEY
,
ts
TIMESTAMP
DEFAULT
CURRENT_TIMESTAMP
,
thd
BIGINT
,
user
VARCHAR
(16),
host
VARCHAR
(64),
statement TEXT,
KEY
(ts,
user
),
KEY
(
user
),
KEY
(host)
) ENGINE = InnoDB;
DELIMITER $$
DROP
EVENT IF EXISTS kill_long_transactions$$
CREATE
EVENT kill_long_transactions
ON
SCHEDULE EVERY 10
SECOND
DO
BEGIN
DECLARE
max_transaction_time
INT
DEFAULT
10;
DECLARE
done
INT
DEFAULT
0;
DECLARE
killed_id
BIGINT
;
DECLARE
killed_user
VARCHAR
(16);
DECLARE
killed_host
VARCHAR
(64);
DECLARE
kill_stmt
VARCHAR
(20);
DECLARE
running_stmt TEXT;
DECLARE
long_transactions
CURSOR
FOR
SELECT
CONCAT(
'KILL '
, trx.trx_mysql_thread_id) kill_statement,
trx.trx_mysql_thread_id thd_id,
ps.
user
,
ps.host,
trx.trx_query
FROM
INFORMATION_SCHEMA.INNODB_TRX trx
JOIN
INFORMATION_SCHEMA.PROCESSLIST ps
ON
trx.trx_mysql_thread_id = ps.id
WHERE
(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(trx.trx_started)) > max_transaction_time
AND
user
!=
'system_user'
;
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done = 1;
OPEN
long_transactions;
kill_loop: LOOP
FETCH
long_transactions
INTO
kill_stmt, killed_id, killed_user, killed_host, running_stmt;
IF done
THEN
LEAVE kill_loop;
END
IF;
SET
@kill := kill_stmt;
PREPARE
stmt
FROM
@kill;
EXECUTE
stmt;
DEALLOCATE
PREPARE
stmt;
INSERT
INTO
kill_long_transactions_log (thd,
user
, host, statement)
VALUES
(killed_id, killed_user, killed_host, running_stmt);
END
LOOP;
CLOSE
long_transactions;
DELETE
FROM
kill_long_transactions_log
WHERE
ts < NOW() - INTERVAL 7
DAY
;
END
$$
DELIMITER ;
http://datacharmer.blogspot.com/2008/10/using-event-scheduler-to-purge-process.html
Subscribe to:
Posts (Atom)