Skip to main content

Posts

Showing posts from 2017

MySQL database configuration for Galera Replication | Galera cluster | Multi Master Replication

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.wsrep_node_address="192.168.0.1"

#configura…

MySQL | Maria DB Galera Cluster Concepts | Galera Cluster Components

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 workloadNo 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 - Gale cluster is transparent to applicationNo splitting - No read and write node, all nodes are read and write, so no splitting is r…

MySQL Percona Tool Kit

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.cnf h=host1
Diff the [mysqld] section …

My SQL Daily SQL

Get list of tables with particular column
SELECT DISTINCT TABLE_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME NOT REGEXP "^test_.*(_archive)|(_new)|(_old)$" AND
TABLE_NAME REGEXP "^test_.*" AND
COLUMN_NAME = '<column_name>' AND
TABLE_SCHEMA ='<schema_name>'

Find number of connections:
SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Find if some table is locked:
SHOW OPEN TABLES WHERE
`Table` LIKE '%[TABLE_NAME]%' AND
`Database` LIKE '[DBNAME]' AND In_use > 0;

Find all foreign keys on a table:
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = '<table_name>';

Oracle Gather Schema | Table Statistics | Extended Statistics | method_opt

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 Ref.:
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 254 <column_name> FOR ALL IND…

Retrive Exadata Version and Configuration Information

Option 1: Use DBMS_LOB package to get cell configuration information, as column CONFVAL is CLOB
spool confval.txt and search for text make in text file you will see get value in tag <makeModel>  </makeModel>

set pagesize 0
SELECT dbms_lob.substr(CONFVAL,4000,1) from V$CELL_CONFIG;
spool off

This option is good if you don't  have privilege to access storage node and can't execute command Cellcli. You would be able to find out interleaving attribute about Celldisk also as follows.

<interleaving>none</interleaving>

Explore about interleaving attribute of Celldisk at

https://uhesse.com/2011/05/18/exadata-part-vii-meaning-of-the-various-disk-layers/amp/
http://basededonnyes.blogspot.com/2012/01/creating-interleaved-grid-disks.html?m=1


Option 2:
From Tanel Podder Blog. I have modified some format to display Make Model properly.
COL cv_cellname       HEAD CELL_NAME        FOR A30
COL cv_cell_path      HEAD CELL_PATH        FOR A30
COL cv_cellversion    HEAD CE…

Oracle ADRCI - Automatic Diagnostic Repository Command Interpreter

adrci - Automatic Diagnostic Repository Command Interpreter

$ adrci
adrci> show homes
Set home for Oracle Database:
adrci> set home
List incidents:
adrci> show incidents
Pack incident for incident number:
adrci> ips pack incident <incident_no> in /tmp
Show incident information in brief:
adrci> show incident -mode brief -p "incident_id=<incident_no>"
Pack incident for problem key (Get problem key by commnad show incidents):
adrci> ips create package problemkey "ORA 600 [qmtGetBaseType]"
Pack incident by time (Get time record by commnd show incidents):
adrci> IPS CREATE PACKAGE TIME '2017-08-23 11:31:44.296000 -07:00' to '2017-08-22 08:32:16.394000 -07:00'

Ref.: http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBICJED


Drop Tuning Set of Oracle Database

Tuning Set Name : Test_TOP_SQL_1503437315950

select name from dba_sqlset;
select * from dba_sqlset where name like 'Test%';
Dropping SQL Task
select name from dba_sqlset;
execute dbms_sqltune.drop_sqlset('Test_TOP_SQL_1503437315950');
You could get an error, if it is reference to advisory task
Get advisory task name
select description, created, owner  from DBA_SQLSET_REFERENCES  where sqlset_name = 'Test_TOP_SQL_1503437315950';
task: SQL_TUNING_1503437363054
select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = 'SQL_TUNING_1503437363054';
execute dbms_sqltune.drop_tuning_task('SQL_TUNING_1503437363054');
execute DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'Test_TOP_SQL_1503437315950');
If you can't drop SQL Set then check whether record exist in work load repository or not
SELECT count(*)  FROM wri$_sqlset_definitions a, wri$_sqlset_references b  WHERE a.name = 'Test_TOP_SQL_1503437315950'  …

Smart scan for ASM disk group in Exadata

Enable smart scan for ASM Disk group while creating disk group in Exadata

SQL> CREATE DISKGROUP data HIGH REDUNDANCY
DISK 'o/*/DATA*'
ATTRIBUTE 'AU_SIZE' = '4M',
          'content.type' = 'data',
          'cell.smart_scan_capable'='TRUE',
          'compatible.rdbms'='11.2.0.2',
          'compatible.asm'='11.2.0.3';

Ref.: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-administering-asm.htm#SAGUG20526

Check cell smart scan is enables for ASM disk or not:

 Make sure to connect ASM instance, if you connect to DB instance you won't find value.

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a
WHERE dg.group_number = a.group_number and a.NAME = 'cell.smart_scan_capable';

EXADATA - Importance of Cellinit.ora and Cellip.ora files
Cellinit.ora and Cellip.ora
After Oracle Exadata Storage Server is configured, …

Kfod tool to inspect ASM disk in Exadata environment

Oracle kfod command line tool for ASM disk inspection:

kfod tool is located in grid home's bin directory.

List optinos for kfod toll using

$kfod -h
_asm_a/llow_only_raw_disks              KFOD allow only raw devices [_asm_allow_only_raw_disks=(TRUE)/FALSE]
_asm_l/ibraries         ASM Libraries[_asm_libraries=lib1,lib2,...]
_asms/id                ASM Instance[_asmsid=sid]
_b/oot          Running in pre-install env (boot=TRUE/FALSE)
_f/lexinfo              Provide flexinfo      (_flexinfo=TRUE/FALSE)
_p/atch_lib             Patchlib [_patch_lib=<asmclntsh_path>]
_u/ser          OS Username
asm_/diskstring         ASM Diskstring [asm_diskstring=discoverystring, discoverystring ...]
asmc/ompatibility               Include diskgroup ASM compatibility [asmcompatibility=TRUE/(FALSE)]
cli/ent_cluster         client cluster name
clus_/version           cluster version
clust/er                KFOD cluster [cluster=TRUE/(FALSE)]
db_/unique_name         db_unique_name for ASM instance…