Category Archives: Oracle

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.
  2. .Set GTID_MODE to ON_PERMISSIVE
  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> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE; 
mysql> SHOW SLAVE STATUS\G;
### Once caught up set it back
mysql> set global gtid_mode=ON;

Thanks for visiting , please dont forget to leave you feedback

Regards

Raja Naveed

MySQL and memory Allocators malloc, tcmalloc and jemalloc

Memory Allocation in UNIX:

UNIX uses C dynamic memory allocation libraries for memory allocation. Namely malloc, realloc, calloc and free   are used.  Functions of these liberaries are given below.

 

Function Description
malloc allocates the specified number of bytes
realloc Increases or decreases the size of the specified block of memory. Reallocates it if needed
calloc allocates the specified number of bytes and initializes them to zero
free releases the specified block of memory back to the system

 

Best memory allocators other than above TCMALLOC and JEMALLOC.

Gperftools tcmalloc():

 

gperftools is Google Performance Tools which also provides malloc() called tcmalloc. Works particularly well with threads and Standard Template Library (STL). gperftools is thread-friendly heapchecker, heap –profiler and cp-profiler. TCMALLOC is available in repository for more details please visit https://github.com/gperftools

 

JEMALLOC : 

jemalloc is a general purpose malloc(3) implementation that emphasises fragmentation avoidance and scalable concurrency support   jemalloc first came into use as the FreeBSD libc allocator in 2005, and since then it has found its way into numerous applications that rely on its predictable behaviour.  In 2010 jemalloc development efforts broadened to include developer support features such as heap profiling and extensive monitoring/tuning hooks.  Modern jemalloc releases continue to be integrated back into FreeBSD, and therefore versatility remains critical.  Ongoing development efforts trend toward making jemalloc among the best allocators for a broad range of demanding applications, and eliminating/mitigating weaknesses that have practical repercussions for real world applications.

How to install tcmalloc:

yum  list gperftools-libs 

yum -y install gperftools-libs

Now you will see few new libraries:

bash-4.2$ rpm -ql gperftools-libs

/usr/lib64/libprofiler.so.0

/usr/lib64/libprofiler.so.0.4.14

/usr/lib64/libtcmalloc.so.4

/usr/lib64/libtcmalloc.so.4.4.5

/usr/lib64/libtcmalloc_and_profiler.so.4

/usr/lib64/libtcmalloc_and_profiler.so.4.4.5

/usr/lib64/libtcmalloc_debug.so.4

/usr/lib64/libtcmalloc_debug.so.4.4.5

/usr/lib64/libtcmalloc_minimal.so.4

/usr/lib64/libtcmalloc_minimal.so.4.4.5

/usr/lib64/libtcmalloc_minimal_debug.so.4

/usr/lib64/libtcmalloc_minimal_debug.so.4.4.5

 

How to install jemalloc:

yum list jemalloc

yum install jemalloc.x86_64

 

Now you will see few new libraries:

 

bash-4.2$ rpm -ql jemalloc

/usr/bin/jemalloc.sh

/usr/lib64/libjemalloc.so.1

/usr/share/doc/jemalloc-3.6.0

/usr/share/doc/jemalloc-3.6.0/COPYING

/usr/share/doc/jemalloc-3.6.0/README

/usr/share/doc/jemalloc-3.6.0/VERSION

/usr/share/doc/jemalloc-3.6.0/jemalloc.html

How and why to use tcmalloc or jemalloc with MySQL :

I will refer to used case. One of MySQL 5.6 instance I have been managing was struggling with memory leaks. MySQL have been using swap with all optimum recommended settings. As one of last resort I decided to use different memory allocation method and results were amazing.  First take a look at swap usage with tcmalloc and with default malloc .

With default malloc()

with TCMALLOC()

Other benefits includes reduced sort activity , smooth i/o activity , less table level locks , low temp table usage , positive memory usage on MyISAM tables for meta data dictionary as show below.  I also noticed the boost in performance and transactions as well because MySQL instance started generating more binary logs.

how to configure tcmalloc or jemalloc with MySQL:

there are two ways you can  make MySQL use tcmalloc or jemalloc once libraries are installed.

  •  configure library in my.cnf file under [mysqld_safe] as shown below.
[mysqld_safe]
#malloc settings
malloc-lib=/usr/lib64/libtcmalloc.so.4.4.5

or

[mysqld_safe]
#malloc settings
malloc-lib=/usr/lib64/libjemalloc.so.1
  • second method is to start MySQL directly using desired library on command line as show below
LD_PRELOAD=/usr/lib64/libtcmalloc.so.4.2.6 mysqld --defaults-file=/etc/my.cnf --daemonize

or

LD_PRELOAD=/usr/lib64/libjemalloc.so.1 mysqld --defaults-file=/etc/my.cnf --daemonize

 

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

Thanks and Regards

Raja M Naveed

Deprecated variables in MySQL 8

The following system variables, status variables, and options have been deprecated in MySQL 8.0.

Deprecated :

