中文AV资源网,色天堂AVV,久久精品国产交换

由于業(yè)務(wù)原因，遇到了如題所述的業(yè)務(wù)問題，事務(wù)執(zhí)行時(shí)間在30s~50s 不等，效果非常不理想

方案1. jdbc批處理

5w+ 數(shù)據(jù)測試，分別使用了mybatis insert()()（拼接xml）, mybatis的批處理和 jdbc的批處理。

可以看到在jdbc執(zhí)行時(shí)間方面是差不多的，但是在方法執(zhí)行時(shí)間上，批處理要稍微快了一些，但是還是不理想

5w+ 數(shù)據(jù)測試1

5w+ 數(shù)據(jù)測試2

方案2. 優(yōu)化MySQL 參數(shù)

修改 my.ini

innodb_buffer_pool_size :

InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

row data. The bigger you set this the less disk I/O is needed to

access data in tables. On a dedicated database server you may set this

parameter up to 80% of the machine physical memory size. Do not set it

too large, though, because competition of the physical memory may

cause paging in the operating system. Note that on 32bit systems you

might be limited to 2-3.5G of user level memory per process, so do not

set it too high.

Innodb的緩沖池會(huì)緩存數(shù)據(jù)和索引，設(shè)置的越大訪問表中的數(shù)據(jù)所需的磁盤I/O就越少。

修改innodb_buffer_pool_size = 512M測試一下效率，這速度簡直感人！

10w+數(shù)據(jù)測試1

innodb_log_buffer_size :

The size of the buffer InnoDB uses for buffering log data. As soon as

it is full, InnoDB will have to flush it to disk. As it is flushed once per second anyway, it does not make sense to have it very large

(even with long transactions).

表示InnoDB寫入到磁盤上的日志文件時(shí)使用的緩沖區(qū)的字節(jié)數(shù)，默認(rèn)值為8M。當(dāng)緩沖區(qū)充滿時(shí)，InnoDB將刷新數(shù)據(jù)到磁盤。由于它每秒刷新一次，所以將它設(shè)置得非常大是沒有意義的 (即使是長事務(wù))。

innodb_log_file_size :

Size of each log file in a log group. You should set the combined size

of log files to about 25%-100% of your buffer pool size to avoid

unneeded buffer pool flush activity on log file overwrite. However,

note that a larger logfile size will increase the time needed for the

recovery process.

該值越大，緩沖池中必要的檢查點(diǎn)刷新活動(dòng)就會(huì)越少，節(jié)省磁盤I/ O。但是越大的日志文件，mysql的崩潰恢復(fù)就越慢

設(shè)置上述兩個(gè)參數(shù)innodb_log_file_size=64M innodb_log_buffer_size=16M，效率提升的并不明顯。