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