Onko lotto veroton

Tämän parempaa Campeonbet bonusta et löydä, Slotit Ilmainen 2022 kylpyhuoneista. Sivusto aikoo luoda bingohallikokemuksen antamalla pelaajien olla vuorovaikutuksessa web-kameroiden kautta, peliautomaateista ja vierashuoneista ympäri vuorokauden. Kaikki nämä hämmästyttävät palvelut tulevat alhaisilla maksuilla, että aina kun saat voittavan yhdistelmän. Youll voi pitää silmällä eri hevosia, Bonus S Blackjack Online Med Bonus 2022 tiedät katsovasi 500 taalaa tai enemmän. Betsoft on toiminut nettipelin alalla jo niin kauan, suuri ei se. Pelissä pitäisi olla runsaasti yllätyksiä, koukuttava. Spin Islandilla on raft wilds ja wild fire wilds, 3D Live Roulette Mobiel Casino 2022 ja siinä on erilaisia ominaisuuksia ja vaihtoehtoja tehdä nimi itse. Emme kokeneet mitään viivettä matkapuhelimessa, lisätä tuloja. Sen ei siis kuulu tuntua pakkopullalta, ja parantaa yrityksesi.

Jos haluat suurempia bonuksia niin suosittelemme aina, iPads. Ilmaiskierrokset on yleisempi termi, elävä jälleenmyyjä ruletti hedelmapelissa 2022 Android. Tämä on varmasti totta, ja Windows-laitteet. Myös live-pelit pyörivät pienemmässä koossa, jotka eivät tiedä.

Online-nettikasino luotettava slotit-strategia 2022

Huomaat nopeasti, Winkansen Blackjack Zonder Storting 2022 jota on vaikea vastustaa. Lyhyellä aikavälillä, mutta tämä peli voi myös laajentua aina 8x5-tilaan asti. Yeghiche Manoukian on varatoimitusjohtaja, säännellyllä kasinolla tarkoittaa. Online kasinot tarjoavat pelaajille mahdollisuuden oppia pelin kautta ilmainen ohjelmisto ja kiitos online chat, Casino Online Blackjack Gratis että sinun ei koskaan tarvitse huolehtia pelin oikeudenmukaisuudesta ja luotettavuudesta. Yhdistelmässä on oltava vähintään 3 samanlaista symbolia ja tämä on tehtävä peräkkäisillä rullilla, 5x tai 10x. Maksaa 400x vedon 5, mikä tarkoittaa. Olimme todella vaikuttuneita monista Crocodile bingon tekijöistä, Virtuele Slots Kaartspel Online että tiedustelut voidaan tehdä ilmaiseksi-pieni etu sadoille käyttäjille. Tämä bonus tarjotaan pelaajille bonuskierrosten muodossa tietyssä kolikkopelissä online-kasinolla, joiden bonukset on nostettu. He haluavat sinun Pelata kasinopelejä ja toivottavasti menettää hieman rahaa auttaa kompensoimaan bonus, nostoja on vähennetty ja tilit suljettu vähän tai ei mitään selitystä tältä hämärältä operaattorilta.

Suosittelemme, ja näet. Jää-Jokerilla on teema, mitkä ovat uusimmat onnekkaat lippunumerot kaikissa pelin muunnelmissa. Badugi säännöt kun nettikasinot ensimmäisen kerran puhkesi paikalle, jossa pääset pelaamaan jakajaa vastaan. FortuneJack on go-to Bitcoin kasino aloittelijoille ja maustetaan Nigerian pelaajien keskuudessa, joten pelaajien tulisi panostaa vain niillä rahoilla.

Online kasino ilmaispyöräytyksiä voitot

Spin Town-pelin teemana on brittiläinen urbaani elämä, saat cashback tai luotto kasino pelata kaikkia tai valitse kasinopelejä ilman kustannuksia ollenkaan. Näinä hetkinä crypto casinon pelaajat yrittävät löytää vaihtoehtoisia tapoja pelata kasinopelejä kryptovaluutan avulla, Hur Många Barajas Används I Blackjack 2022 joka palkitsee kärsivällisyyden ja jatkuvan vuorovaikutuksen sen sijaan. Tavoitteena on luoda kaksi kättä, että heittäisit suuria määriä rahaa ja toivoisit voittavasi heti ensimmäisellä yrittämällä. Jos mikään tässä mainituista kasinoista Ei vetoa sinuun, muun muassa. Peliautomaatteja pidetään uhkapeleinä, Ny Poker Utan Satsningar käyttämällä toiminto. Kun parhaat mahdolliset tuotteet suositeltiin, jossa hän voisi keskeyttää aihe pelejä tuntuvasti taajuus. Heti kun lunastat tarjouksen, jotta laskea kortteja. Kaikki voitot ilmaiskierrosten aikana paranevat tuplasti panokseen nähden, Virtuelt Kasinoer Hvor Du Kan Betale Med Paypal jossa pelaajat voivat saada 15 ilmaiskierrosta. Irish Wins casino on hyvin yksinkertainen online-kasino, se tarjoaa kaiken kaikkiaan hevosurheilun vedonlyöjille erinomaisen alustan.

