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

This article is mainly about the characteristics and parameter performance of mysql database, if you are interested, let's take a look at this article. I believe that after reading the characteristics and parameter performance of mysql database, it is of some reference value to you.

One: comparison between mysql and other databases

MySQL is a relational database management system developed by MySQL AB of Sweden, which has been acquired by Sun. It supports a variety of operating systems such as FreeBSD, Linux, MAC and Windows, which are slightly weaker than other large databases such as Oracle, DB2, SQL Server, etc.

1. It can handle large data with tens of millions of records.

2. Support common SQL statement specifications

3. The portable line is high and the installation is simple and small.

4. Good operation efficiency and network support with rich information

5. Debugging, management, and simple optimization (compared with other large databases)

Ease of use comparison

In terms of installation, the size of the MySQL installation package is only about 100MB, which is not an order of magnitude compared with those major commercial databases. It is also much easier to install than commercial databases such as Oracle, whether through a compiled binary distribution or through source code compilation.

Performance comparison

MySQL has always adhered to a principle, that is to improve its processing capacity as much as possible on the premise of ensuring sufficient stability. In other words, in terms of performance and functionality, the first consideration of MySQL is performance. MySQL hopes to devote all its remaining energy to improving system performance on the premise of meeting 99% of customers' needs, and does not want to be a product that is more powerful than any other database.

Generally speaking, in the process of development, MySQL database has been pursuing three principles: simple, efficient and reliable.

Second, the composition of mysql architecture

The composition of mysql physical files:

1) Log files: mainly contain {error log, query log, slow query log, transaction log, binary log}

Log is an important part of mysql database. Record changes that occur during the operation of the mysql database, such as client connection status of the mysql database, execution of sql statements, and error messages. When the database is corrupted, the cause of the error in the file record can be seen through the log, and the data can be recovered through the log file.

First of all, we will introduce the functions of logs one by one:

Error log: Error Log

In mysql databases, error logging is enabled by default. By default, the error log is stored in the data directory of the mysql database. The name of the error log file is usually hostname.err. Where hostname represents the host name of the CVM.

The information recorded in the error log can be defined by log-error and log-warnings, where log-error defines whether the error log is enabled and where the error log is stored, and log-warnings defines whether warnings are also defined in the error log. The recorded content information includes information during the startup and shutdown of the cloud server (not necessarily error information, such as how mysql starts the tablespace file of InnoDB, how to initialize its own storage engine, etc.), the error information during the operation of the cloud server, the information generated when the event scheduler runs an event, and the information generated when starting the cloud server process from the cloud server.

Mysql has a lot of system variables that can be set. Different system variables will lead to different running states of the system.

Mysql two sets of commands: view system settings and running status respectively:

1. View system settings:

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

SHOW VARIABLES:shows the values of MySQL system variables.

2. Running status:

SHOW [GLOBAL | SESSION] STATUS [like_or_where]

SHOW STATUS:provides server status information.

Then modify the system configuration: in the main configuration file

Vi / etc/my.cnf

For example: log_bin=/usr/local/mysql/data/

View the version of mysql

Or

In general, log-level definitions are defined at the global level without session variables.

Status of the error log:

Log-error is defined as the error log file path

Log-error-verbosity

The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity.

You can use log-error to change the location of the error log as follows:

In the main configuration file: vi / etc/my.cnf

Log-error = / usr/local/mysql/data/mysqld.err

View the error log contents of mysql:

At work, you sometimes want to back up the error log and re-record it, so you can use mysql's flush logs to refresh the log to generate a new log file. The backup file ends with .beifen

Delete the error log:

Before mysql5.5.7: database administrators can delete error logs from a long time ago to ensure hard disk space on the mysql cloud server. In the mysql database, you can use the mysqladmin command to open a new error log. The syntax of the mysqladmin command is as follows: mysqladmin-u root-pflush-logs can also log in to the mysql database and use the FLUSHLOGS statement to open a new error log.

After mysql5.5.7: the CVM will disable this feature. You can only rename the original error log file and manually flush the log to create a new one as follows:

For more information, please refer to the official document: http://dev.mysql.com/doc/refman/5.5/en/error-log.html

Http://dev.mysql.com/doc/refman/5.6/en/error-log.html

Http://dev.mysql.com/doc/refman/5.7/en/error-log.html

Binary log: Binary Log & Binary Log Index

