MySQL

    It’s a well know fact that we could take backups with almost zero downtime with the “mysqldump” tool provided with the community edition of MySQL with a magical option --single-transaction on one  condition that all the tables are InnoDB. Myisam tables locked even with single transaction option.   Now let us talk about the MySQL Enterprise backup tool that is only available with the Enterprise edition. We are also offered a 30 day trial. It has been said that MEB(let us just call the MySQL Enterprise backup, MEB)  provides hot online backups with zero downtime.   So a hot backup is done online and doesn’t disrupt the normal operations of the database and it even captures the database operations happening during the backup process and applies it during the restoration process. This is applicable only to InnoDB tables.   When it comes to other engine tables like Myisam, CSV it has to read lock the tables.. So this is pretty much similar to mysqldump with --single-tran

A few days back we were given a MySQL database hosted in EC2 with around 5TB data to move some of the tables to a new server.  The intention was to share the tables between 2 EC2 instances. Since AWS had the option to take online snapshots, the plan was to take a snapshot, create a new machine with that snapshot and drop the unwanted tables. So everything went as planned until creating a new machine with the snapshot.  The real challenge was dropping the unwanted tables.  It took around 4 minutes to Drop a table whose size is 20GB.  It took 20 minutes to drop a 100GB table. The time kept on increasing for larger tables. MySQL even went to “defunct” when we killed the drop query and at times crashed. To track down this issue we executed drop table in one session and checked the processlist from another session that gave the below output. mysql> show processlist \G *************************** 1. row ***************************      Id: 2352    User: dbuser    Hos

Choosing the right backup solution is most important for any database. People always choose a backup solution that has the below qualities  a) Minimal Downtime  b) Consistent data  c) Less restoration time There are several backup solutions like mysqldump, mysqlhottcopy, Xtrabackup, raw copy using cp/rsync/scp (and of course MySQL Enterprise backup. we are not going to talk about it here) etc.., I always prefer mysqldump when the data size is small(few GBs) even if it locks the tables. But what if the data size is huge in TB's, you cannot bring down the DB for a whole day. No client is comfortable with this, they need minimal or no downtime. Percona's Xtrabackup actually comes in handy in such situations. Xtrabackup locks the tables only when dumping the meta data.                 But what we are really interested here is LVM snapshot. With LVM snapshots we could actually take backups with zero downtime. Requirements  a) Disk should be partitioned with LVM  b) Al

What I love about Galera cluster is it's simplicity, SST/IST protocol and the native MySQL feel. SST(state transfer protocol) is nothing but a mechanism used by Galera to transfer data between the nodes. Galera supports 3 types of SST’s                1)       mysqldump                2)       rsync                3)       xtrabackup( Xtrabackup should be installed separately) Xtrabackup is the most preferred one with less downtime since it locks only when the schema is fetched. There are lots of superb features in Galera that we could talk about, but we shall skip that and concentrate on what the topic is about Practically speaking I wouldn't recommend running multiple Galera nodes in a single machine. But you might run into a situation where you want to create a development environment and you have a single machine. The procedure that I am going to explain here might be handy in such situations. Every Galera reference manual instructs us to go for a mi

At some point of time every DBA must have come across a situation where he has to enable the general log to debug an issue or even to capture all the queries hitting the DB. In such situations what most DBA’s do is inform the stakeholders that he is  enabling the general log and this might cause a drop in performance and in turn the stakeholders ask a question like “What will the percentage of performance drop?  Can you give us a number?” The easy answer is “It is difficult to provide a number”.  I had wanted to address this, but never really made an effort until I saw the below blog post “PERFORMANCE_SCHEMA VS SLOW QUERY LOG”  http://www.mysqlperformanceblog.com/2014/02/11/performance_schema-vs-slow-query-log/ In that particular blog Peter explains why he chose to use slow query log to capture slow queries instead of using the PERFORMANCE SCHEMA. At some point he talks about the overhead of enabling the PERFORMANCE SCHEMA. He used sysbench tool to calculate the overhead and pro

I am writting my first ever blog post after four and a half years since I started my career as a MySQL DBA. I used to read lots of blog posts  especially from mysqlperformceblog , but  never really had the interest of writting one.  But this interesting question from a client made me write this blog post. “Why is my MySQL consuming only “core 0”, while I have 8 cores and there is hardly any usage in the other cores?” This is a question put forward by one of our clients. Started my investigation by looking into the cpu usage stats. It was true that only “core 0” was getting used and the other cores were rarely getting used.  Usage of “core 0” was like below Where as there is hardly any usage in the other cores as shown below But if we look at the overall cpu usage it rarely crossed 30%. But the client questioned “why can’t MySQL share the usage in ‘core 0’ with other cores?” In my first look found that the machine is a dedicated for MySQL with multi-thread