Day 25 Optimization

Locking is depends upon Isolation level and Storage Engine.

MySQL uses table level locking (instead of page, row, or column locking) for all storage engines except InnoDB, which uses row level locking

locking system in MySQL:

Implicit Locking:

To maintain the ACID property every transaction has to maintain the locking internally so that end user will retrieve the valid data to achieve it engine has to have some implicit locking like write lock readers.

Explicit locking:

For some of the transactions user wanted to make sure that their transaction will be uninterrupted so they make an explicit lock on transaction.

Blocking is locking on session level, one transaction is writing on the object and if other transaction is trying to access same object, it will not until first write transaction completes (first session is blocking second session)

To get the blocking information:


MySQL>Show Full PROCESSLIST; ## extra information.

Following command will provide additional detail:

SELECT    ,pl.user    ,pl.state    ,it.trx_id     ,it.trx_mysql_thread_id     ,it.trx_query AS query   ,it.trx_id AS blocking_trx_id    ,it.trx_mysql_thread_id AS blocking_thread    ,it.trx_query AS blocking_queryFROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it    ON = it.trx_mysql_thread_idINNER JOIN information_schema.innodb_lock_waits AS ilw    ON it.trx_id = ilw.requesting_trx_id         AND it.trx_id = ilw.blocking_trx_id

  • MySQL uses memory accordingly to value assigned to variable “innodb_buffer_pool_size “ standard value should be 50 % – 70% of total OS memory.the value to this variable is dynamic – no need to restart mysql to get effect.


To know how many times we have locks on table and required wait


| Table_locks_immediate | Table_locks_waited

To get the benchmark information about how much time required to retrieve the data for calculation

SELECT BENCHMARK(1000000,1+1)1 row in set (0.32 sec)

To get structure information about table


Similarly SHOW CREATE TABLESHOW TABLE STATUS, and SHOW INDEX statements to get information about tables.


To get the execution plan for the statement explain is the command.

{EXPLAIN tbl_name [col_name | wild]




{explainable_stmt | FOR CONNECTION connection_id}

explain_type: {



explainable_stmt: {

SELECT statement

| DELETE statement

| INSERT statement

| REPLACE statement

| UPDATE statement


Analyze table is used to update the statistics of the table and analyze it. During this operations table will be locked.

Statistics information is are stored in mysql.innodb_table_stats and mysql.innodb_index_stats

and also can be used using mysqlstats plugin OPTIMIZE TABLE Syntax

Optimize table is used to reorganized the indexes and data on the table for better performance. CHECK TABLE Syntax

It check the table if any error exists CHECKSUM TABLE Syntax

To maintain the consistency of the table this command it used, it also used for validation for backup data. REPAIR TABLE Syntax

This command is used for repair of any issue on table.




This entry was posted in DeadLock, Lock/Blocking, MySQL, Others, Performance Tuning, What I learned today and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.