Shulou(Shulou.com)06/01 Report--

Many students must have heard of MariaDB. As one of the important branches of MySQL, it continues to be completely open source (MySQL also has many useful features for a fee), and is widely used by many large Internet companies (such as Google, Twitter). At the same time, Red Hat 7 (including CentOS) also changed the default database from MySQL to MariaDB. In this case, be sure to understand the reverse growth of MariaDB.

This article focuses on some very attractive features of MariaDB 10.0 GA version, which is convenient for you to compare with MySQL.

1. Feedback plug-in-not recommended:

A) Open method: [mysqld] module of my.cnf configuration file, and add feedback=on

B) role: usage data will be sent to developers to help them optimize their code.

2. InnoDB and XtraDB conversion:

A) MDB uses XtraDB by default.

B) opening method:

1) stop the mysql service.

2) in the [mysqld] module of the my.cnf configuration file, add ignore_builtin_innodb,

Plugin_load=innodb=ha_innodb.so

3. MYSQL client reports:

A) Control parameters: the range of global.progress_report_time values should be greater than 5. In seconds. Values below 5 are ignored.

B) method of closing:

1) add-- disable-progress-reports when starting the service

2) set the global.progress_report_time value to equal to 0.

C) this feature is also supported by mytop scripts that come with MariaDB

4. Variants of the SHOW EXPLAIN FOR query_id:Explain command.

You can query the execution plan when you know the QID.

5. An evolutionary version of the LIMIT ROWS EXAMINED:LIMIT command.

Syntax: LIMIT [m] ROWS EXAMINED n (m optional)

What it does: ordinary LIMIT statements continue to execute after querying a specified number of paging results. If it is a large table, such a paging operation will consume too many resources.

For example: LIMIT 100 ROWS EXAMINED 10000 will filter the first 10000 records again from a page containing 100 records.

6. INSTALL SONAME: install the specified plug-in and engine. INSTALL PLUGIN command variant.

Grammar: INSTALL SONAME engine_name.

Example: install the BLACKHOLE engine.

INSTALL SONAME 'ha_blackhole'

INSTALL PLUGIN Blackhole SONAME 'ha_blackhole'

Uninstall a plug-in and replace INSTALL with UNINSTALL

7. Generate the HTML/XML file:

For example: mysql-- html/--xml isfdb

Isfdb-001.html/ isfdb-001.xml

The results returned by the query statements in isfdb-001.sql are generated into the corresponding html or xml file.

8. Convert MYISAM engine to ARIA engine:

The main difference between ARIA engine and MYISAM engine is that ARIA engine comes with crash safe function, which can recover data in the event of table corruption caused by catastrophic power outage or other unexpected table errors.

Use the ALTER TABLE command to convert the engine.

The operation procedure of the conversion engine is the same as that of MYSQL:

A) create a new table first

B) Import data

C) Rename operation

So for tens of millions of large watches, this process will be quite a long time. This operation is not recommended on the production database.

9. Control the MariaDB query optimizer policy:

A) View the optimization policy status in the current query optimizer:

SELECT @ @ optimizer_switch\ G

Turn an optimization policy on or off:

For example, SET [GLOBAL] optimizer_switch= "mrr=on"

Or add to the [mysqld] module in the my.cnf configuration file:

[mysqld]

Optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"

B) Open the optimizer extended keys policy on INNODB and XTRADB:

1) the opening method is shown above, and the parameter name is: extended_keys=on

2) role: because the optimizer is CBO-based, queries that have many indexes but cannot be used by the optimizer will appear in the execution plan. Turning on the optimizer extended keys policy will cause the optimizer to use indexes to return as much as possible when this occurs, rather than scanning the entire table.

10. Configure the Aria engine two-step deadlock monitoring:

A) principle:

When the Aria engine is unable to create locks on the table, it first creates a deep search wait graph (WFG) for possible deadlocks based on the value of deadlock_search_depth_short. When the search is over, if the lock cannot be created, the Aria engine is in no hurry to determine the deadlock, but waits for the microsecond defined by deadlock_timeout_short to search again. If the lock still cannot be created, the Aria engine uses the value of the deadlock_search_depth_long parameter to create a deep search wait graph. When the search is over, if the deadlock has not been defined, the Aria engine will wait for the microseconds defined by deadlock_timeout_long and return a timeout error.

B) View the current settings: SHOW VARIABLES LIKE 'deadlock_%'\ G

C) modify parameter values, such as:

D) or write to the [mysqld] module of the configuration file my.cnf.

