Admin Linux ... MySQL DBA

MySQL allows a client session to acquire a table lock explicitly for preventing other sessions from accessing the table during a specific period. A client session can acquire or release table locks only for itself. It cannot acquire or release table locks for other sessions. Below are the method to check lock details: SHOW ENGINE INNODB STATUS         This is generally very good, but it can get truncated, and simply may not contain every bit of info you need. Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)         This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.   Run "mysqladmin debug"         Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the Inno

IOPS (input/output operations per second) is the standard unit of measurement for the maximum number of reads and writes to non-contiguous storage locations of devices like hard disk drives (HDD), solid state drives (SSD), and storage area networks (SAN). IOPs is often measured with an open source network testing tool called an Iometer. Some commonly accepted averages for random IO operations, calculated as 1/(seek + latency) = IOPS:  IOPS calculations Every disk in your storage system has a maximum theoretical IOPS value that is based on a formula. Disk performance — and IOPS — is based on three key factors: Rotational speed (aka spindle speed). Measured in revolutions per minute (RPM), most disks you'll consider for enterprise storage rotate at speeds of 7,200, 10,000 or 15,000 RPM with the latter two being the most common. A higher rotational speed is associated with a higher performing disk. This value is not used directly in calculations, but it is highly i

MySQL's InnoDB storage engine data refresh every situation. This post from InnoDB down, look at the data from the memory to the InnoDB actually written on the media storage device in the end there is a buffer in which to work. We look through the following figure at the relevant buffer: From the figure above, we can see that the InnoDB data to disk need to go through     InnoDB buffer pool, Redo log buffer. This is a buffer InnoDB application itself.     page cache / Buffer cache (can be bypassed by o_direct). This is a buffer vfs layer.     Inode cache / directory buffer. This is the buffer vfs layer. Need to refresh by O_SYNC or fsync ().     Write-Back buffer. (You can set parameters to bypass the memory controller)     Disk on-borad buffer. (Can be bypassed by setting the disk controller parameters) Here we use the term "buffer" (typically buffer) to represent the data write scratch, using the term "cache" (usually cache) to represent the data re

20 Database Design Best Practices Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID ...). Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you'll write “Student Course”. StudentCourse is much better). Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.). Keep passwords as encrypted for security. Decrypt them in application when required. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing ...). Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performanc

Kill Sleep Queries It’s all too common for me to come across a box that is hopelessly overloaded with Apache requests and MySQL queries. Sometimes, it takes some brute force to keep the whole box from going under. When all else fails, I use the following one-liner to kill off all of those queries that have been running for too long. mysql> select concat('KILL ',id,';') from information_schema.processlist where Command =    'Sleep'   and Time > 60 into outfile '/tmp/sleep_processes.txt'" mysql> source /tmp/sleep_processes.txt

Replication with Differing Table Definitions on Master and Slave Today, I have increased the size column varchar(5000) to varchar(10000) on slave for table. alter table CASE_ACTIVITY modify remarks VARCHAR(10000)  DEFAULT NULL; And, start getting below error and replication is stoped, [ERROR] Slave SQL: Column 6 of table 'db.slave' cannot be converted from type 'varchar(5000)' to type 'varchar(10000)', Error_code: 1677 MySQL error code 1677 (ER SLAVE CONVERSION FAILED): Columns of table '%-.192s.%-.192s' cannot be converted from type '%-.32s' to type '%-.32s' As a solution I changes slave_type_conversions show global variables like '% slave_type_conversions %'; MySQL>  set global slave_type_conversions=' ALL_NON_LOSSY ' And, it worked :) Controlling what conversions are allowed is controlled with a new server variable SLAVE_TYPE_CONVERSIONS , which is of the type SET('ALL_LOSSY','A

There is issues like “Relay log read failure: Could not parse relay log event entry” many times on MySQLslave node. Most of time in our cases, it’s due to Linux Server crashed/restarted unexpectedly. The detail error is like this: mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 146.23.1.34                   Master_User: repl_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.002032           Read_Master_Log_Pos: 537558834                Relay_Log_File: mysql-relay-bin.003601                 Relay_Log_Pos: 461487606         Relay_Master_Log_File: mysql-bin.002032              Slave_IO_Running: Yes             Slave_SQL_Running: No               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table: