Author: MinervaDB

What is MySQL partitioning ?

MySQL partitioning makes data distribution of individual tables (typically we recommend partition for large & complex I/O table for performance, scalability and manageability) across multiple files based on partition strategy / rules. In very simple terms, different portions of table are stored as separate tables in different location to distribute I/O optimally. The user defined division of data by some rule is known as partition function, In MySQL we partition data by RANGE of values / LIST of values / internal hashing function / linear hashing function. By restricting the query examination on the selected partitions by matching rows increases the query performance by multiple times compared to the same query on a non partitioned table, This methodology is also called partition pruning (trimming of unwanted partitions), Please find below example of partition pruning:
CREATE TABLE tab1 (
col1 VARCHAR(30) NOT NULL,
col2 VARCHAR(30) NOT NULL,
col3 TINYINT UNSIGNED NOT NULL,
col4 DATE NOT NULL
)
PARTITION BY RANGE( col3 ) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Write a SELECT query benefitting partition pruning:
SELECT col1, col2, col3, col4
FROM tab1
WHERE col3 > 200 AND col3 < 250;
What is explicit partitioning in MySQL and how is it different from partition pruning ? 
In MySQL we can explicitly select partition and sub-partitions when executing a statement matching a given WHERE condition, This sounds very much similar to partition pruning, but there is a difference:

Partition to be checked are explicitly mentioned in the query statement, In partition pruning it is automatic.
In explicit partition, the explicit selection of partitions is supported for both queries and DML statements, partition pruning applies only to queries.
SQL statements supported in explicit partitioning – SELECT, INSERT, UPDATE, DELETE, LOAD DATA, LOAD XML and REPLACE

