Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
I rarely need to go down the list more than 3 items to make a dramatic improvement.
If you are weak on knowing what indexes to add, don'...
This below worked for me:
WHERE table_schema = 'mydatabase'
and COLUMN_NAME = 'mycolumn'
AND table_name = 'mytable' LIMIT 1;
set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable` ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
'select \'Column Exists\' status');...
This nice post explains it well.
The deadlocks occur due to gap locking done by mysql. There are
several reasons for gap locking, and in this particular case, it has
to do with preserving a unique key constraint on an index. The
situation presents itself to us this way: There is a unique key
constraint on a column and we are doing an insert. Mysql ...
The only way to avoid such deadlocks is to make sure that all data modifying statements process the rows in the same order, e.g. in ORDER BY uid, job_id order.
With the INSERT statements that can be easily done, but with the UPDATE statements it depends on the execution plan the database uses. Perhaps you can do something by using certain indexes and ...
For performance, don't try to do it all in one statement.
Avoid non-sargable expressions, such as (expires + INTERVAL 5 DAY) >= NOW(); instead say (expires >= NOW() - INTERVAL 5 DAY)
Probably it would be better to do 3 queries to test for the 3 possible dups (bundle_id, etc).
If it would be OK to pick the newest expired bundle_id dup without seeing ...
Instead of doing 3 separate joins, you could accomplish the same thing by simply using an OR clause (the execution time seems to be ~1-2ms):
SELECT DISTINCT u.id
FROM user u
JOIN translation_revision t1
ON u.id = t1.translator_id OR u.id = t1.proofreader_id OR u.id = t1.reviewer_id
GROUP BY u.id
Other ideas would be to denormalize your data ...
Mine is fast, at 1-2 milliseconds (tested across various versions between 5.5 and 8.0, plus MariaDB):
FROM user u
WHERE EXISTS ( SELECT 1 FROM translation_revision t1
WHERE u.id = t1.translator_id )
OR EXISTS ( SELECT 1 FROM translation_revision t2
WHERE u.id = t2.proofreader_id )
OR EXISTS ( SELECT 1 FROM ...
The mysql client has the init-command option, which does what you want, I think.
From the manual:
SQL statement to execute after connecting to the server. If
auto-reconnect is enabled, the statement is executed again after
Don't use autocommit=0, you might have forgotten to do COMMIT.
Try SHOW PROCESSLIST; again. But this time look for the non-system process with the highest "Time". It may indicate the 'user@host' that forgot to COMMIT (or whatever).
Alas, the slowlog does not show a query until it finishes.
"Sleep" can occur for an open transaction:
SELECT ... FOR ...
This query may assist in your search for locking and blocking ids.
# IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh
# if this helps you, please email SHAREABLE comments to email@example.com
# From article at
# 20191227 tested on ...
The error is related to the difference between Innodb definition and the frm file definition.
It probably occurred because of a crash during an ALTER TABLE or similar statement at some point.
To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb ...
'column1' 'column2' 'column3' is different than 'column1,column2,column3'
So, you must compare the whole string:
IF (col_names = 'column1,column2,column3')
If you want to compare each variable separately, then you must pass them separate in your procedure call:
It is not surprising that temp tables would come into play. Why ???
According to the MySQL 5.5 Documentation on join_buffer_size (Italics mine):
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. ...
Mysql (community) Version 8.0.17-1.sles12 - OpenSUSE tumbleweed 2019.10.02
mysql> SET GLOBAL expire_logs_days = 4;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds
cannot be used together. Please use binlog_expire_logs_seconds to set the expire
time (expire_logs_days is deprecated)
The queries are bad
Then they need fixing. You could add extra hardware but you will get linear improvements at best, fixing the queries could potentially give several orders of magnitude of improvement by removing huge scans and such. Though I'm only guessing here as we have no information about the actual queries.
and there are far too many of them to ...
This can be related to Bug 83912. If you run SHOW ENGINE INNODB STATUS you may see transactions like this:
---TRANSACTION 4216198446592, not started sleeping before entering InnoDB
In order to fix it:
SET GLOBAL innodb_thread_concurrency=0;