SQL Mode

Default :: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Used :: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

Explanation:
------------
ONLY_FULL_GROUP_BY :: Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

STRICT_TRANS_TABLES :: If a value could not be inserted as given into a transactional table, abort the statement. For a non transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

NO_ZERO_IN_DATE :: The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.
  - The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.
  - If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.
  - If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.

NO_ZERO_DATE :: The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.
  - If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.
  - If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.
  - If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.
 
ERROR_FOR_DIVISION_BY_ZERO :: The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.
  - If this mode is not enabled, division by zero inserts NULL and produces no warning.
  - If this mode is enabled, division by zero inserts NULL and produces a warning.
  - If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.
 
NO_AUTO_CREATE_USER :: Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.
It is preferable to create MySQL accounts with CREATE USER rather than GRANT.

NO_ENGINE_SUBSTITUTION :: Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in. The default SQL mode includes NO_ENGINE_SUBSTITUTION.
Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:
  - With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.
  - With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.
 
NO_AUTO_VALUE_ON_ZERO :: It affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. 
 
Other Important One's:
---------------------------
ANSI :: This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section. 

TRADITIONAL :: Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section.

References:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Semaphores

SEMAPHORES - Semaphores have to do with waiting for internal thread locks in the OS
---------------------
OS WAIT ARRAY INFO: reservation count 90528934
OS WAIT ARRAY INFO: signal count 210610453
OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
--Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
--Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 153873448, rounds 52690538, OS waits 1037962
RW-shared spins 66106957, rounds 2526364206, OS waits 72728391
RW-excl spins 22519696, rounds 862681564, OS waits 15671776
Spin rounds per wait: 0.34 mutex, 38.22 RW-shared, 38.31 RW-excl

1st Information:
One is list of current waits. This section will only contain any entries if you’re running in high concurrency envinronment, so Innodb has to fall back to OS waits frequently. If wait was resolved via Spinlock it will not be seen in this section.

Looking at this section you can get an idea what might be hot spot in your workload. It however requires some knowledge of source code – you only get file names and lines (which are different in different versions), you get no information what this object is responsible for. You however can well guess from file names – in this case file is “buf0buf.ic” what means there is some buffer pool contention. However if you want to know more – you need to browse source.

You also see some details printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number of waiters, plus you can see wait status information “wait is ending” in this case which means mutex is already free for grabs but os has not yet scheduled thread so it could proceed with execution.

2nd Information:
The second piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are allocated in it and how frequently threads are signaled using sync array. These counters can be used to represent frequency with which Innodb needs to fall back to OS Wait. There is direct information about OS waits as well – you can see “OS Waits” for
mutexes, as well as for read-write locks. For these information both for exclusive locks and for shared locks is displayed. OS Wait is not exactly the same as “reservation” – before falling back to complex wait using sync_array Innodb tries to “yield” to OS hoping when name thread is scheduled next time object will be free already. OS Waits are relatively slow, and if you get tens of thousands of OS waits per second it may be the problem. The other way to look at it is context switch rate in your OS stats.

The other important peice of information is number of “spin waits” and “spin rounds”. Spin locks are low cost wait, compared to OS wait, it is however active wait which wastes your CPU cycles, so if you see very large amount of spin waits and spin rounds significant CPU resources may be wasted. It should come to hundreds of thousands spin rounds per second to start really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU time running spin locks and doing unneeded context switches.

Note: innodb_thread_concurrency = 0;
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.

The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the queries inside InnoDB and queries in queue counters in the ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output.

References:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
http://dba.stackexchange.com/questions/123703/query-about-mysql-semaphores-in-engine-status

nagios monitoring

nagios: Nagios plugins for monitoring MySQL databases and database servers.

Plugin Used :: check_mysql_health - check_mysql_health is a plugin for Nagios that allows you to monitor a MySQL database. Among the list of metrics are time to login, index usage, bufferpool hit rate, query cache hit rate, slow queries, temp tables on disk, table cache hit rate, connected threads, and many more.

Checks for:
1> CPU Load :: Check CPU load percentage.
   [root@ ~]# top -n 1 -b | head -5
  
2> Disk partitions :: Check the disk partition free and used percentage.
   [root@ ~]# df -h
  
3> Users logged in :: How many users been logged in at database server.
   [root@ ~]# who
  
4> Memory percentage :: How much memory been used by the MySQL process.
   ps aux | grep -i mysqld | grep -v grep
  
5> MySQL Connection time :: Determines how long connection establishment and login take
   time mysql -u$DB_USER -p$DB_PASSWD -D$DB_NAME -e "exit"
  
