AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Configure MySQL Database parameters for Galera replication in /etc/my.cnf file: #Defines the path to the Galera Replication Plugin. wsrep_provider=/usr/lib/libgalera_smm.so #Semicolon (;) separated list of wsrep options, #https://mariadb.com/kb/en/library/wsrep_provider_options/ wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" # logical name for your cluster. You must use the same name for every node in your cluster. wsrep_cluster_name="my_cluster" #IP addresses of nodes in a comma separated list. wsrep_cluster_address="gcomm://node1,node2,node3" #method or script the node uses in a State Snapshot Transfer, mysqldump, rsync, #rsync_wan, xtrabackup,wsrep_%.sh For instance, giving the node a transfer method of #MyCustomSST causes it to look for wsrep_MyCustomSST.sh in /usr/bin wsrep_sst_method=rsync #logical name for the individual node. wsrep_node_name=node1 #set the IP address for the individual node. w srep_node_address="192.168.0...

Features of Galera Cluster: Multi-master - Galera cluster is real multi master, all the nodes are available for read and write operation. Synchronous replication - Replication in Galera cluster is synchronous. There is no slave, so there is no slave lag, either write to all node or all roll back. Tightly coupled - All nodes in Galera cluster are coupled tightly. Data on all node is same. Performance - Galera cluster support multi threaded to balance workload No failover - As there is no slave so there is not master slave failover. Hot standby - Standby nodes are always available and have same data, so there is hot standby. Automatic node provisioning - While adding new node to cluster family there is no need to manually back up the database, no need to restore backup to new node. InnoDB - Galera cluster supports only InnoDB engine. Transparent to application - Galera cluster is transparent to application No splitting - No read and write node, all nodes are read a...

Kill queries running longer than 60s: pt-kill --busy-time 60 --kill Print, do not kill, queries running longer than 60s: pt-kill --busy-time 60 --print Print all login processes: pt-kill --match-state login --print --victims all Find all tables created more than a day ago, which use the MyISAM engine, and print their names:  pt-find --ctime +1 --engine MyISAM Find InnoDB tables and convert them to MyISAM: pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM" Find empty tables in the test and junk databases, and delete them: pt-find --empty junk test --exec-plus "DROP TABLE %s" Find tables more than five gigabytes in total size: pt-find --tablesize +5G Summarize MySQL information nicely: pt-mysql-summary --user=root Diff MySQL configuration files and server variables: Diff host1 config from SHOW VARIABLES against host2: pt-config-diff h=host1 h=host2 Diff config from [mysqld] section in my.cnf against host1 config: pt-config-diff /etc/my.cn...

Disables locking while copying the non-InnoDB files: [mysqlbackup] no-locking After issuing FLUSH TABLES WITH READ LOCK: We can see one of the following status: Flushing tables Waiting for table flush Waiting for global read lock Pending FLUSH TABLES WITH READ LOCK because connection has: executed LOCK TABLES ... WRITE; for one or more tables a long running query (including DDL, simple SELECT, etc.) Identify Queries Taking Longer Than 10 Seconds: SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10; SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10; Flush MySQL Host: flush hosts; Flush MySQL bin logs: flush bin logs; Select current date and time: s...

Select Histograms for tables:- SELECT column_name, num_distinct, histogram FROM   user_tab_col_statistics WHERE  table_name = '<table_name>'; Gather tables stats using DBMS_STATS for schema exec dbms_stats.gather_schema_stats ( ownname => '<owner_name>', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt  => 'FOR ALL COLUMNS SIZE 1', cascade => true, degree => 10, granularity => 'ALL' ); exec dbms_stats.gather_schema_stats ( ownname=> 'SAMAPPL', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade=> true, granularity=>'ALL', degree => 10 ); Extended statistics R ef.: https://blogs.oracle.com/optimizer/extended-statistics http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm        method_opt options FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE ...