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

Rule Based Optimization Vs Cost Based Optimization

Rule Based Optimization: This is an old technique. Basically, the RBO used a set of rules to determine how to execute a query. E.g. If an index is available on a table, the RBO rules can be to always use that index (a RBO for our travel analogy can be avoid all routes with speed brakers). As it turns out that this is simpler to implement but not the best strategy always and can backfire. RBO was supported in earlier versions of Oracle. (SQL Server supports table hints which in a way can be compared to RBO, as they force optimizer to follow certain path).

Basically, the RBO used a set of rules to determine how to execute a query. If an index was available on a table, the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example, assume someone put an index on the GENDER column, which holds one of two values, MALE and FEMALE.

SELECT * FROM emp WHERE gender='FEMALE';

If the above query returned approximately 50% of the rows, then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10% of the total table volume, using an index would slow things down. The RBO would always use an index if present because its rules said to.

The biggest problem with the RBO was that it did not take the data distribution into account.

Cost Based Optimization: Motivation behind CBO is to come up with the cheapest execution plan available for each SQL statement. The cheapest plan is the one that will use the least amount of resources (CPU, Memory, I/O, etc.) to get the desired output (in relation to our travel analogy this can be Petrol, time, etc.). This can be a daunting task for DB engine as complex queries can thousands of possible execution paths, and selecting the best one can be quite expensive. CBO is supported by most of databases including MySQL, Oracle, SQL Server, etc.

The CBO uses statistics about the table, its indexes and the data distribution to make better informed decisions. Using our previous example, assume that the company has employees that are 95% female and 5% male. If you query for females, then you do not want to use the index. If you query for males, then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.


References:
https://nirajrules.wordpress.com/2009/06/10/cost-based-optimization-cbo-vs-rule-based-optimization-rbo/
http://searchoracle.techtarget.com/answer/Rule-based-vs-cost-based-optimization

MySQL Clutser Features

MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The shared-nothing architecture enables the system to work with very inexpensive hardware, and with a minimum of specific requirements for hardware or software.

MySQL Cluster is designed not to have any single point of failure. In a shared-nothing system, each component is expected to have its own memory and disk, and the use of shared storage mechanisms such as network shares, network file systems, and SANs is not recommended or supported.

MySQL Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB (which stands for “Network DataBase”).

A MySQL Cluster consists of a set of computers, known as hosts, each running one or more processes. These processes, known as nodes, may include MySQL servers (for access to NDB data), data nodes (for storage of the data), one or more management servers, and possibly other specialized data access programs.

MySQL Cluster features:   
 - Synchronous Replication - Data within each data node is synchronously replicated to another data node.
 - Automatic Failover - MySQL Cluster's heartbeating mechanism instantly detects any failures and automatically fails over, typically within one second, to other nodes in the cluster, without interrupting service to clients.
 - Self Healing - Failed nodes are able to self-heal by automatically restarting and resynchronizing with other nodes before re-joining the cluster, with complete application transparency
 - Shared Nothing Architecture, No Single Point of Failure - each node has its own disk and memory, so the risk of a failure caused by shared components such as storage, is eliminated.
 - Geographical Replication - Geographic replication enables nodes to be mirrored to remote data centers for disaster recovery.   

Other features:
 - On-Line schema updates
 - On-Line scaling (adding nodes for capacity and performance)
 - On-Line upgrades and patching of hosts, OS and database
 - On-Line backup

References:
https://www.mysql.com/products/cluster/availability.html
http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html

explain plan

Explain Plan
==========

- Prefix a SELECT query with EXPLAIN. MySQL won't actually execute the query, it only analyses it.
- In 3 ways, we can optimise the query:
  : Modify or create indexes
  : Modify query structure
  : Modify data structure
- Keep indexes in memory by trimming the fat:
  : Reduce the characters in VARCHAR index
  : Use TINYINT instead of BIGINT
- MySQL will only use 1 index per query/table. It cannot combine 2 separate indexes to make a useful one.

Case-1:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: recipes
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where

table: The table entry tells you which table this relates to - not at all useful in this example but when you run a select statement with multiple tables through explain, you'll get one row per table so this column tells you which table it is.
possible_keys: The possible_keys shows which indexes apply to this query and the key tells us which of those was actually used - here there are no keys that apply.
rows: Tell us how many rows MySQL had to look at to find the result set.

Note: The ideal outcome is that the rows number is the same as the number of results from the query

Case-2: In explain plan of a query, getting: Using where; Using temporary; Using filesort
- Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.
- Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.

Case-3:
possible_keys: All the possible indexes which MySQL could have used. Based on a series of quick lookups and calculations.
- FTS are almost always the slowest query.

References:
http://www.slideshare.net/phpcodemonkey/mysql-explain-explained

MySQL XA Transaction

MySQL XA Transaction:
XA supports distributed transactions, that is, the ability to permit multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources.