6> MySQL Failed Lock Rate :: Table lock contention. Rate of failed table locks.
   mysql> show global status like 'Table_locks_waited';
  
7> MySQL Hitrate Table Cache :: Hitrate in the Table-Cache. Table cache hitrate
   mysql> show global status like 'open%table%';
   Table Cache Hit Rate = open_tables/opened_tables
  
8> MySQL InnoDB Buffer Wait Rate :: Rate of the InnoDB Buffer Pool Waits
   mysql> show global status like 'Innodb_buffer_pool_wait_free';
  
9> MySQL InnoDB Log Wait Rate :: InnoDB log waits because of a too small log buffer. Rate of the InnoDB Log Waits
   mysql> show global status like 'Innodb_log_waits';

10> MySQL Long Running Procs :: long running processes. Sum of processes that are running longer than 1 minute.
    mysql> select * from information_schema.processlist where time>60 and command != 'Sleep';

11> MySQL Lowmem Prunes :: Query cache entries pruned because of low memory
    mysql> show global status like 'Qcache_lowmem_prunes';

12> MySQL Open Files :: Number of open files (of upper limit)
    mysql> show global status like 'Open_files';

13> MySQL Open Threads :: How many database threads been created.
    mysql> show global status like 'Threads_connected';

14> MySQL Slave IO :: Checks if the IO-Thread of the Slave-DB is running
    mysql> show slave status\G
   
15> MySQL Slave Lag :: Delay between Master and Slave
    mysql> show slave status\G
   
16> MySQL Slave SQL :: Checks if the SQL-Thread of the Slave-DB is running
    mysql> show slave status\G
   
17> MySQL Slow Queries :: Rate of queries that were detected as “slow”
    mysql> show global status like 'Slow_queries';

18> MySQL Tmp Disk Tables :: Percent of the temporary tables that were created on the disk instead in memory
    mysql> show global status like 'Created_tmp_disk_tables';

19> MySQL Uptime :: Time since start of the database server (the server is running).
    mysql> SHOW STATUS LIKE 'Uptime'
   
20> Mysql Service Port :: Check if the services are running on designated MySQL database port.
    [root@ ~]# netstat -na | grep -i 3306 | grep -i "ESTABLISHED" | wc -l
    [root@ ~]# netstat -na | grep -i 3306 | grep -i "LISTEN" | wc -l
   
21> Total Processes :: How many processes by running at OS.
    [root@ ~]# ps -eaf | wc -l

22> Check memcache port :: Check if the services are running on designated MySQL memcache port.
    [root@ ~]# netstat -na | grep -i 11211 | grep -i "ESTABLISHED" | wc -l
    [root@ ~]# netstat -na | grep -i 11211 | grep -i "LISTEN" | wc -l

23> InnoDB Buffer Pool Hit rate :: Check if innodb buffer pool hit rate percentage.
     mysql> show engine innodb status\G
     # grep for keyword "Buffer pool hit rate"


References:
http://kedar.nitty-witty.com/blog/10-steps-mysql-monitoring-nagios-installation-configuration
https://exchange.nagios.org/directory/Plugins/Databases/MySQL
https://exchange.nagios.org/directory/MySQL/check_mysql_health/details
https://labs.consol.de/nagios/check_mysql_health/

AWS S3 commands

S3 : Simple Storage Service

aws s3 <Command> [<Arg> ...]
aws s3 help

Available Commands:
 - cp :: Copies a local file or S3 object to another location locally or in S3.
 - ls :: List S3 objects and common prefixes under a prefix or all S3 buckets.
 - mb :: Creates an S3 bucket.
 - mv :: Moves a local file or S3 object to another location locally or in S3.
 - presign :: Generate a pre-signed URL for an Amazon S3 object. This allows anyone who receives the pre-signed URL to retrieve the S3 object with an HTTP GET request.
 - rb :: Deletes an empty S3 bucket. A bucket must be completely empty of objects and versioned objects before it can be deleted.
 - rm :: Deletes an S3 object.
 - sync :: Syncs directories and S3 prefixes. Recursively copies new and updated files from the source directory to the destination. Only creates folders in the destination if they contain one or more files.
 - website :: Set the website configuration for a bucket.

Note: Bucket name is Case sensitive.

Example:
1> aws s3 ls s3://mybucket
   aws s3 ls s3://DatabaseBackups/
   aws s3 ls s3://DatabaseBackups/ --recursive | head -10
   aws s3 ls s3://DatabaseBackups/db_name/