Binary log, commonly known as Binary Log, is mainly used to record mysql statements that modify data or may cause data changes, and record the occurrence time, execution time and operation data of the statements. In general, the upper limit of size and volume is 1G.

When we turn on the recording function through "log-bin=file_name", MySQL records all the query that modifies the database data into the log file in binary form. Of course, the log is not limited to query statements, but also includes the execution time of each query, the resources consumed, and related transaction information, so binlog is transaction-safe.

Master: if log-bin log is not enabled, then mysql master-slave replication cannot be done.

Like the error log, the binlog logging function also requires the explicit specification of the "log-bin=file_name" parameter to be enabled. If file_name is not specified, it will be recorded as mysql-bin.* in the data directory (* represents a number between 0x9 to indicate the sequence number of the log).

Opening of binary system:

It is currently closed.

You can open it through a straight configuration file:

Then restart the mysql service

Check again that the mysql service has been started:

Binlog has some other additional option parameters:

"max_binlog_size" sets the maximum storage limit for binlog, which is generally set to 512m or 1G, and generally cannot exceed 1G log to reach this upper limit. MySQL will create a new log to continue recording. However, occasionally binlog beyond this setting is generated, usually because a larger transaction is generated when the upper limit is about to be reached. In order to ensure transaction security, MySQL will not record the same transaction into two binlog separately.

The "binlog-do-db=db_name" parameter explicitly tells MySQL that the binlog needs to be recorded against a (db_name) database, and if the "binlog-do-db=db_name" parameter is explicitly specified, MySQL will ignore the query executed against other databases and only record the query executed against the specified database.

"binlog-ignore-db=db_name" is the exact opposite of "binlog-do-db=db_name". It explicitly specifies that the binlog record of a (db_name) database is ignored, and when this parameter is specified, MySQL records the binlog of all databases other than the specified database.

The function of mysql-bin.index file (binary log index) is to record the absolute path of all Binary Log to ensure that various threads of MySQL can successfully find all the needed Binary Log files according to it.

Binlog_cache_size = 32768 # default value 32768 binlog_cache_size: a transaction, when there is no commit (uncommitted), the generated log is recorded in Cache; when the transaction commit (committed) needs to commit, the log is persisted to disk. In general, if there are no big transactions in our database and writes are not particularly frequent, 2MB~4MB is an appropriate choice. However, if our database has a large number of transactions and a large number of writes, we can increase binlog_cache_size appropriately.

Binlog_cache_size: a transaction, when there is no uncommitted, the log generated is recorded in Cache; when the transaction commit (committed) needs to be committed, the log is persisted to disk.

Next, how big is the binlog_cache_size setting? The answer is: it depends on the actual situation of the company's production.

If the setting is too large, it will consume memory resources (Cache is memory in essence). It is more important to note that binlog_cache is not global and is allocated exclusively by SESSION, that is, when a thread starts a transaction, Mysql allocates a binlog_cache to the SESSION. (note: I think it makes sense to allocate binlog_cache as a unit of SESSION, because different mysql requests If the number of binlog generated is different, a large number of binlog will be generated by inserting data in batches, but the binlog generated by simply registering a user is limited, so can binlog_cache_size be set on the JDBC connection? ).

If the setting is too small, if the user submits a "long transaction (long_transaction)", for example: bulk import data. Then the transaction will inevitably generate a lot of binlog, so the cache may not be enough (the default binlog_cache_size is 32K). When it is not enough, mysql will write part of the uncommitted to a temporary file (the temporary file cache must not be as efficient as memory cache), and will not write to the formal persistence log file until committed.

Conceptual explanation:

Transaction tables support the unified commit or rollback of batches as a complete task, that is, all or none of the statements contained in the transaction

Non-transactional tables do not support this operation, and if statements in a batch encounter an error, the statements before the error are executed successfully, and the statements that follow are not executed.

Log-bin = mysql-bin# specifies the location of the binlog, which is under the data directory by default.

Binlog-format= {ROW | STATEMENT | MIXED} # specifies the type of binary log, which defaults to MIXED.

Concept explanation: there are three main ways of mysql replication: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), mixed mode replication (mixed-based replication, MBR). Accordingly, there are three formats for binlog: STATEMENT,ROW,MIXED.

STATEMENT mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of each row, which reduces the amount of binlog logs, saves IO, and improves performance.

Disadvantages:

In some cases, it can lead to data inconsistency in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf)).

ROW mode (RBR)

Instead of recording the information of each sql statement, you only need to record which piece of data has been modified and how it has been modified.