A global transaction involves several actions that are transactional in themselves, but that all must either complete successfully as a group, or all be rolled back as a group. In essence, this extends ACID properties “up a level” so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties. (As with nondistributed transactions, SERIALIZABLE may be preferred if your applications are sensitive to read phenomena. REPEATABLE READ may not be sufficient for distributed transactions.)

References:
http://dev.mysql.com/doc/refman/5.7/en/xa.html

MySQL High Availability

MySQL HA
-----------------
- Elastic Load Balancers (ELB) have existed in Amazon Web Services (AWS) for quite some time now. As you might guess, they are commonly used to balance incoming HTTP(S) traffic across a collection of instances running your app in EC2
- They can also perform health checks on their target EC2 instances, and detect whether or not an instance and the app running on it are healthy.
- The health check itself is simply an HTTP(S) GET request to a port on which an application is listening. If the application is functioning correctly, it responds to the health check with a 200 status code.

Automatic failover
-------------------------
- Can combine ELB health checks with Auto Scaling groups to identify failing instances and cycle them out automatically, with zero downtime.
- When the ASG boots an instance, it verifies the health of that instance to make sure it has booted and is functioning correctly. We configure the ASG so it adds its instances to the ELB and then uses the ELB’s health checks for verifying instance health.

Auto Scaling
------------------
Auto Scaling helps you maintain application availability and allows you to scale your Amazon EC2 capacity up or down automatically according to conditions you define. You can use Auto Scaling to help ensure that you are running your desired number of Amazon EC2 instances. Auto Scaling can also automatically increase the number of Amazon EC2 instances during demand spikes to maintain performance and decrease capacity during lulls to reduce costs. Auto Scaling is well suited both to applications that have stable demand patterns or that experience hourly, daily, or weekly variability in usage. Whether you are running one Amazon EC2 instance or thousands, you can use Auto Scaling to detect impaired Amazon EC2 instances and unhealthy applications, and replace the instances without your intervention. This ensures that your application is getting the compute capacity that you expect.

- ELB with the help of Auto Scaling:
  - Only 1 DB server behind ELB (Master - M1)
  - Master M2 is running in different AZ, however in sync with M1
  - nagios monitoring - whenever M1 goes down:
    * take out the M1 server from ELB.
    * Put M2 behind ELB.
    * Create M3 from the backup and start replication between M2 and M3
   
ELB Health Check Configuration
- Ping Port
- Ping Path   

Register ec2 instance with ELB:
aws elb register-instances-with-load-balancer --load-balancer-name my-loadbalancer --instances i-4e05f721

Deregister ec2 instance from ELB:
aws elb deregister-instances-from-load-balancer --load-balancer-name my-loadbalancer --instances i-4e05f721

Check the health status of your instance:
aws elb describe-instance-health --load-balancer-name my-load-balancer

References:

MHA (MySQL HA)   
-------------------------
https://code.google.com/p/mysql-master-ha/
https://mysqlstepbystep.com/2015/06/01/mysql-high-available-with-mha-2/
https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2
https://github.com/yoshinorim/mha4mysql-manager

Virtual IP Address failover on EC2
--------------------------------------------
https://aws.amazon.com/articles/2127188135977316

AWS Failover Architectures
-----------------------------------
http://docs.rightscale.com/clouds/aws/amazon-ec2/aws_failover_architectures.html

ELB for auto failover
---------------------------
https://engineering.gosquared.com/use-elb-automatic-failover

Daily DBA Issues

Daily DBA Issues:
===============

- Disk space issue at the time of backup/binary logging
- Replication lags at the time of very high insert at Master
- Master-Slave discrepancy when some one deletes data from slave (Using SQL_LOG_BIN=0)
- High disk reads at Primary server
- Dropping older partitions because query is taking lock at all partitions
- Lot of slow queries. (Not using indexes/Slow disk I/O, data grows in size and thus not fitting in innodb_buffer_pool_size)
- Purging old data
- High CPU %. Queries got stuck at releasing connection.
- Slow disk IO. HW issue. Promoted slave to master and all worked fine.
- Deploy DB patch at running system. Can't afford downtime.

memcache

memcache :: memcached is a simple, highly scalable key-based cache that stores data and objects wherever dedicated or spare RAM is available for quick access by applications, without going through layers of parsing or disk I/O. To use, you run the memcached command on one or more hosts and then use the shared cache to store objects.

2 types:
  - InnoDB memcached  
  - Traditional memcached  
 
Default Port Used: 11211 

InnoDB Memcached plugin :: The InnoDB memcached plugin (daemon_memcached) provides an integrated memcached daemon that automatically stores and retrieves data from InnoDB tables, turning the MySQL server into a fast “key-value store”. Instead of formulating queries in SQL, you can use simple get, set, and incr operations that avoid the performance overhead associated with SQL parsing and constructing a query optimization plan. You can also access the same InnoDB tables through SQL for convenience, complex queries, bulk operations, and other strengths of traditional database software.

This “NoSQL-style” interface uses the memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as add, set, and incr are stored to disk, in InnoDB tables. The combination of memcached simplicity and InnoDB reliability and consistency provides users with the best of both worlds.