Explicit partition example:
CREATE TABLE customer (
cust_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cust_fname VARCHAR(25) NOT NULL,
cust_lname VARCHAR(25) NOT NULL,
cust_phone INT NOT NULL,
cust_fax INT NOT NULL
)
PARTITION BY RANGE(cust_id) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Query explicitly mentioning partition:
mysql> SELECT * FROM customer PARTITION (p1);
RANGE partitioningIn RANGE partitioning you can partition values within a given range, Ranges should be contiguous but not overlapping, usually defined by VALUES LESS THAN operator, The following examples explain how to create and use RANGE partitioning for MySQL performance:
CREATE TABLE customer_contract(
cust_id INT NOT NULL,
cust_fname VARCHAR(30),
cust_lname VARCHAR(30),
st_dt DATE NOT NULL DEFAULT ‘1970-01-01’,
end_dt DATE NOT NULL DEFAULT ‘9999-12-31’,
contract_code INT NOT NULL,
contract_id INT NOT NULL
)
PARTITION BY RANGE (contract_id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (200)
);
For example, let us suppose that you wish to partition based on the year contract ended:
CREATE TABLE customer_contract(
cust_id INT NOT NULL,
cust_fname VARCHAR(30),
cust_lname VARCHAR(30),
st_dt DATE NOT NULL DEFAULT ‘1970-01-01’,
end_dt DATE NOT NULL DEFAULT ‘9999-12-31’,
contract_code INT NOT NULL,
contract_id INT NOT NULL
)
PARTITION BY RANGE (year(end_dt)) (
PARTITION p0 VALUES LESS THAN (2001),
PARTITION p1 VALUES LESS THAN (2002),
PARTITION p2 VALUES LESS THAN (2003),
PARTITION p3 VALUES LESS THAN (2004)
);
It is also possible to partition a table by RANGE, based on the value of a TIMESTAMP column, using the UNIX_TIMESTAMP() function, as shown in this example:
CREATE TABLE sales_forecast (
sales_forecast_id INT NOT NULL,
sales_forecast_status VARCHAR(20) NOT NULL,
sales_forecast_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(sales_forecast_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2008-01-01 00:00:00’) ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2008-04-01 00:00:00’) ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2008-07-01 00:00:00’) ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2008-10-01 00:00:00’) ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2009-01-01 00:00:00’) ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2009-04-01 00:00:00’) ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2009-07-01 00:00:00’) ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2009-10-01 00:00:00’) ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2010-01-01 00:00:00’) ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
LIST partitioningThe difference between RANGE and LIST partitioning is: In LIST partitioning, each partition is grouped on the selected list of values of a specific column. You can do it by PARTITION BY LIST (EXPR) where EXPR is the selected column for list partition, We have explained LIST partitioning with example below:
CREATE TABLE students (
student_id INT NOT NULL,
student_fname VARCHAR(30),
student_lname VARCHAR(30),
student_joined DATE NOT NULL DEFAULT ‘1970-01-01’,
student_separated DATE NOT NULL DEFAULT ‘9999-12-31’,
student_house INT,
student_grade_id INT
)
PARTITION BY LIST(student_grade_id) (
PARTITION P1 VALUES IN (1,2,3,4),
PARTITION P2 VALUES IN (5,6,7),
PARTITION P3 VALUES IN (8,9,10),
PARTITION P4 VALUES IN (11,12)
);
HASH partitioningHASH partitioning makes an even distribution of data among predetermined number of partitions, In RANGE and LIST partitioning you must explicitly define the partitioning logic and which partition given column value or set of column values are stored. In HASH partitioning MySQL take care of this, The following example explains HASH partitioning better:
CREATE TABLE store (
store_id INT NOT NULL,
store_name VARCHAR(30),
store_location VARCHAR(30),
store_started DATE NOT NULL DEFAULT ‘1997-01-01’,
store_code INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
P.S. : If you do not include a PARTITIONS clause, the number of partitions defaults to 1.
LINEAR HASH partitioningThe LINEAR HASH partitioning utilizes a linear powers-of-two algorithm, Where HASH partitioning employs the modulus of the hashing function’s value. Please find below LINEAR HASH partitioning example:
CREATE TABLE store (
store_id INT NOT NULL,
store_name VARCHAR(30),
store_location VARCHAR(30),
store_started DATE NOT NULL DEFAULT ‘1997-01-01’,
store_code INT
)
PARTITION BY LINEAR HASH( YEAR(store_started) )
PARTITIONS 4;
KEY partitioningKEY partitioning is very much similar to HASH, the only difference is, the tasing function for the KEY partitioning is supplied by MySQL, In case of MySQL NDB Cluster, MD5() is used, For tables using other storage engines, the MySQL server uses the storage engine specific hashing function which os based on the same algorithm as PASSWORD().
CREATE TABLE contact(
id INT NOT NULL,
name VARCHAR(20),
contact_number INT,
email VARCHAR(50),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 5;
P.S. – if the unique key column were not defined as NOT NULL, then the previous statement would fail.
SubpartitioningSUBPARTITIONING  is also known as composite partitioning, You can partition table combining RANGE and HASH for better results, The example below explains SUBPARTITIONING better:
CREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)
PARTITION BY RANGE( YEAR(purchase_date) )
SUBPARTITION BY HASH( TO_DAYS(purchase_date) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2010),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
It is also possible to define subpartitions explicitly using SUBPARTITION clauses to specify options for individual subpartitions:
CREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)
PARTITION BY RANGE( YEAR(purchase_date) )
SUBPARTITION BY HASH( TO_DAYS(purchase_date) ) (
PARTITION p0 VALUES LESS THAN (2000) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2010) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
Things to remember:

Each partition must have the same number of subpartitions.
Each SUBPARTITION clause must include (at a minimum) a name for the subpartition. Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option.
Subpartition names must be unique across the entire table. For example, the following CREATE TABLE statement is valid in MySQL 5.7:

