Posts

MySQL router kubernetes deployment

-
How to setup MySQL router in Kubernetes cluster? Continuing from previous post - MySQL router high availability for InnoDB cluster , in situations where application is deployed in kubernets it is better to incorporate mysql router to K8s itself. Inorder to deploy Mysql router in kubernetes two services are required along with the mysql router deployment. MySQL Router deployment Router deployment should listen on two ports for read write and read only connections. hostAliases are used to define the backend innodb cluster database servers. MySQL router docker image is used with variables - MYSQL_HOST, MYSQL_PORT, MYSQL_USER and MYSQL_PASSWORD. MYSQL_HOST variables should point to MySQL service defined in next section. mysql_router_deployment.yaml apiVersion: apps/v1 kind: Deployment metadata: name: mysql-router namespace: default spec: replicas: 1 selector: matchLabels: app: mysql-router template: metadata: labels: app: mysql-router
Post a Comment
Read more

MySQL router high availability for InnoDB cluster

-
Image
How to setup MySQL router with High availability for InnoDB cluster? MySQL router deployed as part of an Innodb cluster must be able to withstand any failures as it acts as the middle man between application and database cluster. There are two options when installing the routers, Install the MySQL routers on application servers, so the routers are a part of application availability it self. MySQL recommends this as the default installation method in the router FAQ section as well. Ref -  https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-faq.html#faq-mysql-router-how-install Install MySQL routers on separate servers. Option two can be used based on the specific design consideration of a given deployment. As an example there are some deployments where router servers are deployed in separate network segment from databases clusters and application servers. When routers are deployed separately it is a must that they are highly available. No matter running a multi node Innodb
Post a Comment
Read more

Jenkins MySQL - auto apply database changes from SCM

-
A database change apply step for Jenkins pipeline? When designing for the fully automated deployment pipeline, databases tend to become the bottleneck more often than not. Starting with the complications of storing database changes in the SCM to applying the actual changes with availability and rollback in consideration can be challenging. Given the uniqueness of each deployment coming up with a single solution for this type of requirement is practically impossible. Having this mind, we will try to come up with a simple set of process and a supporting bash script that can help to automate the database change apply process. This particular solution was mainly proposed as an automation step for development environment where multiple dev teams are changing the same databases backend. This requires some discipline from the developers while committing the code to the SCM as well. The particular implementations is based on Jenkins MySQL and GitLab as SCM. The Idea In a step wise timel
Post a Comment
Read more

MySQL InnoDB Cluster in 15 minutes

-
Image
What is MySQL innodb cluster MySQL innodb cluster is a high availability solutions provided by MySQL which includes both database service auto failover and Client auto failover. It utilize multiple technologies underneath to ensure its availability targets. Group Replication -- Ensure distributed consensus, Make sure all member of the cluster agrees same state transaction wise. MySQL Shell -- Provide cluster maintenance and monitoring interfaces. MySQL Router -- Manges client failovers, provide a single access point for clients. Setting up innodb cluster Although a complete solution, quickly setting up cluster at least for testing purposes can be challenging using the normal process. This is mainly because it involves different technologies and the requirement of setting up at least 3 nodes. Hence we can use below Vagrant and Virtual box based solution to setup MySQL innodb cluster rapidly also with the reusability. InnoDB cluster using Vagrant and Virtual box Inst
1 comment
Read more

MySQL check query execution time, number of executions - Using general log

-
Image
How to get MySQL Query Time using general log data Quickly finding the query execution time can be very helpful during scenarios such as load tests. Below are simple set of steps to extract the query time , number of execution based on general log. Set general_log and log_output parameters, this should log all queries to mysql.general_log table SET global general_log = 1; SET global log_output = 'table'; Create a table with id column included to general log table CREATE TABLE `test.general_log_id` ( id MEDIUMINT NOT NULL AUTO_INCREMENT, `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumblob NOT NULL, PRIMARY KEY (id) ) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='General log'; Filter out the data from mysql.general_log and insert t
Post a Comment
Read more

MySQL int max value

-
Image
What is MySQL int max value? MySQL int max value is 2147483647 when signed and of 4294967295 when unsigned. Although rare, there are situations where auto increment columns does reach MySQL int 11 max value specially when signed. Comparison of MySQL int data types and there max values MySQL provide five int data types, TINYINT - 1 byte - max 127 signed and 255 unsigned  SMALLINT  - 2 bytes - max 32767 signed and 65535 unsigned  MEDIUMINT - 3 bytes - max 8388607 signed and 16777215 unsigned  INT (11) - 4 bytes - max 2147483647 signed and 4294967295 unsigned BIGINT - 8 bytes - max 2 63 - 1 and 2 64 - 1 unsigned First three int types can be used when the highest value is predictable, else it is always safe to go for MySQL int 11 or bigint. Bigint does provide huge limit advantage over int 11 , compromising the fixed storage space usage of  2 times (8 bytes compared to 4 bytes) which can cause access overheads as well. Script to identify auto increment columns reaching MySQL max int va
Post a Comment
Read more

Solaris Linux iostat in DBAs view

-
Image
Background When troubleshooting  any database performance issue, we need to understand that it can happen in two possible sequence, 1. Additional large utilization (load) on database server causing the delay in normal database operation. Which can be from newly added application component, query that is running abnormally (e.g plan change),  new parallel execution etc.. 2. A change or suboptimal operation on underlying hardware/software layer that causes the default load of the database to take more time than it usually does. Here database system is not the culprit but the victim. It is trying its best to perform as it used to, but a bottleneck in different layer that is not in the control of database is causing the delay. Example Below is a demonstration using iostat command in Solaris environment running Oracle database, which shows the metric differences while database system is experiencing performance delays. In both situation the result is the same, distorted db performance wher
Post a Comment
Read more