Advantages:
 - Direct access to the InnoDB storage engine avoids the parsing and planning overhead of SQL.
 - Running memcached in the same process space as the MySQL server avoids the network overhead of passing requests back and forth.
 - Data requested through the memcached protocol is transparently queried from an InnoDB table, without going through the MySQL SQL layer.
 - Data is stored in a MySQL database to protect against crashes, outages, and corruption.
 - You can ensure high availability by using the daemon_memcached plugin on a master server in combination with MySQL replication.

Note: The InnoDB memcached plugin implements memcached as a MySQL plugin daemon that accesses the InnoDB storage engine directly, bypassing the MySQL SQL layer.

Features:
 - memcached as a daemon plugin of mysqld. Both mysqld and memcached run in the same process space, with very low latency access to data.
 - Control over how often data is passed back and forth between InnoDB and memcached operations through the innodb_api_bk_commit_interval, daemon_memcached_r_batch_size, and daemon_memcached_w_batch_size configuration options.
 - Direct access to InnoDB tables, bypassing the SQL parser, the optimizer, and even the Handler API layer.

Parameters:
 daemon_memcached_w_batch_size=25     //Specifies how many memcached write operations, such as add, set, or incr, to perform before doing a COMMIT to start a new transaction.

 daemon_memcached_option='-v -m 1024'   //Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup.

 innodb_api_enable_binlog=1    //To use the daemon_memcached plugin with the MySQL binary log

Steps:
  1> Configure the daemon_memcached plugin so it can interact with InnoDB tables by running the innodb_memcached_config.sql configuration script, which is located in MYSQL_HOME/share. This script installs the innodb_memcache database with three required tables (cache_policies, config_options, and containers). It also installs the demo_test sample table in the test database. It will create:
    - Database : innodb_memcache
    - Tables : cache_policies, config_options, containers
   
  2> Alter table - Add columns:
    - c3 : int(11) NULL comment 'Must exists col for flag in memcahe req'
    - c4 : bigint(20) unsigned  NULL comment 'Must exists col for compare and swap in memcahe req'
    - c5 : int(11) NULL comment 'Must exists col for expire timestamp in memcahe req'
   
  3> Add index/FK to the key_value column.
 
  4> Add entry of the table into innodb_memcache.containers
    - insert ignore into innodb_memcache.containers(name,db_schema,db_table,key_columns,value_columns,flags,cas_column,expire_time_column,unique_idx_name_on_key) values ('env_pd','database_name','table_name','key_column','value_columns', c3,c4,c5,'PRIMARY');commit;"
    Note: value_columns -> deviceId|statusTS|deviceTS
   
  5> Activate the daemon_memcached plugin by running the INSTALL PLUGIN statement: 
     mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
     Note: mysql -u$DB_USER -pDB_PASSWD -e"install plugin daemon_memcached soname \"libmemcached.so\";"
   
  6> Restart DB and check for:
     memcachedRunning=`netstat -tap | grep -i memcache | wc -l`
   
Note:
   1> To uninstall
      mysql> uninstall plugin daemon_memcached;   
   2> To check if memcache installed or not 
      memcachedInstalled=`mysql -u$DB_USER -e "show plugins;" | grep -i  libmemcached.so | wc -l`  
 
References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-intro.html
http://dev.mysql.com/doc/refman/5.7/en/ha-memcached-using-deployment.html

Partitioning

Partitioning :: Partitioning allows you to store parts of your table in their own logical space. With partitioning, you want to divide up your rows based on how you access them. If you partition your rows and you are still hitting all the partitions, it does you no good. The goal is that when you query, you will only have to look at a subset of the data to get a result, and not the whole table.
   * A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance reasons.
  
   * RANGE : RANGE partitioning contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. Rows are partitioned based on the range of a column (i.e date, 2006-2007, 2007-20008, etc,.). Range partitioning is particularly useful when you want or need to delete “old” data. Can simply use ALTER TABLE employees DROP PARTITION p0.
   Example: PARTITION BY RANGE ( YEAR(hired) ) (
            PARTITION p0 VALUES LESS THAN (1991)
            PARTITION p3 VALUES LESS THAN MAXVALUE
            );
           
   * LIST : List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning by RANGE, each partition must be explicitly defined. The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. Unlike the case with partitions defined by range, list partitions do not need to be declared in any particular order.
   Example: PARTITION BY LIST(dept_id) (
            PARTITION pB1 VALUES IN (3,5,6,9,17),
            PARTITION pB2 VALUES IN (1,2,10,11,19,20));
           
   * HASH : Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.
   Example: PARTITION BY HASH(dept_id)
            PARTITIONS 4;
           
   * Key : Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose.
   Example: PARTITION BY KEY()
            PARTITIONS 2;

Percona Toolkit

