MySQL common storage engine introduction and parameter settings tuning, mysql Tuning

MySQL common storage engine introduction and parameter settings tuning, mysql Tuning

MyISAM

Features:
1. Concurrency and lock level
2. Repair of Table Corruption
Check table tablename
Repair table tablename
3. Index types supported by MyISAM tables
① Full-text index
② Prefix Index
4. MyISAM tables support Data Compression
Myisampack
Restrictions:
Version <MySQL5.0 Default table size is 4 GB
If the storage is up to standard, you need to modify MAX_Rows and AVG_ROW_LENGTH.
Version> MySQL5.0 supports 256 TB by default

Applicable scenarios:
1. Non-transactional applications
2. Read-Only applications
3. spatial applications

MySQL common storage engine-Innodb

Features of the Innodb Storage Engine
1. Innodb is a transactional storage engine.
2. Fully supports ACID features of transactions
3. Redo Log and Undo Log
4. Innodb supports row-level locks

Innodb uses tablespaces for data storage
Create a tablespace for each table
Innodb_file_per_table
ON: Independent tablespace: tablename. ibd
OFF: System tablespace: ibdataX (X is a number starting from 1)

How to Select system tablespace and independent tablespace
Comparison:
The system tablespace cannot be scrubbed.
You can use the optimize table command to shrink system files.
System tablespace may cause IO bottlenecks
Independent tablespace can refresh data to multiple files at the same time

Table transfer procedure
Steps:
1. Use mysqldump to store all database table data everywhere
2. Stop MySQL, modify parameters, and delete Innodb files.
3. Restart the MySQL service and recreate the Innodb system tablespace.
4. re-import data

CSV for MySQL common storage engines

File System storage features
1. data is stored in text
22.16.csv file storage table content
3. csm file storage table metadata, such as table status and data volume
4. frm file storage table structure information
5. Storage in csv format
6. All columns must not be Null.
7. indexing is not supported.

Applicable scenarios:
Used as an intermediate table for data exchange (workbook> csv File> MySQL database directory)

Archive

File System storage features

1. Use zlib to compress table data with less disk I/O
2. data is stored in files suffixed with ARZ.

Features of Archive storage engine
1. Only insert and select operations are supported.
2. Only auto-increment ID columns can be indexed.

Applicable scenarios:
Log and Data Collection Applications

Memory of MySQL common storage engine

File System storage features
1. It is also a HEAP storage engine, so data is stored in the memory.

Features:
1. Support for HASH indexes and Btree Indexes
2. All fields have a fixed length of varchar (10) = char (10)
3. large fields such as BLOG and TEXT are not supported.
4. The Memory storage engine uses table-level locks.
5. the maximum size is determined by the max_heap_table_size parameter.

Applicable scenarios:
1. Used to search for or map tables, such as the corresponding table of zip code and region
2. It is used to save the intermediate table generated by data distraction.
3. A result table used to cache periodically aggregated data

Federated

Features:
1. provides methods to access the table above the remote MySQL Server
2. Data is not stored locally, and all data is stored on the remote server.
3. You need to save the table structure and remote server connection information locally.

How to Use
It is static by default. You need to add the federated parameter at startup.
Mysql: // user_name [: password] @ host_name [: port]/db_name/table_name

Applicable scenarios:
Occasional statistical analysis and manual Query

How to select the Correct storage engine

Reference conditions
1. Whether to support transactions
2. Regular backup
3. Crash recovery
4. unique features of the storage engine

Mysql Server Parameters

MySQL obtains the configuration path

1. Command Line Parameters

Mysqld_safe -- datadir =/data/SQL _data

2. Configuration File

Command to view the configuration file:
[Root @ localhost ~] # Mysqld -- help -- verbose | egrep-A 1 'default options'
Valid path of the configuration file
/Etc/my. cnf/etc/mysql/my. cnf/usr/etc/my. cnf ~ /. My. cnf

Scope of MySQL configuration parameters

1. Global Parameters
Set global parameter name = parameter value;
Set @ global. Parameter Name: = parameter value;
2. Session Parameters
Set [session] parameter name = parameter value;
Set @ session. Parameter Name: = parameter value;

Memory configuration parameters
1. determine the maximum memory available
2. Determine the memory used by each MySQL connection.
Sort_buffer_size
Join_buffer_size
Read_buffer_size
Read_rnd_buffer_size
3. Determine the memory size to be retained for the operating system.
4. How to allocate memory for the cache pool
Innodb_buffer_pool_size
Note: The consideration for setting the cache pool size is: total memory-(memory * connections required for each programming)-memory retained by the System
Key_buffer_size
Select sum (index_length) from information_schema.tables where engines = 'myisam'

I/O-related configuration parameters
Innodo I/O-related configuration
Innodb_log_file_size size of a single transaction log
Innodb_log_files_in_group controls the number of days of Files
Total transaction log size = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32 M or 128 M)
Innodb_flush_log_at_trx_commint
0: Write logs to the cache every second and flush logs to the disk.
1 [Default]: execute log writing to cache in each transaction commit, and flush log to disk
2 [Suggestion]: Execute the flush log to the disk every second after each transaction commit and write the log data to the cache.
Innodb_flush_method = O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1

Configuration related to MyISAM I/O
Delay_key_write
OFF: refresh the dirty blocks in the key buffer to the disk after each write operation.
ON: only use delayed refresh for tables with the delay_key_write option specified in the key table
ALL: Use delayed write for ALL MyISAM tables

Security-related configuration parameters
Expire_logs_days specifies the number of days for binlog automatic cleanup
Max_allowed_packet controls the size of packages that MySQL can connect to. It is recommended to set it to 32 M. If master-slave replication is used, the parameters should be set to consistent
Skip_name_resolve disable DNS Lookup
Sysdate_is_now ensure that sysdate () returns the guaranteed date
Read_only disables write operations for non-super Users. Note: We recommend that you enable this function in the slave database of master-slave replication. To ensure that the operations in the slave database cannot be modified, they can only be synchronized from the master database.
Skip_slave_start disable automatic Salve recovery (used in slave database settings)
SQL _mode: sets the SQL mode used by MySQL (careful operations may cause MySQL to fail)
① If the data given by strict_trans_tables cannot be inserted into the database, the transaction engine will perform terminal operations without affecting non-transaction engines.
② When no_engine_subitiution specifies engines in create table, if the engine is unavailable, the default engine is not used to create a table.
③ No_zero_date cannot be used to insert a date from 0 to 0 to 0 in the table.
④ No_zero_in_date does not accept a part of the 0 date
⑤ Noly_full_group_by

Other common configuration parameters
Sync_binlog controls how MySQL refreshes binlog to the disk
Tmp_table_size and max_heap_table_size control the size of the temporary memory table (it is not recommended to set too large to avoid memory overflow)
Max_connections controls the maximum number of connections allowed (the default value is 100, which is a little small. Adjust the size according to your business)

What affects performance?

Impact of Database Design on Performance
1. Too many columns are created for tables in an anti-paradigm.
2. Excessive normalization results in too many table associations (the associated tables should be controlled within 10 as much as possible)
3. Use a non-forward partition table in the OLTP Environment
4. Use foreign keys to ensure data integrity

Summary

Performance Optimization Sequence
1. Database Structure Design and SQL statements
2. database storage engine selection parameter configuration
3. System Selection and Optimization
4. hardware upgrade