>>
Site Map
>>
Forums
>>
PHP+MySql
Forum module - topics in forum:
PHP+MySql - Coding mã nguồn mở rât thông dụng để làm web, và hệ thống cơ sỏ dữ liệu rất mạnh duợc ưa chuộng
84 thủ thuật tăng tốc sử lý SQL and tốc độ mysql server
This article is meant to be an easy and relatively safe way to enhance mysql performance. It is not meant to be a complete guide to tuning MySQL. Fully optimizing MySQL takes both time and effort since every application has different requirements. The Debian MySQL packages ship with very conservative memory usage settings and the same is probably true for other Linux distributions and the Windows binaries. If you loosen these settings up a little, MySQL will perform much faster under ordinary circumstances.
The items are ordered on impact, high to low. Feedback and hints will be greatly appreciated.
Key Buffer
The key buffer holds the indexes of tables in memory and a bigger key buffer results in faster row lookups. Adjust according to your own needs. Bigger is better, but prevent swapping at all costs. A good rule of thumb seems to be to use 1/4 of system memory.
key_buffer = 128M
Query Cache
This is where the magic happens. Well, not magic really, just plain old caching. Keeping the result of queries in memory until they are invalidated by additional writes enhances performance by magnitudes. The query_cache_size, as the name suggests, is the total size of memory available to query caching. The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached. Setting this value too high might prevent a lot of smaller queries to be cached. Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources. Adjust according to your own needs and memory available:
query_cache_size = 128MB
query_cache_limit = 4MB
Table Cache
An important variable if your application accesses many tables. It is the number of tables a thread can keep open at the same time. A value of 512 should do no harm.
table_cache = 512
Sort Buffers
sort_buffer_size (the variable previously known as sort_buffer), used for grouping and sorting and is a per-thread buffer. If the buffer can not hold the data to be sorted, a sort is performed on disk. Watch out for making this too large as the buffer is allocated for every thread that needs sorting and with many sorts it can easily consume all your memory.
sort_buffer_size = 32M
myisam_sort_buffer_size = 32M
The InnoDB Engine
Most people do not use the InnoDB engine in MySQL and use MyISAM instead. Since MySQL reserves memory for this engine, you are better off without it. If you need InnoDB, you can find more on its settings in the official MySQL docs.
Add `skip-innodb’ to my.cnf to disable the engine.
Binary Logging
MySQL has a few powerful features. Replicating data changes to a second server is one of them. MySQL keeps a log file of data changes which is used for this purpose. If you do not use replication or use the file as incremental backup, you can disable it. This will save you expensive disk write actions for every change to your data. For applications that have a lot of frequently updated data, this can be quite a performance boost. According to the official docs, this will generally result in just a 1% boost but it’s an easy gain if you do not need the log. Read more about the binary log here. Comment the following line:
log-bin = /var/log/mysql/mysql-bin.log
Temporary Tables
Temporary tables are used for sorting and grouping. The buffer is created on demand so watch out for setting this too high here as well. If the buffer cannot accomodate the data, a temp file is used on disk instead.
tmp_table_size = 64MB
Delayed Writing
This setting can greatly improve writing or updating data to a table. Instead of directly committing data to the disk, MySQL queues writes and returns write queries immediately. Be very very careful with this, because this also means that in case of a power failure or crash, you lose data. You can use this for logging if you don’t mind losing a couple of rows in case of a crash.
delay_key_write = 1
Connection Timeout
This is a little tweak that determines the closing of sleeping connections. The default is one hour and is often too long for practical purposes. I often set this at one minute instead (60).
wait_timeout = 60
The above settings are just to make mysql a little faster in general. You can get much better speed improvements by optimizing the database itself. Setting the correct indexes on tables can be a life-saver.
Vi dụ hữu hiệu tối ưu mysql
After 3 months of tweaking here is how I optimized to pull off these numbers with only a single dual Xeon 3.2ghz server with 4GB of memory. This is a very simplified guide and I am very far from being an expert - so this only reflects my personal success with this configuration. At the bottom of the article is a script I use to test performance.
Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options. Before we get started I suggest you get aquainted with the my.cnf file as well as the tuning parameters within it.
Here is the my.cnf I use:
| Quote: : |
[mysqld]
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
|
Let’s just look at the important bits.
max_connections = 500 – I use a tool (see below) to check how many current connections I have, and under very heavy load (2000 simultaneous users) I rarely hit 400 concurrent connections to the database. This is because most connections only last for a few milliseconds.
key_buffer = 384M - When tuning a MySQL server, key_buffer_size is very important. This number works well for me and with the mysqlreport script I rarely use 50% of the available memory.
table_cache = 1800 – After key_buffer the next most important variable is your table cache. Again this is set for vBulletin so you may be able to significantly reduce this value depending on the number of tables in your database.
wait_timeout = 7200 – This variable determines the timeout in seconds before mysql will dump a connection. If set to low you will likely receive mySQL server has gone away errors in your log, which in vBulletin’s case is quite common.
max_allowed_packet = 16M – Again if set to low (the default is 8M) users will likely experience errors. 16M has always worked fine for my production environments.
You can grab a mySQL performance script from the guys at hackmysql.com.
http://hackmysql.com/mysqlreport
I use it to tell me how the database is performing under load. You can run this from any shell when you are loaded with traffic. Nothing fancy but should give you an idea.
If you run into problems mtop is a great tool for monitoring a live mySQL server. In particular mtop: http://mtop.sourceforge.net/
will show you the queries which are taking the most amount of time to complete. Thanks to konforce on digg for pointing that out.
Don’t forget mySQL’s own lengthy and dull performance tuning whitepaper. http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