percona toolkit
============
-> pt-query-digest :: Analyses a slow query log and prints a report.
   * pt-query-digest --limit /u99/mysql-slow.log
   (--limit: Default value 95%:20, means 'displays the 95% worst queries or top 20 worst whichever comes first)
   (--interval=0.01)
  
-> pt-archiver :: archiving/purging
  * archiving - Archiving means moving data from one table to another table
  * purging - Purging means removing data
  * purge :: pt-archiver -u -p -P -D -h --where 'emp_id>1000' --limit 100 --commit-each --purge
  * archive :: pt-archiver -u -p -P -D -T -h --dest -h -D -T --where 'emp_id>1000' --limit 100 --commit-each
  * Use --dry-run :: to know what the tool will do
 
-> pt-table-checksum :: Replication doesn't check data consistency. On slaves, it tries to run queries registered in the binlogs of the master.
   * What can go wrong : Writing on slave, instead of master.
                       : Skipping replication events.
                       : Undeterministic writes
                       : Replication may stop with problems, or may continue with problems
   * Computing a checksum of rows on the master and slave.
  
-> pt-table-sync - Will generate the queries to fix errors
   * Use: --no-check-triggers option
  
-> pt-online-schema-change :: Problem with ALTER TABLE - It creates a copy of the table. Original table is locked during the process.
   * It track changes to the original table - by using triggers
   * And then copies rows in chunks, like ALTER TABLE, but without lock
   * If you already have triggers, it won't work. MySQL allows only 1 trigger for each action
   * Slower than plain ALTER TABLE.
   * Check: --dry-run. And if all goes good replace --dry-run with --execute

MySQL Enterprise Backup

Enterprise Backup
================
- MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.

- MySQL Enterprise Backup delivers:
  * “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
  * High Performance – Save time with faster backup and recovery
  * Incremental Backup – Backup only data that has changed since the last backup
  * Partial Backup – Target particular tables or tablespaces
  * Compression – Cut costs by reducing storage requirements up to 90%
  * Backup to Tape – Stream backup to tape or other media management solutions
  * Fast Recovery – Get servers back online and create replicated servers
  * Point-in-Time Recovery (PITR) – Recover to a specific transaction
  * Partial restore – Recover targeted tables or tablespaces
  * Restore to a separate location – Rapidly create clones for fast replication setup
 
- mysqlbackup --user=root --password --backup-dir=/u02/$DB_NAME backup-and-apply-log

- If wanted to have a unique folder for this backup, can use the –with-timestamp. The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

- If don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.

- You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.

- On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.

- /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back-and-apply-log

- An easy way to check to see if the databases match can use one of the MySQL Utilities – mysqldbcompare.

- The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.”

References:
https://www.mysql.com/products/enterprise/backup.html
http://www.oracle.com/us/products/mysql/mysql-enterprise-backup-362550.pdf

InnoDB locks

Locks
======

InnoDB Lock Modes
-----------------------------
InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.
  - A shared (S) lock permits the transaction that holds the lock to read a row.
  - An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
 
If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:
  - A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
  - A request by T2 for an X lock cannot be granted immediately.
 
If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately.
Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.  

Intention Locks
----------------------
Additionally, InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table.
There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):
   - Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.
   - Intention exclusive (IX): Transaction T intends to set X locks on those rows.
For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.

The intention locking protocol is as follows:
   - Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.
   - Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t. b

Record Locks
-------------------
A record lock is a lock on an index record. For example, SELECT c1 FOR UPDATE FROM t WHERE c1 = 10; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

Gap Locks
-----------------
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty. Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED

Auto-Inc Locks
---------------------
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

Insert Intention Locks
--------------------------------
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.


References:
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

mysqlbinlog

mysqlbinlog
==========
The server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs.

For statement-based logging, event information includes the SQL statement, the ID of the server on which it was executed, the timestamp when the statement was executed, how much time it took, and so forth. For row-based logging, the event indicates a row change rather than an SQL statement.

Parameters:
log-bin        = /u03/mysqldata/$DB_NAME/logs/bin/mysql-bin
log_bin_index  = /u03/mysqldata/$DB_NAME/logs/bin/mysql-bin.log.index
binlog_format=mixed

1>
The binary log files and its data are likely to be very huge, thus making it almost impossible to read anything on screen. However, you can pipe the output of mysqlbinlog into a file which can be open up for later browsing in text editor, by using the following command:

mysqlbinlog binlog.000001 > filename.txt

2> –start-datetime=datetime

Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument. The datetime value is relative to the local time zone on the machine where you run mysqlbinlog. The value should be in a format accepted for the DATETIME or TIMESTAMP data types. For example:

mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000001

3> –stop-datetime=datetime

Stop reading the binary log at the first event having a timestamp equal or posterior to the datetime argument. This option is useful for point-in-time recovery. See the description of the –start-datetime option for information about the datetime value.

4> –start-position=N

Start reading the binary log at the first event having a position equal to the N argument. This option applies to the first log file named on the command line.

5> –stop-position=N

Stop reading the binary log at the first event having a position equal or greater than the N argument. This option applies to the last log file named on the command line.