CREATE TABLE purchase (id INT, item VARCHAR(30), purchase_date DATE)
PARTITION BY RANGE( YEAR(purchase_date) )
SUBPARTITION BY HASH( TO_DAYS(purchase_date) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
MySQL partitioning limitationsMySQL partitioning also has limitations, We are listing down below the limitations of MySQL partitioning:
A PRIMARY KEY must include all columns in the table’s partitioning function:
CREATE TABLE tab3 (
column1 INT NOT NULL,
column2 DATE NOT NULL,
column3 INT NOT NULL,
column4 INT NOT NULL,
UNIQUE KEY (column1, column2),
UNIQUE KEY (column3)
)
PARTITION BY HASH(column1 + column3)
PARTITIONS 4;
Expect this error after running above script – ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
The right way of doing it:
CREATE TABLE table12 (
column1 INT NOT NULL,
column2 DATE NOT NULL,
column3 INT NOT NULL,
column4 INT NOT NULL,
UNIQUE KEY (column1, column2, column3)
)
PARTITION BY HASH(column3)
PARTITIONS 5;

CREATE TABLE table25 (
column11 INT NOT NULL,
column12 DATE NOT NULL,
column13 INT NOT NULL,
column14 INT NOT NULL,
UNIQUE KEY (column11, column13)
)
PARTITION BY HASH(column11 + column13)
PARTITIONS 5;
Most popular limitation of MySQL – Primary key is by definition a unique key, this restriction also includes the table’s primary key, if it has one. The example below explains this limitation better:
CREATE TABLE table55 (
column11 INT NOT NULL,
column12 DATE NOT NULL,
column13 INT NOT NULL,
column14 INT NOT NULL,
PRIMARY KEY(column11, column12)
)
PARTITION BY HASH(column13)
PARTITIONS 4;

CREATE TABLE table65 (
column20 INT NOT NULL,
column25 DATE NOT NULL,
column30 INT NOT NULL,
column35 INT NOT NULL,
PRIMARY KEY(column20, column30),
UNIQUE KEY(column25)
)
PARTITION BY HASH( YEAR(column25) )
PARTITIONS 5;
Both of the above scripts will return this error – ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
The right way of doing it:
CREATE TABLE t45 (
column50 INT NOT NULL,
column55 DATE NOT NULL,
column60 INT NOT NULL,
column65 INT NOT NULL,
PRIMARY KEY(column50, column55)
)
PARTITION BY HASH(column50 + YEAR(column55))
PARTITIONS 5;

CREATE TABLE table88 (
column80 INT NOT NULL,
column81 DATE NOT NULL,
column82 INT NOT NULL,
column83 INT NOT NULL,
PRIMARY KEY(column80, column81, column82),
UNIQUE KEY(column81, column82)
);
In above example, the primary key does not include all columns referenced in the partitioning expression. However, both of the statements are valid !
You can still successfully partition a MySQL table without unique keys – this also includes having no primary key and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type, The example below shows partitioning a table with no unique / primary keys:
CREATE TABLE table_has_no_pk (column10 INT, column11 INT, column12 varchar(20))
PARTITION BY RANGE(column10) (
PARTITION p0 VALUES LESS THAN (500),
PARTITION p1 VALUES LESS THAN (600),
PARTITION p2 VALUES LESS THAN (700),
PARTITION p3 VALUES LESS THAN (800)
);
You cannot later add a unique key to a partitioned table unless the key includes all columns used by the table’s partitioning expression, The example below explains this much better:
ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column10);

ALTER TABLE table_has_no_pk drop primary key;

ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column10,column11);

ALTER TABLE table_has_no_pk drop primary key;
However, the next statement fails, because column10 is part of the partitioning key, but is not part of the proposed primary key:
mysql> ALTER TABLE table_has_no_pk ADD PRIMARY KEY(column11);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
mysql>
MySQL partitioning limitations (at storage engine level)
InnoDB

InnoDB foreign keys and MySQL partitioning are not compatible, Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys, So you cannot partition InnoDB tables which have or referenced by foreign keys.
InnoDB does not support use of multiple disks for subpartition (MyISAM supports this feature)

Use ALTER TABLE … REBUILD PARTITION and ALTER TABLE … ANALYZE PARTITION than using ALTER TABLE … OPTIMIZE PARTITION

NDB storage engine 

We can only partition by KEY (including LINEAR KEY) in NDB storage engine.

FEDERATED storage engine 

Partitioning not supported in FEDERATED storage engine.

CSV storage engine

Partitioning not supported in CSV storage engine.

MERGE storage engine 

Tables using the MERGE storage engine cannot be partitioned. Partitioned tables cannot be merged.

MySQL functions shown in the following list are allowed in partitioning expressions:

ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT()
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP()
WEEKDAY()
YEAR()
YEARWEEK()

