Tag Archives: MySQL

Skip Slave Counter with GTID_MODE ON #ERROR 1858 (HY000)

With generic asynchronous MySQL Replication we often use skip_slave_counter to tackle / skip anonymous transactions which creates errors or halt MySQL Replication. But if we try to skip slave counter whilst GTID_MOD is ON we get following error.

 ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON.  

In this case we should do following steps

  1. Stop Replication.
  3. Skip slave counter.
  4. Start slave.
  5. Set GTID_MODE to ON once it catches the master.

GTID_MODE is dynamic variable which can be set on the run. Here we need to look at GTID_MODE following options.

  • OFF: Both new and replicated transactions must be anonymous.
  • OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
  • ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
  • ON: Both new and replicated transactions must be GTID transactions.

All these steps are shown below

mysql> stop slave;
mysql> set global gtid_mode=ON_PERMISSIVE; 
mysql> START SLAVE; 
### Once caught up set it back
mysql> set global gtid_mode=ON;

Thanks for visiting , please dont forget to leave you feedback


Raja Naveed

Reduce startup and shutdown time by using warm up innodb buffer pool

Reduce startup and shutdown time by using warm up innodb buffer pool

For database servers having large memory utilisation and have big innodb_buffer_pool_size , it takes longer to stop and start the instance. This is because more data and indexes stored in huge  innodb_buffer_pool. This data and indexes are used by queries running on the server prior to go to the disk  if not found in innodb_buffer_pool. So bigger the buffer pool more time it will take to shutdown. One way of reducing the time is using warm up innodb buffer pool by dumping the state of buffer and reusing it when starting up.

We can do it by turning innodb_buffer_pool_dump_at_shutdown on. In normal restart , innodb_buffer_pool gets empty and after restart it take time to warm up again.  By using innodb_buffer_pool_dump_at_shutdown we can use pre- warmed up innodb_buffer_pool which can reduce the time significantly. It is dynamic variable and can but turned on / off online and we can add it in the my.cnf file as permanent change.

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1 / ON

or we can add it in my.cnf as shown under


Now if we shutdown the server,  ib_buffer_pool dump file will be created under the MySQL datadir which can be configured to be created on different location and file name by using innodb_buffer_pool_filename. This file will store current state and data of innodb buffer pool.

Now we want server to use this dump file when it will start again. We can do it by using following entry in my.cnf file.


After restarting the server we can check ib_buffer_pool file created in data directory and also we can view the contents as well as shown  below.

bash-4.2$ ls ib_buffer_pool

bash-4.2$ less ib_buffer_pool

I hope this will help you all, Don’t forget to like, share and comment

Thanks and Regards

Raja M Naveed

Fine tune MySQL for Better performance

Few issues I came across when dealing with MySQL performance. Let us go through these problems one by one.

Swap usage:

MySQL loves memory as almost all databases do but MySQL tries to utilise all  the possible available memory. In order to restrict MySQL to use allocated memory resources we should implement following:-

  •  Swapiness

Swappiness is the kernel parameter that defines how much (and how often) your Linux kernel will copy RAM contents to swap. This parameter’s default value is “60” and it can take anything from “0” to “100”. The higher the value of the swappiness parameter, the more aggressively your kernel will swap.

Action: set swapiness to 1 as shown below

bash-4.2$ sysctl vm.swappiness=1

bash-4.2$  cat /proc/sys/vm/swappiness
  • innodb_buffer_pool_size

Allocate 60% of the total memory to MySQL instance. It is configured via configuration file my.cnf. For Example if we have 100G of RAM then we will allocate 60G to MySQL. We can reduce or increase percentage of memory allocation as per needs but increase in memory up to certain limit allocation can cause aggressive swap operations.

innodb_buffer_pool_size= 60G

  • Huge Pages

Hugepages is a mechanism that allows the Linux kernel to utilize the multiple page size capabilities of modern hardware architectures. Linux uses pages as the basic unit of memory, where physical memory is partitioned and accessed using the basic page unit. The default page size is 4096 Bytes in the x86 architecture. Hugepages allows large amounts of memory to be utilized with a reduced overhead. Linux uses “Translation Lookaside Buffers” (TLB) in the CPU architecture. These buffers contain mappings of virtual memory to actual physical memory addresses. So utilizing a huge amount of physical memory with the default page size consumes the TLB and adds processing overhead.

Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.

Before large pages can be used on Linux, the kernel must be enabled to support them and it is necessary to configure the HugeTLB memory pool. For reference, the HugeTBL API is documented in the Documentation/vm/hugetlbpage.txt file of your Linux sources.

Please click here to see how to implement huge pages

  • Metadata states persistence

Metadata or database stats should be kept up to date by performing optimize operations and by keeping innodb_persistent_stats =1 / ON . Optimisation of tables can be difficult when table is very large therefor we should implement data partitions for large tables. Optimisation not only updates the stats but it re-organises the indexes as well and helps regain the free storage.

  • I/O capacity

If there are enough cpus and memory available then we should consider to increase number of  innodb_write_io_threads and innodb_read_io_threads from (4 default ) to 8 or 16. This can make huge impact on performance.

  • innodb_flush_log_at_trx_commit

Controls the balance between strict atomicity, consistency, isolation, and durability compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash.

Keeping value to 2 means we can loose 1 sec worth of data as commits happens every sec regardless of size of transactions / redo but can improve i/o operations.

  • innodb_flush_method  

                      innodb_flush_method=O_DIRECT is recommended for OS caching for storage engine.  This will not use OS file cache.  

  • Partitions

Always use time range partitions to manage data deletions as it helps the big data to keep meta data stats up to date. It also helps indexing and data retrieval very fast.


Thanks for reading . Don’t forget to leave your feed backup.


Raja Naveed

Oracle VS MySQL file architecture

Oracle VS MySQL file architecture

For  Oracle database administrator, when it comes to learn new technology it always starts from comparison with respect to physical or logical architecture. Physical file architecture  comparison is given below , I hope it will be very useful for Oracle or MySQL database administrators who want to learn either of these databases.

Oracle database consist of following files

  • init. ora

Parameter file for database instance. It contains information about location of data, archive, redo and other log files. It also contains memory settings for the instance and remote locations.

  • Control file

It holds the information of each and every data file, redo log file locations and backup information starting time and ending time

  • Data file

It stores the data which includes user data and undo data. Extension of these files is “.dbf”. Names format and location of these files are defined in init.ora file.

  • Redo log file

It is part of an oracle database. It’s the main purpose is to recover the database. It’s extension “.log”

  • Archive log

It is a group of redo log files to one or more offline destinations, known collectively as the archived redo log. These logs contains all the transactions including redo statements which are applied to or can be applied to the database.

MySQL Database consists of following files:-

  • my.cnf

Parameter file for database instance. It contains information about location of data, archive, redo and other log files. It also contains memory settings for the instance and information about remote host for replication of data.

  • mysql-bin.index

This file contains information binary logs (archive logs in oracle called bin logs in MySQL). This information is stored in control file in oracle.

  • Redo log file

In MySQL ib_logfile0 and ib_logfile1 are called redo log files all transactions are stored before they are committed to disk. These transactions are translated into binary logs after commit. We can have more than 2 files in more than 1 groups as well.

  • Binary log ( archived logs in oracle)

These logs are same as archived logs in oracle. These files contains all the transactions or rollback statements committed to the database / disk. These files are translated into relay logs on remote destination for data replication but in oracle archived logs are created on remote destination to be applied on standby databases.


Thanks for visiting , dont forget to leave your feedback.
Raja Naveed