6> --verbose::Reconstruct row events as SQL statements
Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata.
mysqlbinlog -v log_file
mysqlbinlog -vv log_file

References:
https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

Restore Single table from full backup

Restore single table from complete backup
====================================

Pr-requisite: Using percona Xtrabackup

a) echo Starts innobackupex
   innobackupex --defaults-file=/u01/app/mysql/$DB_NAME/etc/my.cnf --user=$DB_ROOT_USER --password=$ROOT_USERPSW --apply-log --export $RESTORE_PATH_DIR/

b) echo Create table schema and discard tablespace
   mysql -u$DB_ROOT_USER -p$ROOT_USERPSW -D$DB_NAME -e"set FOREIGN_KEY_CHECKS=0;source $TABLESTR_FILE;ALTER TABLE $TABLE_NAME DISCARD TABLESPACE;"

c) echo copy files
   cp -p /u99/mysqldata/${DB_NAME}/${BACKUP_DIREC}/${DB_NAME}/$TABLE_NAME.ibd /u99/mysqldata/${DB_NAME}/${BACKUP_DIREC}/${DB_NAME}/$TABLE_NAME.exp /u99/mysqldata/${DB_NAME}/${BACKUP_DIREC}/${DB_NAME}/$TABLE_NAME.cfg /u02/mysqldata/$DB_NAME/data/$DB_NAME/

d) echo change permissions
   chown mysql:mysql /u02/mysqldata/$DB_NAME/data/$DB_NAME/$TABLE_NAME.*
   chmod 660 /u02/mysqldata/$DB_NAME/data/$DB_NAME/$TABLE_NAME.*

e) echo Import tablespace  
   mysql -u$DB_ROOT_USER -p$ROOT_USERPSW -D$DB_NAME -e"set FOREIGN_KEY_CHECKS=0;ALTER TABLE $TABLE_NAME IMPORT TABLESPACE;"

Note: To achieve above result, innodb_file_per_table should be enabled.

DB Backups

DB backups
==========
1> OS level
   a) :: Shutdown the slave. Make the copy of whole system. Move the tar file and untar it on new system.
  
2> Percona backup/Recvoery (if using Community version)
   :: backup - innobackupex --defaults-file=${MY_CNF} --user=${DB_USER} --password=${DB_PASS} --no-lock --parallel=4 --port=${DB_PORT} ${DEST_DIR} --no-timestamp
   :: recovery -
      a) Check space availability and Stop DB instance
      b) copy and untar backup file
      c) Remove data/log directories
      d) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --apply-log $BACKUP_DIR/$BACKUP_DIREC
      e) innobackupex --defaults-file=/u01/app/mysql/${DB_NAME}/etc/my.cnf --copy-back $BACKUP_DIR/$BACKUP_DIREC
      f) Copy back configuration file
     
3> MySQL Enterprise backup/recovery (if using Enterprise edition)
   :: backup - mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image backup-and-apply-log
  
   backup-and-apply-log :: This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored.
  
   :: Restore - mysqlbackup --defaults-file=<backupDir>/backup-my.cnf -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log  
 
4> mysqldump - in case need to migrate 1 or 2 tables for some debugging.
   :: --single-transaction -  This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
  
5> LVM Snapshots

Indexes usage

Indexes usage:

1> Too many indexes / Too few indexes - both are equally bad for the system
   - Too few :: Full table scan. SELECT Queries will take time.
   - Too many :: DML operation may get slow, as they have to update page indexing.
2> Never put index on column which is regularly updated.
3> All SELECT queries should be indexed properly. Exception can be taken:
   - Column with low cardinality. (like M/F or Y/N)  
   - Column which is updated often.
4> Log queries which are not using indexes.
5> Check explain plan for the queries been captured.

database running slow

Very often we get concern that database is running slow. Below are the checks what we can perform:

1> Ask the developer/tester/Designer - What particularly is running slow? or whole system as such slow?
2> Logon to database. And check following:
   - slow_query_log (Edit long_query_time/log_queries_not_using_indexes accordingly.)
   - select * from information_schema.processlist;
   - select * from information_schema.innodb_trx;
   - select * from information_schema.innodb_lock_waits; //To check for any transaction locks
3> Execute show engine innodb status\G
   - Check for log movement
   - Transaction timeout.
4> Check how much memory been taken by MySQL process as such. 'free -m' / 'top'
5> Check for disk utilization 'df -h'
6> Check for error.log
7> Check for NW latency (from app server or replicated server).
8> Check for SELECT statement is taking time, or some DML command or particularly 'COMMIT' / 'ROLLBACK' is taking time.
   - What is the cause for this? And its solution?
   Ans> If 1000s of transactions are coming and trying to Commit;
   Use: innodb_flush_log_at_trx=2
9> Check for nagios/cacti/AWS graphs. Any spike in the system. Inbound/Outbound traffic.     
10> If any query provided - Explain plan of that query.

MySQL Database Security