MySQL partitioning and locks 
Effect on DML statements

In MySQL 5.7, updating a partitioned MyISAM table cause only the affected partitioned to be locked.
SELECT statements (including those containing unions or joins) lock only those partitions that actually need to be read. This also applies to SELECT …PARTITION.
An UPDATE prunes locks only for tables on which no partitioning columns are updated.
REPLACE and INSERT lock only those partitions having rows to be inserted or replaced. However, if an AUTO_INCREMENT value is generated for any partitioning column then all partitions are locked.
INSERT … ON DUPLICATE KEY UPDATE is pruned as long as no partitioning column is updated.
INSERT … SELECT locks only those partitions in the source table that need to be read, although all partitions in the target table are locked.
Locks imposed by LOAD DATA statements on partitioned tables cannot be pruned.

Effect on DML statements

CREATE VIEW does not cause any locks.
ALTER TABLE … EXCHANGE PARTITION prunes locks; only the exchanged table and the exchanged partition are locked.
ALTER TABLE … TRUNCATE PARTITION prunes locks; only the partitions to be emptied are locked.
In addition, ALTER TABLE statements take metadata locks on the table level.

Effect on other statements

LOCK TABLES cannot prune partition locks.
CALL stored_procedure(expr) supports lock pruning, but evaluating expr does not.
DO and SET statements do not support partitioning lock pruning.

 
The post What is MySQL partitioning ? appeared first on MySQL Consulting, Support and Remote DBA Services.

How to use mysqlpump for faster MySQL logical backup ?

MySQL 5.7.8 introduced much improved version of mysqldump, It’s called “mysqlpump”, mysqlpump is much faster than mysqldump with parallel threads capabilities, There are many other compelling reasons for choosing mysqlpump over mysqldump, This blog is about how mysqlpump can be used for good. mysqlpump is relatively a new utility of MySQL and we are confident that Oracle MySQL will invest more to make mysqlpump efficient, we haven’t recommended mysqlpump in production for any of our customers till date, considering several concerns. The following below are mysqlpump features we are really excited about:

Supports parallel MySQL logical backup, The resource usage efficiency and high performance backups (we love it !)
Much better orchestration possible – You can backup selected databases, tables, stored programs and user accounts etc.
By default mysqlpump will not backup performance_schema, sys schema, ndbinfo by default, You have to name them with –databases or –include-databases option
mysqlpump does not dump INFORMATION_SCHEMA schema.
Faster secondary indexes creation, The indexes created only after inserting rows ! 

