In recent study innodb_flush_log_at_trx_commit this configuration items affect the performance of mysql, speaking, reading and writing,
Pressure test before using sysbench built three table, table structure is exactly the same, just the table there are differences in the amount of data
T500: data of 500 w
T2k: data of 2000 w
T5000: data of 5000 w

After building the table, use the following statements to the database for pressure measurement:
Sysbench -- test=oltp, oltp - table - size=5000000 - oltp - read - only=off - init - RNG=on, num - threads=8, Max - requests=0 - oltp - dist - type=uniform - Max - time=150 - oltp - table - name=t500 - mysql db=test - mysql - user=root - mysql - socket=/data/mysql/test/3333/mysql. The sock run & gt;/TMP/result500. Log

Sysbench -- test=oltp, oltp - table - size=20000000 - oltp - read - only=off - init - RNG=on, num - threads=8, Max - requests=0 - oltp - dist - type=uniform - Max - time=150 - oltp - table - name=t2k - mysql db=test - mysql - user=root - mysql - socket=/data/mysql/test/3333/mysql. The sock run & gt;/TMP/result2k. Log

Sysbench -- test=oltp, oltp - table - size=50000000 - oltp - read - only=off - init - RNG=on, num - threads=8, Max - requests=0 - oltp - dist - type=uniform - Max - time=150 - oltp - table - name=t5000 - mysql db=test - mysql - user=root - mysql - socket=/data/mysql/test/3333/mysql. The sock run & gt;/TMP/result5000. Log

The following screenshots of the summary results for partial pressure test:




Can be found that when the data volume is 500 w and 2000 w, speed is obviously disk write & gt; Disk read it, but when the amount of data of 5000 w, only to find that disk read is very high, and the disk writes is become very low
Soon because I am new to mysql, the internal mechanism of mysql does not understand, so don't know why will appear this kind of phenomenon, I hope you glad, thank you!

CodePudding user response:

Thermal data & lt; Database cache cache hit high vice hit low need to go to disk to find the data

CodePudding user response:

reference 1st floor rucypli response:

hot data & lt; Database cache cache hit high vice hit low need to go to disk for data

I think not cache hit ratio of the problem, because although is random, speaking, reading and writing, but the select statement is basic about the same, I tested many times, every time is a disk read high;
According to my understanding, if because of the cache hit ratio, so the back several times the same test, should be corresponding to reduce disk read, but did not,,,

CodePudding user response:

Check the database in the configuration file for database cache configuration is how old, seems to be commonly is 128 k,