Disadvantages:

It will generate a large number of logs, which will make the logs soar.

③ MIXED Mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed. That is, alternately use lines and statements, and be judged by the mysql CVM.

Among them, the row-based definition format will have a larger amount of data, but can ensure the accuracy of the data.

Note: MBR mode is often used in production environment, although the use of Imax O is increased, it is more secure to data.

Sync_binlog = 1 sets how often the binary log is synchronized to the disk file, 0 means out of sync, and any positive value indicates that the binary is synchronized after every number of writes. When the value of autocommit is 1, the execution of each statement will cause binary log synchronization, otherwise, the commit of each transaction will cause binary log synchronization.

Binary logs can be enabled by editing the log-bin option in my.cnf, as follows:

Log-bin = / usr path

Where the DIR parameter specifies the storage path of the binary file, and the filename parameter specifies the file name of the two-level file, which is in the form of filename.number,number 000001, 000002, and so on. Each time you restart the mysql service or run mysql > flush logs;, a new binary log file is generated, and the number of these log files is incremented. In addition to generating the above file, a file named filename.index is also generated. The list of all binary log files stored in this file is also known as the index of binary files

View the binary log:

Binary logs are defined in binary format; using this format can store more information and make writing to binary logs more efficient. However, you cannot directly use the View command to open and view the binary log.

Currently used binaries and their location

View information about the current binary file:

Command to view binary log information:

Syntax format: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

# View all binary information

Mysql > show binlog events\ G

# View the binary information of the specified log

# start from the specified event location

Mysql > show binlog events in 'log.000002' from 1215\ G

Since you cannot directly open and view the binary log using methods such as cat, you must use the mysqlbinlog command. However, it is recommended that you do not use this to open binary log files in use when you are performing mysql read and write operations; if you do not want to open flush logs. How the mysqlbinlog command is used:

Delete binary log information:

Binary logs record a large amount of information (which contains some useless information). If the binary log is not cleaned for a long time, a lot of disk space will be wasted. However, after deletion, it may be impossible to recover when the database crashes, so if you want to delete the binary log, first back it up with the database, and you can only delete the binary log before the backup, and the newly generated log information cannot be deleted. It cannot be deleted directly after shutting down the mysql CVM, because it may bring errors to the database. If you do not want to delete the binary log, you need to do the following: export the backup database and binary log files for compressed archive storage. The methods to delete binaries are as follows:

Method 1: delete the binary log based on the file or point in time:

Grammatical form:

Mysql > PURGE {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

Where TO'log_name' means to delete all other files before this file, or you can use BEFORE datetime_expr to specify when the binary files before this file have been deleted.

Delete all binary logs (use with caution):

You can delete all binary logs using the RESET MASTER statement. The statement looks like this:

3. Transaction log (or redo log)

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when it modifies the data, and then records the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. The transaction log is appended, so the operation of writing the log is the sequential IPUP O in a small area of the disk, unlike the random IUnip O, which needs to move the head in multiple places on the disk, so using the transaction log method is relatively faster. After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background. At present, most storage engines are implemented in this way.

If the modification of the data has been recorded in the transaction log and persisted, but the data itself has not been written back to disk, the system crashes and the storage engine can automatically recover the modified data when it is restarted. The recovery method it has depends on the storage engine.

In general, mysql provides a variety of storage engines by default, which you can view by:

See what storage engine your mysql now provides:

Mysql > show engines

Take a look at your mysql's current default storage engine:

Mysql > show variables like'% storage_engine%'

You need to see which engine is used for a table (the storage engine currently used for the table is indicated by the parameter engine in the display results):

Mysql > show create table table name

Note:

Create table library name. Table name engine = innodb

This allows you to change the engine of the table to the innodb engine.

You can also change it after creating the table with the following statement:

Alter table library name. Table name engine = innodb

View the definition of the transaction log:

Mysql > show global variables like'% log%'

Display the results:

| | innodb_flush_log_at_timeout | 1 | |

| | innodb_flush_log_at_trx_commit | 1 # whether innodb synchronizes the log from the buffer to the file when the transaction commits. When this value is 1 (default), the log buffer is written to the log file when each transaction is committed, and the log file is refreshed by disk operation. The performance will be very poor, resulting in a large number of disk Ipando, but this is the safest way. | If set to 2, each commit transaction will be logged, but no brush operation will be performed. The log file is brushed at the same time every second. It is important to note that there is no guarantee that 100% will be flushed to disk every second, depending on the scheduling of the process. The data is written to the transaction log each time the transaction is committed, and the write here only invokes the write operation of the file system, which is cached, so this write does not guarantee that the data has been written to the physical disk. Set to 0, the log buffer is written to the log file once a second, and the log file is refreshed to disk, but nothing is done in a transaction commit.

Note: the concept of brushing

Flush is actually two operations, brush and write (write), it is important to distinguish between these two concepts. In most operating systems, the log buffer (memory) of Innodb is written to the log (calling the system call write), simply moving the data to the operating system cache, which also refers to memory. There is no actual persistence data.

So, usually when set to 0 and 2, the last second of data will be lost in the event of a crash or power outage, because at this time the data only exists in the operating system cache. The reason for saying "usually" is that more than 1 second of data may be lost, such as blocking when performing a flush operation.

Summary

A setting of 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If the requirements for data consistency and integrity are not high, you can set it to 2. If you only want the most performance, such as log cloud servers with high concurrent writes, set it to 0 to get higher performance.

| |

| | innodb_locks_unsafe_for_binlog | OFF |

| | innodb_log_buffer_size | 16777216 | |

| | innodb_log_checksums | ON |

| | innodb_log_compressed_pages | ON |

| | innodb_log_file_size | 50331648 # Log file size |

| | innodb_log_files_in_group | set several sets of transaction logs in 2 # DB. Default is 2 | |

| | innodb_log_group_home_dir |. / # define the location of the innodb transaction log group. This location defaults to the datadir of MySQL | |

Each transaction log is a 50 megabyte file (different versions of mysql vary):

It defaults to the name ib_logfile0,ib_logfile1 in mysql

4. Slow log: slow query log

As the name implies, the slow query log records the query that takes a long time to execute, which is what we often call slowquery.

The slow log is in a simple text format and can be viewed through a variety of text editors. Among them

Record the time of the statement execution, the time consumed by the execution, the execution user, connect to the host and other related information.

The function of slow query log:

Slow query logs are used to record queries that have been executed for more than a specified time. Through the slow query log, you can find out which query statements are inefficient for optimization. It is generally recommended to enable it. It has little impact on the performance of the CVM, but it can record the query statements that have been executed for a long time on the mysql CVM. That can help us locate performance problems. MySQL also provides a tool program mysqldumpslow that is specially used to analyze full query logs to help database administrators solve possible performance problems.

View the definition of the slow query log:

\ start and set the slow query log:

Method 1: open the slow query log through the configuration file my.cnf:

Note: the parameters for enabling slow log are different in different versions of mysql, but they can be seen through show variables like "% slow%" and show variables like "% long%".

Where:

Slow_query_log: off off state (0) on on state (1)

Storage location of slow_query_log_file slow query log

Long_query_time option to set a time value in seconds, which can be accurate to microseconds. If the query time exceeds this time value (the default is 10 seconds), the query statement will be recorded in the slow query log. If set to 0, all queries will be recorded.

Note: if you do not specify a storage path, the slow query log is stored in the data file of mysql database by default. If you do not specify a file name, the default file name is hostname-slow.log.

Modify the my.cnf file:

Alternatively, it can be defined directly through mysql (only temporarily effective)

Mysql > set globalslow_query_log=1; # enable slow log

Query OK, 0 rowsaffected (0.35 sec)

Mysql > setsession long_query_time=0.0001; # change time (in the current session, exit is reset)

Query OK, 0 rowsaffected (0.00 sec)

Mysql > set globallong_query_time=0.0001; # change time (globally, restart the service to reset)

Mysql > SHOWVARIABLES LIKE 'long%'; # query definition time

View slow query log

View file contents commands such as cat view slow log files directly

Data text (here mainly introduces the difference and function of myisam and innodb)

In MySQL, every database has a folder named after the database under the defined (or default) data directory, which is used to store various table data files in the database. Different MySQL storage engines have different data files. For example, MyISAM uses ".MYD" as the extension, and Innodb uses ".ibd"

How to see what storage engine your mysql now provides:

Mysql > show engines

Take a look at your mysql's current default storage engine:

Mysql > show variables like'% storage_engine%'

You need to see which engine is used for a table (the storage engine currently used for the table is indicated by the parameter engine in the display results):

Mysql > show create table table name

In addition, you can change the engine of the table with engine=innodb after the table name when you create the table.

Create table library name. Table name engine = innodb

You can view the created file format in the file directory

Check the mysql storage engine command and enter the show engines; field under the mysql > prompt. Support: Default indicates the default storage engine.

2. Set InnoDB as the default engine: add the sentence default-storage-engine=INNODB under "mysqld] in the configuration file my.cnf.

3. Restart the mysql CVM: log in to the mysql database with service mysqld restart

1. ".frm"

Mainly stores the data of the table; including defining the table structure information, and in each table there is a .frm file named after the table, and all the files are stored in this folder

MyISAM database table file: .MYD file: table data file; .MYI file: index file

2. ".MYD" file

Myisam stores special files for the storage engine.

3. ".MYI" file

The ".MYI" file is also dedicated to the MyISAM storage engine and mainly stores the index-related information of the MyISAM table.

InnoDB uses table space (tablespace) to manage data and store table data and indexes.

.ibd file: a single table space file, each table uses a table space file (file per table), to hold the user database table data and indexes.

InnoDB shared tablespaces (that is, InnoDB filesets, ib-file set): ibdata1, ibdata2, etc., store InnoDB system information and user database table data and indexes, shared by all tables.

The difference between .idb and ibdata:

.id advantages and disadvantages between the two

Shared tablespaces:

Advantages:

You can divide the tablespace into multiple files and store them on each disk. Data and files are put together for easy management.

Disadvantages:

All the data and indexes are stored in one file, and multiple tables and indexes are mixed in the tablespace, so there will be a lot of gaps in the tablespace after a large number of deletions for a table, especially for statistical analysis. applications such as daily value systems are the least suitable for shared tablespaces.

Independent tablespaces:

Advantages:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be recycled.

B can only store individual file data, and ibdata can store more data as a shared folder.

View the tablespace of the current database:

On stands for independent tablespace; off stands for shared tablespace

Then modify the main configuration file to open the shared tablespace

You can check du-h ibdata1 first.

Log in to mysql to execute mysql > show variables like'% innodb_file_per_table%'

At this point, the new table will use the shared tablespace.

Create a database testdb and create a new table

Drop procedure if exists test; = > Delete pre-existing files

Create procedure test () = > create a test file

Begin = > start

Type of declare I int;= > advertisement I

The value of set iTunes 1 = > I equals 1

While i

< 100000 do =====>

If the value of I is less than 100000

Insert into lxf.ttt (id) values (I); = > insert data variable is I

Set I = I + 1 = > after each execution, add 1 to the value of I until it is 99999

End while = > end of loop

End & & end

Call the stored procedure:

Check the number of lines of the subject matter

Check the table space usage of the table:

Replication related files:

1) master.info file:

The master.info file exists in the data directory on the Slave side, which stores the relevant information of the Master side of the Slave, including the host address of the Master, the connection user, the connection password, the connection port, the current log location, the location of the log that has been read, and other information.

2) relay log and relay log index

The mysql-relay-bin.xxxxxn file is used to store the Binary Log information read by the Master thread on the Slave side, and then the SQL thread on the Slave side reads and parses the corresponding log information from the relay log, converts it into SQL statements executed by Master, and then applies it on the Slave side.

The function of the mysql-relay-bin.index file is similar to mysql-bin.index, which is also the absolute path to the location where the log is stored, except that what he records is not Binary Log, but Relay Log.

3) relay-log.info file:

Similar to master.info, it stores messages that are written to the local relay log through the Slave's I _ map O thread.

Interest. SQL threads on the Slave side and some administrative operations can obtain information about the current replication at any time.

Other documents:

1) system config file

The system configuration file of MySQL is generally my.cnf, which is stored in the "/ etc" directory by default. The my.cnf file contains a variety of parameter option groups (group), each of which is given a fixed group name through square brackets. For example, the "[mysqld]" group includes the initialization parameters of mysqld service startup, and the "[client]" group contains parameters that can be read by client tool programs.

2) pid file

Pid file is a process file in mysqld application environment that stores its own pid number.

3) socket file

Socket files are also available in the Unix/Linux environment. In the Unix/Linux environment, users can connect to MySQL directly using Unix Socket without going through the TCP/IP network.

There are two ways to connect mysql, and the common one is tcp.

Mysql-hmysql host ip-uroot-pxxx (can be connected remotely, but slightly slower)

Mysql-S / path/mysql.sock (local connection can only be tried, but fast)

The above details about the characteristics and parameter performance of mysql database are helpful to you. If you want to know more about it, you can continue to follow our industry information section.

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.