Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to solve mysql performance problems", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to solve mysql performance problems" this article.

Recently the project has encountered performance problems using mysql. When the data in a single table is more than 400W, the speed of adding, deleting, changing and checking all decreases obviously.

We are a call center, with an average of 20 calls per second, so the most conservative calculation requires 20 insertions into a single table per second, and there are more query operations, so the performance requirements are slightly higher. (a little beside the point.)

If you encounter a problem, you have to solve it. Optimize! The steps for our optimization are as follows (some difficulties encountered in the process):

1. Optimize the index and analyze all the sql statements, especially the slow ones, one by one. (with EXPLAIN) you want all queries to use indexes.

two。 Optimize the database itself and optimize the my.cnf (my.ini) file. But personally, I think the effect is not very obvious (please guide me. )

The my.cnf file is configured as follows. (4-core cpu 4G memory) this my.cnf specific problem specific analysis, configuration, there are a lot of things to pay attention to.

So it needs to be debugged step by step. (never restart mysql.... after a large number of modifications It is possible that the startup failed. )

Java code

[client]

Port=3306

[mysql]

Default-character-set=utf8

[mysqld]

Port=3306

Basedir= "D:/Program Files/MySQL/MySQL Server 5.1 /"

Datadir= "C:/Documents and Settings/All Users/Application Data/MySQL/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=120M

Table_cache=1024M

Tmp_table_size=32M

Thread_cache_size=64

Myisam_max_sort_file_size=100G

Myisam_sort_buffer_size=64M

Key_buffer_size=512M

Max_allowed_packet = 32m

Max_heap_table_size = 32m

Table_open_cache = 512m

Thread_concurrency = 8

Innodb_lock_wait_timeout = 50

Bulk_insert_buffer_size = 64m

Key_cache_block_size=2048

Skip-external-locking

Skip-name-resolve

Read_buffer_size=8M

Read_rnd_buffer_size=8M

Sort_buffer_size=64M

Innodb_additional_mem_pool_size=20M

Innodb_flush_log_at_trx_commit=1

Innodb_log_buffer_size=8M

Innodb_buffer_pool_size=512M

Innodb_log_file_size=24M

Innodb_thread_concurrency=10

3. Sub-table is the best way to solve the problem of large amount of data.

Our my does not meet the performance requirements when we have more than 4 million data in a single table (we have a bit high performance requirements). But the amount of data we generate in one day reaches 2 million of the single table. You can't build a set of tables in one day, can you? After 1 or 2 years, there will be more than 600 sets of tables in the database (there are dozens of tables in one set, that is, tens of thousands of single tables), which looks like a headache.

4. Solve the problem from the business, realize the curve to save the country. (this is also the last method we adopt, which may not be suitable for other businesses.)

Our business is like this, the data is carried forward once an hour to generate statistics.

Finally, it is decided to insert the pipelining data into two sets of pipelining meters (one set is used to save the pipelining data, the other set is used to carry forward the statistics. )

Why divide it into two sets?

For the sake of performance, the set of tables used to hold pipelined data do not operate on it. (it is used to back up data. )

The set of flow meters used to carry forward into statistics are deleted to carry forward after the carryover is completed.

In this way, the amount of data of the carry-over meter will not continue to grow, which ensures the efficiency of the data carry-over.

I hope it will give others a little bit of inspiration.

Please don't laugh at me. My mysql is pirated. No mysql DBA tuned it for me. And no one will order our special mysql for me.

This is the only way to solve the problem of saving the country.

These are all the contents of the article "how to solve mysql performance problems". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.