2> aws s3 cp backup.tar.gz s3://DatabaseBackups/db_name/backup.tar.gz
   aws s3 cp ${BACKUPFILE}.tar.gz s3://DatabaseBackups/${DB_NAME}/${BACKUPFILE}.tar.gz  

References:
http://docs.aws.amazon.com/cli/latest/reference/s3/
http://docs.aws.amazon.com/cli/latest/userguide/cli-s3.html
http://docs.aws.amazon.com/cli/latest/userguide/using-s3-commands.html

disk addition at EC2

Following steps/commands needs to be executed for disk add at EC2 server:

mkdir -p /u01/app/mysql
mkdir -p /u02/mysqldata
mkdir -p /u03/mysqldata/
mkdir -p /u99/mysqldata/
fdisk /dev/xvdb
fdisk /dev/xvdc
fdisk /dev/xvde
fdisk /dev/xvdd
ls /dev/x
mkfs -t ext4 /dev/xvdb1
mkfs -t ext4 /dev/xvdc1
mkfs -t ext4 /dev/xvdd1
mkfs -t ext4 /dev/xvde1
mount -t ext4 /dev/xvdb1 /u01/app/mysql/
mount -t ext4 /dev/xvdc1 /u02/mysqldata/
mount -t ext4 /dev/xvdd1 /u03/mysqldata/
mount -t ext4 /dev/xvde1 /u99/mysqldata/
df -h
vi  /etc/fstab
reboot


[root@ip ~]# cat /etc/fstab
#-Database  Mounts
/dev/xvdb1      /u01/app/mysql          ext4    defaults        0   0
/dev/xvdc1      /u02/mysqldata          ext4    defaults        0   0
/dev/xvdd1      /u03/mysqldata          ext4    defaults        0   0
/dev/xvde1      /u99/mysqldata          ext4    defaults        0   0


[root@ip ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       15G  3.2G   12G  22% /
devtmpfs        3.7G   88K  3.7G   1% /dev
tmpfs           3.7G     0  3.7G   0% /dev/shm
/dev/xvdb1      9.8G  1.1G  8.2G  12% /u01/app/mysql
/dev/xvdc1       99G  2.3G   92G   3% /u02/mysqldata
/dev/xvdd1       99G  215M   94G   1% /u03/mysqldata
/dev/xvde1       99G  687M   93G   1% /u99/mysqldata

OS packages installation at EC2 server

OS packages needs to be installed at EC2 server:
========================================

perl-Time-HiRes
perl-Data-Dumper
perl-DBI
perl-DBD-MySQL
perl-ExtUtils-MakeMaker.noarch

telnet
openssl-devel
ksh
libaio
wget
mutt
mail

Commands:
yum install perl-Time-HiRes perl-Data-Dumper perl-DBI perl-DBD-MySQL perl-ExtUtils-MakeMaker.noarch
yum install telnet openssl-devel ksh libaio wget mutt mail

Database monitoring

Apart from Nagios, Cacti, AWS graphs, several shell scripts been executed on the databases to gather stats timely.

gatherDBStats.ksh - Once every 1 hr
echo "Select Global status"
SELECT CONCAT(VARIABLE_NAME,'$del',VARIABLE_VALUE) FROM information_schema.GLOBAL_STATUS;

echo "Select Global Variables"
SELECT CONCAT(VARIABLE_NAME,'$del',VARIABLE_VALUE) FROM information_schema.GLOBAL_VARIABLES;

echo "Select DB Size"
SELECT CONCAT(sum(ROUND(data_length/1024/1024,2)),'$del',sum(ROUND(index_length/1024/1024,2)))
  FROM   information_schema.TABLES
  WHERE  table_schema = '$DB_NAME';

monitorDBStatus.ksh - Once every 5 minutes
echo "Select PROCESSLIST"
SELECT * FROM information_schema.PROCESSLIST;

echo "Select INNODB TRX"
SELECT * FROM information_schema.INNODB_TRX;

echo "Select INNODB TRX Locks"
SELECT * FROM information_schema.INNODB_LOCKS;

echo "Select INNODB TRX Lock Waits"
SELECT * FROM information_schema.INNODB_LOCK_WAITS

echo "Select InnoDB status"
SHOW ENGINE INNODB STATUS\G

echo "Checking Free memory"
free -m

echo "Checking MySQL Process Memory"
ps aux | grep -i %CPU | grep -v grep
ps aux | grep -i mysqld | grep -v grep

echo "Checking OS Memory"
top -n 1 -b | head -5

echo "Checking iostat"
iostat -d -x 5 1