E) Note: the units in the above timeout parameters are microseconds.

11. Configure the MYISAM engine key cache segment (key_cache_segments):

Function: enhance the performance of MYISAM engine table. Improve MYISAM engine performance in a high concurrency environment.

A) principle:

When the thread of the MYISAM engine uses key caching (key cache), the thread first acquires a lock. In high concurrency scenarios, a large number of threads apply for locks at the same time. For large tables or hotspot tables, complete key caching can become a performance bottleneck. The split key cache can reduce lock competition, and the thread that acquires the lock only needs to lock the corresponding key cache segment, without locking the whole key cache.

B) View the current settings: SHOW VARIABLES LIKE 'key_cache_segments'\ G

C) modify parameter values:

D) or write to the [mysqld] module of the configuration file my.cnf.

twelve。 Configure the thread pool (top priority, core functions):

MariaDB's thread pool provides a more powerful ability to centralize threads. And replaces the one tread per client connection method before mysql. This method is not ideal for thread reuse in typical web stress scenarios (scenarios with a large number of threads but small returns).

A) modify parameter values:

Add thread_handling = pool-of-threads to the [mysqld] module in my.cnf.

B) restart the service

C) function:

Thread pool is the best way to deal with this problem when there are a large number of short queries or CPUload is too high in the production scenario. However, when a large number of long queries appear in a short time, it is not applicable. Usually in this case, the thread_pool_idle_timeout parameter can be used to relieve stress in linux.

D) other important parameters:

1) thread_pool_stall_limit: unit millisecond.

Used for the occurrence of a large number of persistent connection queries, this parameter controls when the thread is stall. The default is 500. If the query has been stall,MDB, a new thread is created, and the maximum number of threads in the thread pool is controlled by the thread_pool_max_threads parameter. The default is 500. When the maximum number of threads in the thread pool is reached, no new thread will be created, even if the thread has been stall.

This situation can be resolved using the parameter extra_port. This parameter opens an additional port to keep the query connected. The value of this parameter must be different from the default port.

2) thread_pool_idle_timeout: unit second.

Defines how long a thread waits before undoing. The default is 60.

If you find that new threads are created periodically after a thread is undone, you should increase the value of this parameter.

3) the thread_pool_size:LINUX platform defines the thread pool size.

13. Configure the Aria engine pagecache:

The PAGE of the Aria engine is row formatting. Its pagecache is controlled by three parameters:

A) aria_pagecache_buffer_size: unit byte. The default is 128M-512M.

This parameter cannot be modified dynamically.

B) aria_pagecache_age_threshold: the block retention length defined in pagecache.

C) aria_pagecache_division_limit: unit percentage. Define the percentage of medium temperature data in pagecache.

14. Optimize queries with subquery caching (subquery cache): MariaDB exclusive!

A) subquery cache greatly improves the performance of subqueries.

B) enabled by default.

C) two state value variables:

1) subquery_cache_hit: the number of hits in the subquery cache

2) subquery_cache_miss: number of misses

15. Optimize semi-join subquery (semijoin query):

The IN clause with where condition is common in semi-concatenated subqueries. This kind of clause is difficult to optimize in MYSQL. MDB provides an optimizer parameter that can be turned on to guide the optimizer to optimize this type of query.

A) Parameter: exists_to_in=on. Not enabled by default.

B) EXISTS-type queries can also be optimized.

C) modify parameter values:

1) SET optimizer_switch='exists_to_in=on'

2) or modify the configuration file:

16. Create a full-text index (full-text index)-not recommended:

A) full-text index is a special type of index used to search for columns based on the text type.

B) full-text indexes can only be created for char, varchar, text types.

C) full-text indexing allows us to query data using the "MATCH ()... AGAINST" syntax.

The MATCH section of the syntax contains a comma-separated list of columns to be queried.

The AGAINST section of the syntax contains the string to search for and an optional modifier to indicate the type of query being executed.

The query types are:

IN NATURAL LANGUAGE MODE, IN BOOLEAN MODE, WITH QUERY EXPANSION

The default type is IN NATURAL LANGUAGE MODE.

17. Enable user statistics:

A) modify the configuration file:

B) or SET GLOBAL USERSTAT = 1

C) you can view the statistics when enabled. Such as:

SHOW INDEX_STATISTICS

18. Use the global transaction ID (global transaction IDs):

More intuitive global transactions. It's easier to understand than the 5. 6 teasing GTID.

A) Global transaction ID (GTID) is a new feature added in version 10.0.2, which will make replication more stable and flexible. It should be noted here that the GTID mentioned in MariaDB and MYSQL 5.6is not universal and is quite different.

