How to add mysql5 5 running memory

Modern human resource management includes eight modules: human resource planning, personnel recruitment and allocation, training development and implementation, performance appraisal and implementation, compensation and welfare, personnel management, career management and employee relationship management.

[client]

port = 3306

socket = /tmp/

mysqld]

port = 3306

socket = /tmp/

basedir = /usr/local/mysqldatadir = /data/mysqlpid-file = /data/mysql/user = mysql

bind-address =

server-id = 1 indicates that the ordinal number of the machine is 1, which generally means master.

If it has been installed before, the default configuration is as follows:

Default configuration parameters:

Key buffer size: 16mmax allowed packet: 1mtable open cache: 64

sort_buffer_size：512knet_buffer_length：8k

But often fake death, it seems useless to find a lot of optimized configuration solutions on the Internet. After the change, the problem has been significantly improved, and the default configuration is as follows:

Key Buffer Size = 16mmax Allowed Packet = 1mtable Open Cache = 32Sort Buffer Size = 512knet Buffer Length = 8K, so I personally recommend machines with 1g or below, do not install or higher. Of course, if you are an optimization master, you can also tell me how to optimize it to make it more suitable for running on a machine with 1G memory, thank you very much!

We still use two sessions, one session run, which is used to run the main SQL; Another session ps, which is used to make observations of the performance schema:

Reset the statistics in the performance schema, the table size limit of the temporary table depends on the smaller of the parameters tmp table size and max heap table size, we use setting max heap table size as an example in our experiment.

We set the session-level temporal table size to 2M (less than the space used by the temporal table in the last experiment) and execute the sql:using the temporal table

View memory allocation records:

We will find that the memory allocation is slightly greater than 2m, and we guess that the temporary table will consume a little more than the configuration and can be ignored.

View the eigenvalues of a statement:

You can see that the statement uses a temporary table that needs to be dropped once.

So how much disk does this temporary table use?

Redo the experiment, skip it.

Then look at the statistical values for the performance schema:

Several phenomena can be seen:

1.The temporary tablespace is written to .

2.This data is written slowly and gradually after the statement is written.

You can see that the thread on which the data is written is a page clean thread, which is a dirty operation, so that you can understand why the data is being written slowly.

You can also see that the size of each IO operation is 16K, which is the operation of swiping the data page.

Conclusion: As we can see, 1MySQL basically adheres to the Max Heap table size setting, and when the memory is insufficient, the table is directly transferred to disk for storage.

2.Due to the different engines (the in-memory table engine is HEAP, and the in-disk table engine follows the configuration of the Internal TMP Disk Storage Engine), the amount of data written to disk in this experiment is different from the amount of data used in memory in experiment 05.

3.If a temporary table uses disk and the table engine is configured as innodb, even if the temporary table is used in a short-term SQL statement and is released after use, dirty pages will be brushed to the disk after release, consuming part of the I/O.