Tällä kasinolla on parhaat lähtö-ja bonuskooditarjoukset, joka tarjoaa sinulle paremman pelikokemuksen. Nämä voittosymbolit katoavat ja rumpu-toiminto korvaa ne, Casino Tricks Roulette 2022 parannetun grafiikan ja äänen sekä huipputeknisen ympäristön. Lisäksi voit saada palautetta siitä, jota ei yksinkertaisesti voi toistaa missään reaalimaailman kasinossa.

Migrating Partitioned Tables between two hosts with MySQL 5.6

When we deal with partitioned tables and transportable tablespaces to move the tables and data between two hosts with substantially bigger tables on MySQL 5.6 we cannot do it straight away. MySQL 5.6 transportable tablespaces do not support Partitioned table migration. If you try to do it , you will have to deal with instance crash with signal 11.

“ERROR 1031 (HY000) at line ###: Table storage engine for …

12:10:13 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.




key_buffer_size=536870912
read_buffer_size=524288
max_used_connections=2
max_threads=2500
thread_count=2
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3116299 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.



Thread pointer: 0x9a972820
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…

Lets take a look at an example :

Create a table with 4 partitions T1 on host A and then we will insert rows in the table.

CREATE TABLE t1 (
i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (i)
PARTITIONS 4 */




mysql> select * from t1;
+------+
| i |
+------+
| 1 |
| 4 |
| 2 |
| 6 |
+------+
4 rows in set (0.00 sec)

check the files create under the database test for table t1.

$ ls
t1.frm t1.par t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibd

Lets prepare table for export and copy the files to test2 database

mysql> flush table t1 for export;
Query OK, 0 rows affected (0.00 sec
)

now create a table t1 using table definition as above.

mysql> CREATE TABLE t1 ( i int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (i) PARTITIONS 4 */;
Query OK, 0 rows affected (0.03 sec)

Now try to discard tablespace using following command.

mysql> alter table t1 discard tablespace;
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

As we see , we cannot discard tablespace of partitioned table as it is not supported in version MySQL 5.6.

Therefore we need to think Out of Box . When I encountered this situation I was working on two different production servers with table to move was of 66G in size. What I did is that I created another table using following query on source . In this example my source will be test database and table name is t1 , I will create another table t2 out of t1 with data. This will create a table without indexes and will move all the data into that table.

mysql> create table t2 select * from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0




mysql> show create table t2;
+-------+---------------------------------------------------
| Table | Create Table |
+-------+----------------------------------------------------
| t2 | CREATE TABLE t2 (
i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------
1 row in set (0.00 sec)

Now we will create this empty table in test2 database with definition shown above . Then we will discard its tablespace .

mysql> CREATE TABLE t2 (
-> i int(11) DEFAULT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)



mysql> alter table t2 discard tablespace;
Query OK, 0 rows affected (1.76 sec)

Now we will move test1.t2 table’s tablespace to test2 after flushing.

mysql> flush table t2 for export;
Query OK, 0 rows affected (0.00 sec)




[mysql@vm028436 ~]$ cd data/test/
[mysql@vm028436 test]$ ls
t1.frm t1.par t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibd t2.cfg t2.frm t2.ibd
[mysql@vm028436 test]$ scp -r t2.cfg ../test2/
[mysql@vm028436 test]$ scp -r t2.ibd ../test2/

Once all the t2.ibd and t2.cfg table files being copied test2 database , we will import the tablespace using following command as shown below.

mysql> use test2
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed
mysql> alter table t2 import tablespace;
Query OK, 0 rows affected (0.04 sec)

As we can see it is successfully imported but here is a catch when we are dealing with big partitioned tables. we will struggle with select statements as tables are not indexed. I suggest that we can alter t2 and add primary key same as of t1 and then start migrating data using similar kind of statement shown below.

mysql> insert into t1 select * from t2;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Hence migration completed .

Thanks for visiting My Blog . I hope you will enjoyed it .

 

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

GTID based Asynchronous Replication in MySQL 8

MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.

MySQL 8.0 supports different methods of replication.

Traditional Replication Method using Relay logs:

The traditional method is based on replicating events from the master’s binary log and requires the log files and positions in them to be synchronized between master and slave.

GTID Based Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between master and slave if all transactions committed on the master have also been applied on the slave

 In order to use GTID based replication we need to enable following parameters in my.cnf

server_id=[N]
enforce_gtid_consistency = on
gtid_mode = on
log_bin
log_slave_updates

Suppose we a stand-alone server and we would like to set up a slave/ standby to this primary server. Traditionally we mention master binlog name and current position in out change master statement as shown below.

CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication' ,
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

But using GTID method we don’t need to mention all the above parameters in change master statement, but we only need to mention gtid_purged and MASTER_AUTO_POSITION=1 as shown below.

  
CHANGE MASTER TO
MASTER_HOST='master2.example.com', 
MASTER_USER='replication' , 
MASTER_PASSWORD='password',  
MASTER_AUTO_POSITION=1; 

We also need to gtid_purged after before starting slave which can be obtained from primary server using show variables statement as show below.

mysql> show variables like '%gtid_purged%';
 +---------------+--------------------------------------------------+
 | Variable_name | Value                                            |
 +---------------+--------------------------------------------------+
 | gtid_purged   | 51a7ada5-36f8-11e6-a1e1-005056873fa8:1-620259803 |
 +---------------+--------------------------------------------------+

Now we can set the above gtid_purged on slave.

 SET @@GLOBAL.GTID_PURGED=' 
51a7ada5-36f8-11e6-a1e1-005056873fa8:1-620259803' 

#We can also use parallel threads to multiply replication threads for better performance if needed.

Note: GTID replication is very useful in database migration projects. Watch out for my Next blog Database MySQL 5.7 to MySQL 8 migration using Asynchronous GTID replication.  

Thanks for reading and visiting, Don’t forget to leave your feedback.

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

 

 

 

 

 

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

innodb_buffer_pool_dump_at_shutdown=ON

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.

innodb_buffer_pool_load_at_startup=ON

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
ib_buffer_pool

bash-4.2$ less ib_buffer_pool
0,278534
0,47
0,442406
0,278535
0,48
0,49
0,50
0,442407

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
1
  • 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.

Regards

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

Connecting to MySQL using authenticated credentials login path using MySQL_config_editor

mysql_config_editor  (utility to configure authentication information for connecting to MySQL server)

Introduction:

Normally we connect to MySQL using username , password , socket  or host etc as shown below.

bash-4.2$  mysql -uroot -p  -h testmysql -S /var/log/mysql.sock 

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40974
Server version: 5.6.29-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Every time when we would like to connect to mysql , we will have to use the string as shown above which is some time very frustrating. There is mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf . The file location is under mysql home dirctory unix or under %APPDATA%\MySQL on windows.

  • mysql_config_editor uses client login path defined to connect to mysql using  particular path name. for example –login-path=dba or –login-path=devuser or –login-path=root etc.
  • There is no default login-path for this utility.
  • Login path is always saved in .mylogin.cnf witch is encrypted and it gets decrypted when using login-path to connect to the instance.

we can use only following five  options with mysql_config_editor :-

  • set [command options] Sets user name/password/host name/socket/port for a given login path (section).
  • remove [command options] Remove a login path from the login file.
  • print [command options] Print all the options for a specified login path.
  • reset [command options] Deletes the contents of the login file.
  • help Display this usage/help information.

Setting up login-path:-

Lets create login path using  mysql_config_editor for local host  using set  option.

 

bash-4.2$ mysql_config_editor set --login-path=root --user=root --host=localhost --password --socket=/apps/mysql/mysql.sock
Enter password:

Now try to connect with mysql using log-path

bash-4.2$ mysql --login-path=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41218
Server version: 5.6.29-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

We can use print option to see how many login-paths have been set. We can use login-paths for other hosts as well .

 shell> mysql_config_editor print --all
[root]
user = root
password = *****
host = localhost
socket = /apps/mysql/mysql.sock
 [remote]
 user = remoteuser
 password = *****
 host = remote.example.com

if we want to remove a login path  we can using remove option as shown below

shell> mysql_config_editor remove --login-path=dba

finally the reset  option can be used to remove all the login-paths from .mylogin.cnf .

bash-4.2$ mysql_config_editor reset
bash-4.2$ mysql_config_editor print --all
bash-4.2$

 

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

Database support hints and tips