MySQL DB Security
==================
a> Password maagement - OS/DB level  //Authentication
b> Access management - OS level  //Authorization
c> Privileges management - DB level   //Authorization

1> No test/blank database. And No user with '%' host or without password.
2> Backups should be encrypted.
   - Either using zip utility. It asks for password while taking backups.
   - Or using gpg utility. For that we've to create private/public key-pair first.
   - Or using openssl
3> No root/password storage management at OS level. And Password should be encrypted (if stored at some file).
4> History at OS level should be disabled. ~/.bashrc and ~/.bash_history
5> MySQL should be running at some OS level user (other than root)
   [mysqld]
   user=mysql
6> Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld.
7> Don't grant the FILE privilege to any non-administrative user. Else can write a file anywhere in the file system with the privileges of the mysqld daemon.
8> Do not grant the PROCESS or SUPER privilege to nonadministrative users. Else that can see the output of SHOW PROCESSLIST which shows the text of any statements currently being executed.
9> SSL/TLS encryption between (Client and Server) / (Master and Slave).
10> Database firewall. (To protect from SQL injection attacks).
11> Seperate DB user for DML/DDL operations. Let application connects to DB via dml_user. And let DDL changes been handled by some sys_user
12> DB Auditing (only in case of MySQL Enterprise). Or else use some 3rd party tools.

Find table locks and blocking transaction

The table locks and blocking transaction are a very common thing in any database system.
To find all locked and blocked transactions of MySQL Server:

SELECT
    pl.id
    ,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_query
FROM information_schema.processlist AS pl
INNER JOIN information_schema.innodb_trx AS it
ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
ON it.trx_id = ilw.requesting_trx_id
        AND it.trx_id = ilw.blocking_trx_id;

For more information on latest deadlocks and transaction locks, please refer:
mysql> show engine innodb status\G

References:
http://www.dbrnd.com/2016/02/mysql-script-to-identify-the-locks-and-blocking-transactions/

MongoDB Vs Cassandra

MongoDB Vs Cassandra
=====================

Cassandra
---------------
 - Wide-column store based on ideas of BigTable and DynamoDB
 - Cassandra has decentralized architecture. Any node can perform any operation. It provides AP(Availability,Partition-Tolerance) from CAP theorem.
 - Cassandra has excellent single-row read performance as long as eventual consistency semantics are sufficient for the use-case.
 - Cassandra does not support Range based row-scans which may be limiting in certain use-cases. Cassandra is well suited for supporting single-row queries, or selecting multiple rows based on a Column-Value index.
 - Aggregations in Cassandra are not supported by the Cassandra nodes - client must provide aggregations.
 - Best used: When you need to store data so huge that it doesn't fit on server, but still want a friendly familiar interface to it.
 - For example: Web analytics, to count hits by hour, by browser, by IP, etc. Transaction logging. Data collection from huge sensor arrays.
 - Real Usages: Twitter
 - Key characteristics:
     * High availability
     * Incremental scalability
     * Eventually consistent
     * Trade-offs between consistency and latency
     * Minimal administration
     * No SPF (Single point of failure) – all nodes are the same in Cassandra
     * AP on CAP
 - Good for:
     * Simple setup, maintenance code
     * Fast random read/write
     * Flexible parsing/wide column requirement
     * No multiple secondary index needed
 - Not good for:
     * Secondary index
     * Relational data
     * Transactional operations (Rollback, Commit)
     * Primary & Financial record
     * Stringent and authorization needed on data
     * Dynamic queries/searching  on column data
     * Low latency

MongoDB
--------------
 - It is a document oriented database.All data in mongodb is treated in JSON/BSON format.
 - It is a schema less database which goes over tera bytes of data in database.
 - It also supports master slave replication methods for making multiple copies of data over servers making the integration of data in certain types of applications easier and faster.
 - MongoDB combines the best of relational databases with the innovations of NoSQL technologies, enabling engineers to build modern applications.
 - MongoDB maintains the most valuable features of relational databases: strong consistency, expressive query language and secondary indexes. As a result, developers can build highly functional applications faster than NoSQL databases.
 - MongoDB provides the data model flexibility, elastic scalability and high performance of NoSQL databases. As a result, engineers can continuously enhance applications, and deliver them at almost unlimited scale on commodity hardware.
 - Full index support for high performance.
 - Best used: If you need dynamic queries. If you prefer to define indexes, not map/reduce functions. If you need good performance on a big DB. If you wanted CouchDB, but your data changes too much, filling up disks.
 - For example: For most things that you would do with MySQL or PostgreSQL, but having predefined columns really holds you back.
 - Real Usages: Craigslist, Foursquare
 - Key characteristics:
     * Schemas to change as applications evolve (Schema-free)
     * Full index support for high performance
     * Replication and failover for high availability
     * Auto Sharding for easy Scalability
     * Rich document based queries for easy readability
     * Master-slave model
     * CP on CAP
 - Good for:
     * RDBMS replacement for web applications
     * Semi-structured content management
     * Real-time analytics and high-speed logging, caching and high scalability
     * Web 2.0, Media, SAAS, Gaming
 - Not good for:
     * Highly transactional system
     * Applications with traditional database requirements such as foreign key constraints.

