MySQL log + engine notes

Error log the log service started off

Querying logs query log

• Normal query log General query log client link and SQL

• Slow query log slow query log execution time exceeds specified value

Binary log binary logs information about the data being modified

Error log

Vim/etc/my.cnf

[Mysqld_safe]

Log-error=/data/3306/mysql_oldboy3306.err

Query log

Mysql> Show variables like ' general_log% '; Common query Log parameters

+------------------+----------------------------------+

| variable_name | Value |

+------------------+----------------------------------+

| General_log | OFF |

| General_log_file | /application/mysql/data/Lnmp.log |

+------------------+----------------------------------+

mysql> Set Global general_log = on;

Slow query:

Long_query_time = 1//over (greater than) 1 seconds of record,,, default 10 seconds

Log-slow-queries =/data/3306/slow.log

Log-queries-not-using-indexes//Queries that do not use indexes will also be logged

Log-long-format//Log Active update log, binary update log, and slow query log for a large amount of information

Log-short-format//Small amount of information

Mysql> show global status like '%slow% ';

+---------------------+-------+

| variable_name | Value |

+---------------------+-------+

| Slow_launch_threads |   0 | A large value indicates that something is delaying the link's new thread

| Long_query_time | 1.000000 |

| slow_queries |    0 | Number of records for slow query

| log_slow_queries |     On | Whether to open

+---------------------+-------+

2 rows in Set (0.00 sec)

Binary log

Log-bin=mysql-bin

Mysql> Show variables like '%log_bin% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | On |

| Sql_log_bin |  On | Temporarily do not log binlog when resuming with MySQL, you can temporarily open

+---------------------------------+-------+

Three modes of Binlog logs

Statement level every statement that modifies a SQL record does not need to record the change in each row

Row level records the pattern of each row of data modifications and then modifies the same data on the slave side Bin-log log Large

Mixed above 2 modes of mixing

Mysql> show variables like '%binlog_format% ';

+---------------+-----------+

| variable_name | Value |

+---------------+-----------+

| Binlog_format | STATEMENT |

+---------------+-----------+

mysql> Set Global Binlog_format = ' ROW '; Temporary global changes exit and re-enter

Mysqlbinlog--no-defaults --base64-output=decode-rows-v mysql-bin.000004//view row type log file

binlog_format=Mixed //modify MY.CNF official recommended mode

MyISAM engine Important parameter: Key_buffer_size = 2048M

• Transaction not supported

• Table-Level locking

• Read and write blocking each other

• Only the index is cached

• Read and write faster and consume less resources

• FOREIGN KEY constraints are not supported, but full-text indexing is supported

• is the default storage engine before 5.5.5

Tuning Essentials

• Set the appropriate index

• Adjust read and write priorities to ensure that important operations are prioritized based on real-world requirements

• Enable deferred insertion for improved high-volume write performance

• Try to keep the insert data written to the tail in order to reduce blocking

• Decomposition of large long-time operations, reducing the blocking time of a single operation

• Reduce concurrency, some high concurrency scenarios are queued by application

• For relatively static database data, the full use of query_cache_size or memcached cache service can greatly improve access efficiency

• Count is especially funny when going to a full table scan. Select COUNT (*) from He.quan;

• You can use InnoDB for master-slave synchronization and use the MyISAM engine from the library (not recommended).

Data/mysql

-RW-RW----1 mysql mysql 10630 March 01:04 user.frm//File save table Definition

-RW-RW----1 MySQL mysql 1212 April 6 07:20 user. MYD//Data files

-RW-RW----1 MySQL mysql 2048 April 6 07:27 user. MYI//Index file

Mysql> Show Engines \g;

-RW-RW----1 mysql mysql 134217728 April 7 02:15 ibdata1//innodb shared table space

InnoDB Engine Features

• Support transactions support 4 transaction isolation Levels ACID

• Row-level locking

• Read-write blocking and transaction isolation level related

• Has a very efficient caching feature: the ability to cache indexes and to cache data .

• The entire table and primary key have been stored in a cluster way, forming a balanced tree

• Supports partitioning, table space, similar to Oracle database

• Support for foreign KEY constraints , not supported for full-text indexing 5.5, was supported later.

• Hardware resource requirements are higher than for MyISAM

Tuning Essentials

• Primary key as small as possible

• Avoid full table scans because table locks are used

• Cache all indexes and data as much as possible, improve response speed, and reduce disk IO consumption

• In large batches of small inserts, try to control your own transactions instead of using autocommit auto-commit

• Reasonable set Innodb_flush_log_at_trx_commit parameter value, do not over-pursue security, if =0,log buffer per second will be brushed write log file to disk, commit the transaction without doing anything.

• Avoid primary key updates, as this can result in a lot of data movement

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Mysql> show engines; Engine

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| engine              | Support | Comment                                                           | transactions | xa   |  savepoints |+--------------------+---------+------------------------------------------------------- ---------+--------------+------+------------+| mrg_myisam          | yes     | collection of identical myisam tables                            | no           | no    | no         | |  csv                |  YES     | CSV storage engine                                                | NO           | NO    | no         | |  MEMORY             | YES     | Hash based, stored in memory,  useful for temporary tables      | no            | NO   | NO          | |  BLACKHOLE          | YES      | /dev/null storage engine  (anything you write to it  Disappears)  | NO           | NO    | no         | |  MyISAM             | YES      | MyISAM storage engine                                            | NO            | NO   | NO          | |  FEDERATED          | NO       | Federated MySQL storage engine                                   | NULL          | null | null       | |  ARCHIVE            | YES      | archive storage engine                                           | NO            | no   | no         | |  InnoDB             | DEFAULT  | supports transactions, row-level locking, and foreign keys      | YES          | YES   | yes        | |  PERFORMANCE_SCHEMA | YES     | Performance Schema                                               | NO            | NO   | NO          |+--------------------+---------+----------------------------------------------------- -----------+--------------+------+------------+

/application/mysql/support-files/my-innodb-heavy-4g.cnf

innodb_buffer_pool_size = 2048M // Cache InnoDB table index, data, 70%-80% best to buffer operating system memory when inserting data.

Modify Engine

Mysql> ALTER TABLE test engine = MyISAM; The engine that modifies the test table.

/application/mysql/bin/mysql_convert_table_format--user=root--password=123456-engine=myisam Hequan test

Yum Install Perl-extutils-makemaker

This article is from the "what-all" blog, please be sure to keep this source http://hequan.blog.51cto.com/5701886/1782212

MySQL log + engine notes