mysqlpump examples 
Plain simple backup using mysqlpump: 
[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees > employeebakup$(date ‘+%Y-%m-%H-%M-%S’).sql
Enter password:
Dump progress: 1/4 tables, 0/630999 rows
Dump progress: 2/6 tables, 541250/3919384 rows
Dump progress: 4/6 tables, 1306627/3919384 rows
Dump progress: 5/6 tables, 2128435/3919384 rows
Dump progress: 5/6 tables, 3081685/3919384 rows
Dump completed in 5309 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#
Using mysqlpump based backup with 6 threads: 
[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees –default-parallelism=6 > employeebakup$(date ‘+%Y-%m-%H-%M-%S’).sql
Enter password:
Dump progress: 0/5 tables, 250/3477363 rows
Dump progress: 2/6 tables, 606250/3919384 rows
Dump progress: 3/6 tables, 1272103/3919384 rows
Dump progress: 5/6 tables, 2028185/3919384 rows
Dump progress: 5/6 tables, 2932185/3919384 rows
Dump progress: 5/6 tables, 3864185/3919384 rows
Dump completed in 5503 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#
Using mysqlpump to backup only selected databases, spawned 5 threads to backup employee and sakila database:
[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p employees –parallel-schemas=5:employees,sakila –default-parallelism=6 > bakup$(date ‘+%Y-%m-%H-%M-%S’).sql
Enter password:
Dump progress: 1/6 tables, 0/3919384 rows
Dump progress: 2/6 tables, 635250/3919384 rows
Dump progress: 3/6 tables, 1354353/3919384 rows
Dump progress: 5/6 tables, 2219935/3919384 rows
Dump progress: 5/6 tables, 3066185/3919384 rows
Dump completed in 5279 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#
Using mysqlpump to backup selected database and schema:
[root@localhost mysqlpump2018-06-23-25-49]# mysqlpump -u root -p –databases employees.titles > emp.titles$(date ‘+%Y-%m-%H-%M-%S’).sql
Enter password:
Dump completed in 437 milliseconds
[root@localhost mysqlpump2018-06-23-25-49]#
Restore backup from mysqlpump
Both mysqldump and mysqlpump generate MySQL logical backup in .SQL file so restoration is quiet an straightforward process.
The post How to use mysqlpump for faster MySQL logical backup ? appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

MariaDB Audit Plugin

MariaDB DBAs are accountable for auditing database infrastructure operations to proactively troubleshoot performance and operational issues, MariaDB Audit Plugin is capable of auditing the database operations of both MariaDB and MySQL. MariaDB Audit Plugin is provided as a dynamic library: server_audit.so (server_audit.dll for Windows).  The plugin must be located in the plugin directory, the directory containing all plugin libraries for MariaDB.
MariaDB [(none)]> select @@plugin_dir;
+————————–+
| @@plugin_dir |
+————————–+
| /usr/lib64/mysql/plugin/ |
+————————–+
1 row in set (0.000 sec)
There are two ways you can install MariaDB Audit Plugin:
INSTALL SONAME statement while logged into MariaDB, You need to use administrative account which has INSERT privilege for the mysql.plugin table.
MariaDB [(none)]> INSTALL SONAME ‘server_audit’;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]>
Load Plugin at Start-Up 
The plugin can be loaded by setting -plugin_load system variable in my.cnf (my.ini in windows)
[mysqld]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

plugin_load=server_audit=server_audit.so
System variables to configure MariaDB Audit Plugin
MariaDB Audit Plugin is highly configurable, Please fine below the system variables available for MariaDB Audit Plugin:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%server_audit%’;
+——————————-+———————–+
| Variable_name | Value |
+——————————-+———————–+
| server_audit_events | |
| server_audit_excl_users | |
| server_audit_file_path | server_audit.log |
| server_audit_file_rotate_now | OFF |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_logging | OFF |
| server_audit_mode | 0 |
| server_audit_output_type | file |
| server_audit_query_log_limit | 1024 |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql-server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+——————————-+———————–+
15 rows in set (0.001 sec)
configure system variable server_audit_events for auditing MariaDB transaction events:
MariaDB [(none)]> SET GLOBAL server_audit_events = ‘CONNECT,QUERY,TABLE’;
Query OK, 0 rows affected (0.008 sec)
Enable MariaDB Audit Plugin
MariaDB [(none)]> set global server_audit_logging=on;
Query OK, 0 rows affected (0.007 sec)
MariaDB Audit Plugin creates audit log file “server_audit.log” on path  /var/lib/mysql/ 
Testing MariaDB Audit Plugin 
MariaDB [employees]> update employees
-> set last_name=’Gupta’
-> where emp_no= 499999;
Query OK, 1 row affected (0.010 sec)
Rows matched: 1 Changed: 1 Warnings: 0

[root@localhost mysql]# tail -f server_audit.log
20180612 20:32:07,localhost.localdomain,root,localhost,16,433,QUERY,,’SHOW GLOBAL VARIABLES LIKE \’%server_audit%\”,0
20180612 20:32:26,localhost.localdomain,root,localhost,16,434,QUERY,,’update employees set last_name=\’Gupta\’ where emp_no= 499999′,1046
20180612 20:32:37,localhost.localdomain,root,localhost,16,435,QUERY,,’SELECT DATABASE()’,0
20180612 20:32:37,localhost.localdomain,root,localhost,16,437,QUERY,employees,’show databases’,0
20180612 20:32:37,localhost.localdomain,root,localhost,16,438,QUERY,employees,’show tables’,0
20180612 20:32:41,localhost.localdomain,root,localhost,16,447,WRITE,employees,employees,
20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_emp,
20180612 20:32:41,localhost.localdomain,root,localhost,16,447,READ,employees,dept_manager,
20180612 20:32:41,localhost.localdomain,root,localhost,16,447,QUERY,employees,’update employees set last_name=\’Gupta\’ where emp_no= 499999′,0
How can we block UNINSTALL PLUGIN ?
The INSTALL PLUGIN statement can be used to uninstall a plugin but you can disable this by adding following line in my.cnf after plugin is loaded once:
[mysqld]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

plugin_load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
 
The post MariaDB Audit Plugin appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

MariaDB 10.3 support Oracle mode sequences

Sequences are used to requesting unique values on demand, The best use case of sequences is to have a unique ID. , that can be used across multiple tables. In some cases sequences are really helpful to have an identifier before an actual row is inserted. With the normal way of having an automatically incrementing identifier, the identifier value will only be available after insert of the row and the identifier will only be unique inside its own table. MariaDB Server 10.3 follows the standard and includes compatibility with the way Oracle does sequences introduced in Oracle Database Server on top of the standard.
Simple steps to create a sequence in MariaDB 10.3 onwards, a create statement is used:

MariaDB [MDB101]> CREATE SEQUENCE Seq1_100
-> START WITH 100
-> INCREMENT BY 1;
Query OK, 0 rows affected (0.015 sec)

This creates a sequence that starts at 100 and is incremented with 1 every time a value is requested from the sequence. The sequence will be visible among the tables in the database, i.e. if you run SHOW TABLES it will be there. You can use DESCRIBE on the sequence to see what columns it has.
To test out the usage of sequences let’s create a table:

MariaDB [MDB101]> CREATE TABLE TAB1 (
-> Col1 int(10) NOT NULL,
-> Col2 varchar(30) NOT NULL,
-> Col3 int(10) NOT NULL,
-> PRIMARY KEY (Col1)
-> );
Query OK, 0 rows affected (0.018 sec)
Since we want to use sequences this time, we did not put AUTO_INCREMENT on the Col1 column. Instead we will ask for the next value from the sequence in the INSERT statements:
MariaDB [MDB101]> INSERT INTO TAB1 (Col1, Col2, Col3) VALUES (NEXT VALUE FOR Seq1_100, ‘India’, 10);
Query OK, 1 row affected (0.011 sec)

MariaDB [MDB101]> INSERT INTO TAB1 (Col1, Col2, Col3) VALUES (NEXT VALUE FOR Seq1_100, ‘Jakarta’, 20);
Query OK, 1 row affected (0.008 sec)

MariaDB [MDB101]> INSERT INTO TAB1 (Col1, Col2, Col3) VALUES (NEXT VALUE FOR Seq1_100, ‘Singapore’, 20);
Query OK, 1 row affected (0.016 sec)

MariaDB [MDB101]> INSERT INTO TAB1 (Col1, Col2, Col3) VALUES (NEXT VALUE FOR Seq1_100, ‘Japan’, 30);
Query OK, 1 row affected (0.007 sec)
Instead of having the NEXT VALUE FOR in each INSERT statement, it could have been the default value of the column in this way:
MariaDB [MDB101]> ALTER TABLE TAB1 MODIFY Col1 int(10) NOT NULL DEFAULT NEXT VALUE FOR Seq1_100;
Query OK, 0 rows affected (0.007 sec)
Records: 0 Duplicates: 0 Warnings: 0
Running a SELECT over the TAB1 table will look like this:
MariaDB [MDB101]> SELECT * FROM TAB1;;
+——+———–+——+
| Col1 | Col2 | Col3 |
+——+———–+——+
| 100 | India | 10 |
| 101 | Jakarta | 20 |
| 102 | Singapore | 20 |
| 103 | Japan | 30 |
+——+———–+——+
4 rows in set (0.000 sec)
As we can see the Col1 column has been populated with numbers that start from 100 and are incremented with 1 as defined in the sequence’s CREATE statement. To get the last retrieved number from the sequence PREVIOUS VALUE is used:
MariaDB [MDB101]> SELECT PREVIOUS VALUE FOR Seq1_100;
+—————————–+
| PREVIOUS VALUE FOR Seq1_100 |
+—————————–+
| 103 |
+—————————–+
1 row in set (0.000 sec)
MariaDB 10.3 shipped another very useful option for sequences is CYCLE, which means that we start again from the beginning after reaching a certain value. For example, if there are 5 phases in a process that are done sequentially and then start again from the beginning, we could easily create a sequence to always be able to retrieve the number of the next phase:
MariaDB [MDB101]> CREATE SEQUENCE Seq1_100_c5
-> START WITH 100
-> INCREMENT BY 1
-> MAXVALUE = 200
-> CYCLE;
Query OK, 0 rows affected (0.012 sec)
The sequence above starts at 100 and is incremented with 1 every time the next value is requested. But when it reaches 200 (MAXVALUE) it will restart from 100 (CYCLE).
We can also set the next value of a sequence, to ALTER a sequence or using sequences in Oracle mode with Oracle specific syntax. To switch to Oracle mode use:
MariaDB [MDB101]> SET SQL_MODE=ORACLE;
Query OK, 0 rows affected (0.000 sec)
After that you can retrieve the next value of a sequence in Oracle style:
MariaDB [MDB101]> SELECT Seq1_100.nextval;
+——————+
| Seq1_100.nextval |
+——————+
| 104 |
+——————+
1 row in set (0.009 sec)
You can read about MariaDB sequences in the documentation, MariaDB documentation
 
The post MariaDB 10.3 support Oracle mode sequences appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

The first impression of MySQL 8 system variable innodb_dedicated_server

We manage several hundreds of MySQL servers, We carefully benchmark and build custom database infrastructure operations for performance, scalability, availability and reliability … But What if we have provision for auto sizing of MySQL system variables innodb_buffer_pool_size, innodb_log_file_sizeand innodb_flush_method ? Actually, These are top 3 system variables we consider tuning for MySQL performance and when we first read about this feature, we got super excited so did some research and decided to write this post:
What was our first reaction, when we first read about innodb_dedicated_server ?
Wow, That will be awesome … Indeed, When we manage several hundreds of MySQL instances, This feature will really improve efficiency and DBA Ops. governance.
Now, Let us explain what we have found:
How does innodb_dedicated_server system variable in MySQL 8.0 size the following variables:

innodb_buffer_pool_size:

<1G – 128M (default value if innodb_dedicated_server is disabled / OFF)
<=4G = Detected Physical RAM * 0.5
>4G : Detected Physical RAM *0.75

innodb_log_file_size: 

<1G: 48M(default value if innodb_dedicated_server is OFF)
<=4G: 128M
<=8G: 512M
<=16G: 1024M
>16G: 2G

innodb_flush_method 

Set to O_DIRECT_NO_FSYNC if the setting is available on the system. If not, set it to the default InnoDB flush method

The first impression of innodb_dedicated_server system variable in MySQL 8.0 is impressive, Definitely will deliver much better performance than default value. This new feature will configure the MySQL system variable mentioned above more intuitively to improve DBA productivity. Till MySQL 5.7 it was always presumed 512M RAM with the default settings.
Are we going to follow this in our daily DBA checklists  ?
Not really, We are an very conservative team about implementing the new features immediately in the critical database infrastructure of our customers, Also we are afraid about the isolated issues due to auto sizing of MySQL / InnoDB memory structures, Let’s explain why we will not be using this feature immediately for our MySQL 8.0 customers:

We carefully size InnoDB memory parameters on various factors like database size, transaction complexity, archiving policies etc.  So we want to be hands-on or follow manual sizing of system variables innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method.

Capacity planning and sizing – We are always afraid of over / undersizing of our database infrastructure operations. Database infrastructure operations reliability is very critical for us, We have dedicated team with-in to monitor and trend database infrastructure operations and system resource usage consumption.

P.S – innodb_dedicated_server system variable is a relatively new feature, We are confident MySQL engineering team will be improving this component in coming days so our perspective will also change, We will never forget then to blog about this feature and why we are seriously thinking about implementing it for our customer production infrastructure.. Technology keeps changing for good, We are adaptive for the change !
The post The first impression of MySQL 8 system variable innodb_dedicated_server appeared first on MySQL Consulting, Support and Remote DBA Services By MinervaDB.

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569