Mysql performance problems

Recently, the project encountered performance problems when using the mysql database. When a single table has more than million data, the speed of adding, deleting, modifying, and querying is significantly reduced.
We are a call center, and we need to process 20 calls in one second on average. Therefore, the most conservative calculation requires 20 insert operations on a single table in one second, and more query operations, therefore, the performance requirement is slightly higher. (A bit out of question ....)
 
Solve the problem and optimize it! The optimization steps are as follows (the difficulties encountered in the process are omitted ):
1. Optimize indexes and analyze all SQL statements, especially slow ones, one by one. (Using EXPLAIN) Make all queries use indexes.
2. Optimize the mysql database and optimize the my. cnf (my. ini) file. However, I personally think the effect is not very obvious (please instruct me .)
The configuration of the my. cnf file is as follows. (4-core cpu, 4 GB memory) the specific problem of my. cnf is analyzed and configured with many things.
So you need to debug it step by step. (Do not restart mysql after modification .)
 
Java code
[Client]
Port = 3306
[Mysql]
Default-character-set = utf8
 
[Mysqld]
Port = 3306
Basedir = "D:/Program Files/MySQL Server 5.1 /"
Datadir = "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.1/Data /"
Default-character-set = utf8
Default-storage-engine = INNODB
SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"
Max_connections = 1000
Query_cache_size = 120 M
Table_cache = 1024 M
Tmp_table_size = 32 M
Thread_cache_size = 64
Myisam_max_sort_file_size = 100G
Myisam_sort_buffer_size = 64 M
Key_buffer_size = 512 M
 
Max_allowed_packet = 32 M
Max_heap_table_size = 32 M
Table_open_cache = 512 M
Thread_concurrency = 8
Innodb_lock_wait_timeout = 50
Bulk_insert_buffer_size = 64 M
Key_cache_block_size = 2048
 
Skip-external-locking
Skip-name-resolve
 
Read_buffer_size = 8 M
Read_rnd_buffer_size = 8 M
 
Sort_buffer_size = 64 M
 
Innodb_additional_mem_pool_size = 20 M
Innodb_flush_log_at_trx_commit = 1
Innodb_log_buffer_size = 8 M
Innodb_buffer_pool_size = 512 M
Innodb_log_file_size = 24 M
Innodb_thread_concurrency = 10
 
3. Table sharding is the best solution for solving large data volumes.
Our mysql database does not meet the performance requirements when more than 4 million of the data is in a single table (we have high performance requirements ). However, the data volume generated in one day has reached 2 million in a single table .. You can't create a set of tables in one day, right ?? In the past two or two years, the database will have more than 600 sets of tables (dozens of tables in a set, that is, tens of thousands of single tables). It is a headache .....
 
4. Solve the problem from the business perspective and save the nation through the curve. (This is the last method we used. This method is not necessarily applicable to other businesses)
Our business is like this. We carry forward the flow data once an hour to generate statistical data.
At last, we decided to insert two sets of streaming water meters (one for saving the streaming data and the other for carrying forward the statistical data .)
Why should we divide it into two sets?
For the sake of performance, the set of tables used to save the flow data will not be operated on. (Used to back up data .)
The streaming water meter used to carry forward data into statistical data will delete the data to carry forward after the carry-over is completed.
In this way, the data volume of the streaming water meter used for the transfer will not continue to grow, which ensures the efficiency of data transfer.
I hope to give some inspiration to others ......
Please do not laugh at me .. My mysql is pirated... I have no mysql DBA for tuning. No one will order our dedicated mysql for me
This is the only way to save the country ....

Author "xsxjb"