Mysql high-concurrency update count

Problem: The function is the link click count. The other two counts are updated, and two content tables are inserted. Only the corresponding IDs of the links are inserted, which are divided into 4 databases and 128 tables in total, mysql has 50 million insert and 50 million update operations every day. If the load is too high, you need to consider a solution, merge the Count update operations, or add cache, and flush to the disk DB after a certain period of time.

There are currently batch processing programs that use the java. SQL package native class, but after a while, java. SQL. BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction error occurs.

Solution in progress, not completed.

Solution

Show global status like 'com % 'query the number of crud times of the current db

Mysqladmin-htestdb-P3306-utest-p-S/tmp/mysql. sock-r-I 10 extended-status | egrep "Com_select | Com_insert | Com_update"

Count the number of insert updates per 10 seconds

| Com_inserts | 5210 |
| Com_insert_select | 0 |
| Com_selected | 2033 |
| Com_update | 0 |
| Com_update_multi | 0 |

 

Java. SQL. BatchUpdateException: The table 'xxx' is full
The tmp_table_size parameter and max_heap_table_size parameter are incorrect.

After checking the hard disk, it is found that the hard disk space is insufficient, 100% is used, and useless files are deleted