References:
https://www.linkedin.com/pulse/real-comparison-nosql-databases-hbase-cassandra-mongodb-sahu
https://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis?

Big Data - Overview

Big Data:
========
Big data is being generated by everything around us at all times. Every digital process and social media exchange produces it. Systems, sensors and mobile devices transmit it. Big data is arriving from multiple sources at an alarming velocity, volume and variety. To extract meaningful value from big data, you need optimal processing power, analytics capabilities and skills.

3 components of Big data:
---------------------------------
Big Data is a phrase used to mean a massive volume of both structured and unstructured data that is so large it is difficult to process using traditional database and software techniques.  In most enterprise scenarios the volume of data is too big or it moves too fast or it exceeds current processing capacity.

1> Volume. Organizations collect data from a variety of sources, including business transactions, social media and information from sensor or machine-to-machine data. In the past, storing it would’ve been a problem – but new technologies (such as Hadoop) have eased the burden.

2> Velocity. Data streams in at an unprecedented speed and must be dealt with in a timely manner. RFID tags, sensors and smart metering are driving the need to deal with torrents of data in near-real time.

3> Variety/Complexity. Data comes in all types of formats – from structured, numeric data in traditional databases to unstructured text documents, email, video, audio, stock ticker data and financial transactions.

Impact of Big Data:
--------------------------
Big data is changing the way people within organizations work together. It is creating a culture in which business and IT leaders must join forces to realize value from all data. Insights from big data can enable all employees to make better decisions—deepening customer engagement, optimizing operations, preventing threats and fraud, and capitalizing on new sources of revenue. But escalating demand for insights requires a fundamentally new approach to architecture, tools and practices.

 - Competitive Advantage: Data is emerging as the world's newest resource for competitive advantage.
 - Decision Making: Decision making is moving from the elite few to the empowered many.
 - Value of Data: As the value of data continues to grow, current systems won't keep pace.

References:
http://www.webopedia.com/TERM/B/big_data.html
https://www.ibm.com/big-data/us/en/
http://www.sas.com/en_us/insights/big-data/what-is-big-data.html

Database world - Various databases flavours

Various Databases flavours:
======================
RDBMS
-----------
Oracle
MySQL
MS SQL Server
PostgreSQL
DB2

5 categories of NoSQL Databases (Top DB of each category):
-------------------------------------------------------------------------------
Key Value Store - Redis
Wide Column Store - Cassandra
Document Store - Mongo DB
Graph - Neo4j
Search Engine - Elasticsearch

References:
http://db-engines.com/en/ranking

Online DDL

Online DDL
===========
The online DDL feature builds on the InnoDB Fast Index Creation feature that is available in MySQL 5.1 and MySQL 5.5. The InnoDB Fast Index Creation feature optimized CREATE INDEX and DROP INDEX to avoid table-copying behavior. The online DDL feature, introduced in MySQL 5.6, enhances many other types of ALTER TABLE operations to avoid table copying or blocking DML operations while DDL is in progress, or both.

The online DDL feature has the following benefits:
 - It improves responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
 - It lets you adjust the balance between performance and concurrency during the DDL operation, by choosing whether to block access to the table entirely (LOCK=EXCLUSIVE clause), allow queries but not DML (LOCK=SHARED clause), or allow full query and DML access to the table (LOCK=NONE clause). When you omit the LOCK clause or specify LOCK=DEFAULT, MySQL allows as much concurrency as possible depending on the type of operation.
 - Performing changes in-place where possible, rather than creating a new copy of the table, avoids temporary increases in disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.

Earlier:
--------
Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.

Now:
-----
MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL. This mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded. The online DDL enhancements in MySQL 5.6 improve many DDL operations that formerly required a table copy or blocked DML operations on the table, or both.

Online DDL improves several aspects of MySQL operation, such as performance, concurrency, availability, and scalability:
 - Because queries and DML operations on the table can proceed while the DDL is in progress, applications that access the table are more responsive. Reduced locking and waiting for other resources throughout the MySQL server leads to greater scalability, even for operations not involving the table being altered.
 - For in-place operations, by avoiding the disk I/O and CPU cycles to rebuild the table, you minimize the overall load on the database and maintain good performance and high throughput during the DDL operation.
 - For in-place operations, because less data is read into the buffer pool than if all the data was copied, you avoid purging frequently accessed data from memory, which formerly could cause a temporary performance dip after a DDL operation.

Algorithm Used:
---------------------
ALGORITHM=INPLACE
ALGORITHM=COPY

4 aspects of Online DDL:
--------------------------------
 - Algorithm=INPLACE (Preferred Value=Yes)
 - Copies Table (Preferred Value=No)
 - Allows Concurrent DML (Preferred Value=Yes)
 - Allows Concurrent Query (Preferred Value=Yes)

