Differences between status and variables in mysql Optimization

Differences between status and variables in mysql Optimization

Differences between mysql status and variables
 
First, you can use the following two commands to view the corresponding mysql system parameters.

Show status like '% abc % ';

Show variables like '% abc % ';
 
However, many people do not understand the differences between the two.

Status indicates that the status of the system cannot be changed. It is a parameter of the current running status of the system, which is described as follows:

Mysql> show status like 'innodb _ rows _ % ';

+ ---------------------- + --------- +
| Variable_name | Value |
+ ---------------------- + --------- +
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 1169098 |
| Innodb_rows_read | 7955216 |
| Innodb_rows_updated | 0 |
+ ---------------------- + --------- +

4 rows in set (0.00 sec)

The following sections describe:
 
| Innodb_rows_deleted | 0 | number of rows deleted from the innodb table. The value 0 indicates that no rows have been deleted.

| Innodb_rows_inserted | 1169098 | the number of insert rows in the innodb table, which indicates that 1169098 rows are inserted.

| Innodb_rows_read | 7955216 | number of rows obtained by executing select for the innodb table

| Innodb_rows_updated | 0 | number of rows involved in performing update for the innodb table

The preceding four parameters are the running status parameters of the innodb table. They cannot be manually modified, but can only be updated by the system. The purpose is obviously to tell the dba the current system status, so that the dba can perform optimization, the above four records tell the dba to read more than write at this time (I am executing insert into a select * from a, so the above data is generated), you can consider creating an appropriate index, if the read is 0, if the write size is large, you can consider deleting indexes and so on.

Mysql> show variables like 'query % ';

+ ------------------------------ + ---------- +
| Variable_name | Value |
+ ------------------------------ + ---------- +
| Query_alloc_block_size | 8192 |
| Query_cache_limit | 1048576 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 16777216 |
| Query_cache_type | ON |
| Query_cache_wlock_invalidate | OFF |
| Query_prealloc_size | 8192 |
+ ------------------------------ + ---------- +

7 rows in set (0.00 sec)

View the information about the query cache according to the above mark. In this case, you can optimize the cache configuration according to the status. Note that the system uses the cache configuration information, which can be modified by setting or modifying the configuration file.

Articles you may be interested in

• Differences in the usage of tinyint, smallint, int, and bigint types in mysql
• 30 methods for optimizing SQL statement query in mysql
• Differences between null and not null in mysql and efficiency issues
• Differences between variables and functions in php after the static keyword is added
• Select into from prompt Undeclared variable... solution to the error
• Differences between echo, print, print_r, var_export, and var_dump in php
• Differences between MySQL Database Engine MyISAM and InnoDB
• Differences among scrollHeight, scrollWidth, scrollLeft, and scrolltop in javascript