B) the GTID information is stored in the mysql.gtid_slave_pos table, and the latest location information of this table is updated in the same transaction in which the data is updated.

C) composition of GTID: 0-1-12345

1) the first is domain ID. This is determined by MariaDB's unique multi-source replication scenario. Is a 32-bit unsigned integer. In a single-source scene, the value of 0.domain id is set in my.cnf, such as gtid-domain-id = 1.

2) the second place is server_id. Is a 32-bit unsigned integer.

3) the third place is Commit_id. Is a 64-bit unsigned integer. The commit id number specified by the transaction during the master commit phase, which is an incremental value, and each commit is different, while in group commit all the commit id specified by the group transaction is the same.

D) enabled by default. Use the show binlog events command to check the value of GTID, and the result is as follows:

The value in the GTID_LIST column is GTID.

It's actually worth [].

To see the current GTID value of the slave library, use the command SELECT @ @ global.gtid_slave_pos to return a null value on the master library.

To see the current gtid value of master, use the command select @ @ global.gtid_current_pos

E) the slave library is copied by gtid. The syntax is: CHANGE MASTER TO master_use_gtid = {slave_pos | current_pos | no}

F) in a normal replication scenario, the slave library uses the value of slave_pos. For example, if An is the master library of B, when An is hung up, B acts as the master library, and A wants to be the slave library of B after A goes online again, the current_ poss value is used. Because A was previously the master library and has never done a slave library, there is no slave_ poss value. Note: there is no slave_pos in the master library that has never been used as a slave library

If binlog is not enabled in the previous slave library, then the values of current_pos and slave_pos are equal.

G) you can use set global gtid_slave_pos= 'XXXX' to modify the value of slave_pos.

Query slave_pos and current_pos:

Select @ @ global.gtid_slave_pos / gtid_current_pos

H) the measured master database crashes when it is constantly written. When the master database is back online, the slave database can catch up with the master database according to slave_pos.

I) understand the concepts of current_pos and slave_pos:

Current_pos: the value is determined by the main library operation. When the machine turns on binlog, the value is recorded when the transaction is executed. All poss values are shown using select @ @ gtid_current_pos.

Slave_pos: this value on the slave library is the same as current_pos. The native copied GTID is shown using select @ @ gtid_current_pos.

19. Multi-source replication:

A) key points that should be paid attention to in multi-source replication:

1) the relay-log parameter should be added to each source my.cnf, such as relay_log = db01-relay-binlog. It is recommended that the hostname be added to the format to distinguish.

2) domain-id from different sources must be different. Otherwise, a replication failure will occur. Configure domain-id to add the gtid-domain-id=n parameter to the my.cnf file. Restart the service.

3) Source hostname can be added to change master syntax to increase differentiation, such as:

Change master ["connect_name"] to

Master_host = 'xxx.xxx.xxx.xxx'

Master_port=3306

Master_user='replication'

Master_password='replication'

Master_use_gtid=current_pos

Note: after using the source hostname for change master, you can only use the start all slaves command to start replication. Other commands are similar.

4) start all replication: start all slaves

Stop all replication: stop all slaves

View all connection replication status: show all slaves status

Check the replication status of a connection: show slave ["connect_name"] status

Clear the replication status of a connection: reset slave ["connect_name"]

Note: the emptying here just redoes the relay_log from 1. Replication information cannot be removed from the presentation of slave status.

Clear the replication status of a connection and remove it from the slave status display:

Reset slave ["connect_name"] ALL

B) reference scenarios of multi-source replication:

1) integrate the data from each main database into a slave to facilitate query.

2) integrate the data of each main database into one slave to facilitate backup.

20. Add binlog comments based on line replication:

Add the binlog_ annotate_row_events parameter to the configuration file. The SQL statement is displayed in the section based on row replication when the mysqlbinlog command views the binlog.

21. Configure the binlog event summation Checker (binlog event checksum):

A) role: for quick detection before a file system failure.

B) Parameter settings:

It can also be enabled by adding the parameter BINLOG_CHECKSUM to the my.cnf configuration file.

twenty-two。 Skip the specified binlog event during replication:

A) Parameter setting: on.

B) function:

All transactions processed during this stage of enabling the feature will not be copied from the library. A similar feature turns off the binlog function: set @ @ sql_log_bin = 0. However, such a parameter setting stops all transaction logging binlog. The Skip_replication parameter is not.

Perry.Zhang

02.18.2016

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.