Note:
 - Any ALTER TABLE operation run with the ALGORITHM=COPY clause prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML).
 - Some other ALTER TABLE operations allow concurrent DML but still require a table copy.
    :: Adding, dropping, or reordering columns.
    :: Changing the nullable status for a column.
    :: Adding or dropping a primary key.
    :: OPTIMIZE TABLE
    :: Rebuilding a table with the FORCE option    

References:
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_online_ddl
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-concurrency.html

Tuning generic DB parameters

table_open_cache
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 2000

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.

#################################################################

table_open_cache_instances
===========================
Variable Scope: Global
Dynamic Variable: No
Type: Integer
Default: 1 or 16 (depedns upon MySQL version)

The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances . A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables. (DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.)

A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.

#################################################################

sort_buffer_size
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)

Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer
If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort.

#################################################################

read_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 131072 (128k)

Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072.
This option is also used in the following context for all storage engines:
 - For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
 - For bulk insert into partitions.
 - For caching results of nested queries.

#################################################################

read_rnd_buffer_size
=====================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256k)

This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.
When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

#################################################################

query_cache_size
================
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 1048576 (1M)

The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. To reduce overhead significantly, you should also start the server with query_cache_type=0 if you will not be using the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.

#################################################################

query_cache_type
=================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Enumeration
Default: 0

Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache.  This variable defaults to OFF.
If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

Options:
 - 0 or OFF :: Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
 - 1 or ON :: Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
 - 2 or DEMAND :: Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

#################################################################

tmp_table_size
==============
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 16777216 (16M)

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables. 
The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table.

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

#################################################################

join_buffer_size
================
Variable Scope: Global,Session
Dynamic Variable: Yes
Type: Integer
Default: 262144 (256K)

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. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

#################################################################

sync_binlog
===========
Variable Scope: Global
Dynamic Variable: Yes
Type: Integer
Default: 0 or 1

Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value greater than 0 this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed. Therefore, even in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state. This causes the server's automatic recovery routine to rollback those transactions. This guarantees that no transaction is lost from the binary log, and is the safest option. However this can have a negative impact on performance because of an increased number of disk writes. Using a higher value improves performance, but with the increased risk of data loss.

When sync_binlog=0 or sync_binlog is greater than 1, transactions are committed without having been synchronized to disk. Therefore, in the event of a power failure or operating system crash, it is possible that the server has committed some transactions that have not been synchronized to the binary log. Therefore it is impossible for the recovery routine to recover these transactions and they will be lost from the binary log.

Prior to MySQL 5.7.7, the default value of sync_binlog was 0, which configures no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. MySQL 5.7.7 and later use a default value of 1, which is the safest choice, but as noted above can impact performance.

#################################################################

References:
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

defragmentation of table

Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

One symptom of fragmentation is that a table takes more space than it “should” take.

All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%. Another symptom of fragmentation is that a table scan such as this takes more time than it “should” take.

To speed up index scans, you can periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table
 - ALTER TABLE tbl_name ENGINE=INNODB

Note:
 - You can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table.
 - As of MySQL 5.7.4, both ALTER TABLE tbl_name ENGINE=INNODB and ALTER TABLE tbl_name FORCE use online DDL (ALGORITHM=COPY).

References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html

Triggers

Tiggers:
------------

A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

- Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use the BEFORE keyword if you want to process action prior to the change is made on the table and AFTER if you need to process action after the change is made.
- The trigger event can be INSERT, UPDATE or  DELETE. This event causes the trigger to be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, you have to define multiple triggers, one for each event.
- A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after the ON keyword.

Before MySQL version 5.7.2, you can to define maximum six triggers for each table.
 - BEFORE INSERT – activated before data is inserted into the table.
 - AFTER INSERT – activated after data is inserted into the table.
 - BEFORE UPDATE – activated before data in the table is updated.
 - AFTER UPDATE – activated after data in the table is updated.
 - BEFORE DELETE – activated before data is removed from the table.
 - AFTER DELETE – activated after data is removed from the table.

Note: However, from MySQL version 5.7.2+, you can define multiple triggers for the same trigger event and action time.
 - Before MySQL version 5.7.2, you can only create one trigger for an event in a table e.g., you can only create one trigger for the BEFORE UPDATE or AFTER UPDATE event. MySQL 5.7.2+ lifts this limitation and allows you to create multiple triggers for the same event and action time in a table. The triggers will activate sequentially when the event occurs.
 - MySQL will invoke the triggers in the order that they were created. To change the order of triggers, you need to specify FOLLOWS or PRECEDES after the FOR EACH ROW clause.

Limitations:
----------------
 - The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.)
 - The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK. (ROLLBACK to SAVEPOINT is permitted because it does not end a transaction.).
 - Cannot Use SHOW, LOAD DATA, LOAD TABLE, BACKUP DATABASE, RESTORE, FLUSH and RETURN statements.

Note:  log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation.

References:
http://www.mysqltutorial.org/mysql-triggers.aspx
http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html