expire_logs_days: Purge binary logs after this many days.

Added:

binlog_expire_logs_seconds: Sets the binary log expiration period in seconds.This is to replace the expire_logs_days.

Deprecated :

innodb_undo_tablespaces: The number of tablespace files that rollback segments are divided between. Deprecated as of MySQL 8.0.4.

Added:

No alternative added.

log_syslog: Whether to write error log to syslog. Deprecated as of MySQL 8.0.2.symbolic-links: Permit symbolic links for MyISAM tables. Deprecated as of MySQL 8.0.2.Symbolic link support, along with the the –symbolic-links option that controls it, is deprecated and will be removed in a future version of MySQL. In addition, the option is disabled by default. The related have_symlink system variable also is deprecated and will be removed in a future version of MySQL

Added:

No Alternative added

 

 

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

Thanks and Regards

Raja M Naveed

 

 

MySQL 8 Invisible Indexes

Invisible Indexes

Invisible indexes is new feature added in MySQL 8.0. This feature can help us to mark an index as   unavailable for use by optimizer. This means that index will still be maintained and kept  up to date  in metadata dictionary as data is modified. These marked indexes are not permitted to be used by optimizer even by INDEX hint.

Indexes are visible by default. To control index visibility for a new index or existing one  VISIBLE OR INVISIBLE keywords are used.

for example: If we have following table with index j_idx

CREATE TABLE t1 ( 

i INT, 

j INT, 

k INT, 

INDEX i_idx (i) INVISIBLE 

                                 ) ENGINE = InnoDB; 

CREATE INDEX j_idx ON t1 (j) INVISIBLE; 

ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

To change the visibility of existing  index we can use following statements.

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; 

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

Information about index, if it is visible or invisible can be extracted from INFORMATION_SCHEMA.STATISTICS as shown below.

mysql> SELECT INDEX_NAME, IS_VISIBLE 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1'; 

+----------------+---------------+ 
| INDEX_NAME     | IS_VISIBLE | 
+----------------+---------------+ 
| i_idx          | YES           | 
| j_idx          | NO            | 
| k_idx          | NO            | 
+----------------+---------------+

Invisible Indexes can help us possible to test the effect of removing an index for query performance, without dropping or recreating index which are expensive operations.

Primary Key Index cannot be made invisible , if you try to do that you will get error as shown below

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; 

ERROR 3522 (HY000): A primary key index cannot be invisible.

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

Thanks and Regards

Raja M 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.
Regards
Raja Naveed

Read files using arrays in Bash script

Read files using arrays in Bash script

I have following file with some numeric contents in it

bash-4.2$ cat /tmp/araytest.txt
1 2
3 4
5 6
7 8
9 10
11 12
13 14

If we would like save the file contents in Array. We can using following method.

 while read LINE
 do
 ARRAY+=("$LINE")
 done < <(cat /tmp/araytest.txt)

Now we can use another Loop to read it line by line for further manipulation as show below.

for i in "${ARRAY[@]}"; do
echo $i
done

Therefore full shell script should look like show below

#!/bin/bash

while read LINE
 do
 ARRAY+=("$LINE")
 done < <(cat /tmp/araytest.txt)

for i in "${ARRAY[@]}"; do
echo $i
done

Save it into file i-e testArray.sh and execute it as show below

bash-4.2$ ./testArray.sh
1 2
3 4
5 6
7 8
9 10
11 12
13 14

 

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

 

 

 

EMD upload error: Upload was successful but collections currently disabled – disk full

EMD upload error: Upload was successful but collections currently disabled – disk full

#Cause

  • The EMD disk system shows used percent at 99%.
  • The agent default for space required for upload files is 98%. The collections will stop when the space on the disk is beyond that.

if you find above error then do following

#Solution 1

 Delete old trc/ trm files from
OB/diag/rdbms/oracle_sid_DIRECTORY/ORACLE_SID_DIRECTORY/trace/.
Remember do not delete alert_ORACLESID.trc
now do following steps

1. emctl stop agent
2. emctl start agent
3. emctl upload

you should get following msg.

Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload completed successfully

#Solution 2

To implement the solution, please execute the following steps:
1. Update the AGENT OH/sysman/config/emd.properties with the parameter:
uploadMaxDiscUsedPct=99
uploadMaxDiskUsedPctFloor=99
2. Stop the agent: emctl stop agent
3. Start the agent: emctl start agent
4. Upload to the OMS: emctl upload

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

if you try
emctl upload  and get above error then do following
emctl stop dbconsole
go to directory $ORACLE_HOME/hostname_SID/sysman and do following steps
2. delete following files using these commands.
rm -r sysman/emd/state/*
rm -r sysman/emd/collection/*
rm -r sysman/emd/upload/*
rm sysman/emd/lastupld.xml
rm sysman/emd/agntstmp.txt
rm sysman/emd/blackouts.xml
rm sysman/emd/protocol.ini
3. emctl secure dbconsole
(reset passord of sysman user if needed)
4. emctl start dbconsole