MySQL large Memory high performance optimization scheme

MySQL optimization under 8G memory

Try the following settings:
Key_buffer = 3840M
Max_allowed_packet = 16M
Table_cache = 1024
Sort_buffer_size = 32M
Read_buffer_size = 32M
Read_rnd_buffer_size = 32M
Myisam_sort_buffer_size = 256M
Thread_cache_size = 32
Query_cache_size = 256M
# Try number of CPU ' s*2 for thread_concurrency
Thread_concurrency = 8

One of the Key_buffer_size caps is 4G, no more.
But actually, in order to optimize the performance of MySQL, the allocation of memory needs to be debugged.

MySQL large memory high performance optimization

The impact of server hardware on MySQL performance

1, disk seek capacity (disk I/O), with the current high speed SCSI hard drive (7200 rpm), for example, this hard disk theoretically seek 7,200 times per second, this is the physical characteristics of the decision, there is no way to change. MySQL every second in a large number of complex query operations, on the disk read and write volume imaginable. As a result, disk I/O is generally considered to be one of the biggest constraints on MySQL performance, and for discuz! forums with daily average visits above 1 million PV, MySQL performance will be very low due to disk I/O constraints! To address this constraint, consider the following solutions: Use a raid-0+1 disk array, and be careful not to attempt to use Raid-5,mysql on the RAID-5 disk array not as fast as you expect.

2, CPU for MySQL application, recommend the use of S.M.P. Architecture of the multi-channel symmetric CPU, for example: You can use the two Intel Xeon 3.6GHz CPU, now I recommend using 4U server to specialize in database server, not only for MySQL.

3, physical memory for a database server using MySQL, server memory recommended not less than 2GB, the recommended use of 4GB of physical memory, but the memory for the current server can be said to be an overlooked problem, The work encountered a high-end server basically memory is more than 32G.

MySQL's own factors

When the above server hardware constraints are resolved, let's take a look at how MySQL's own optimization operates. The optimization of MySQL itself is mainly to adjust the parameters of its configuration file MY.CNF. Here are some of the parameters that have a greater impact on performance. Since the optimization settings of the my.cnf file are closely related to the server hardware configuration, we specify an imaginary server hardware environment:
Cpu:2 Intel Xeon 2.4GHz
Memory: 4GB DDR
Hard disk: SCSI 73GB (a common 2U server).

Below, we are based on the above hardware configuration combined with a MY.CNF has been optimized to explain:

The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, and the other paragraphs have little effect on the performance of the MySQL operation, so
and ignored.

[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reduce the chance of error increase stability.

Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, use this option to eliminate the time that MySQL makes DNS resolution. Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!

Back_log = 384
The value of the #back_log parameter indicates how many requests can be on the stack in the short time before MySQL temporarily stops responding to the new request. If the system has many connections in a short time, you need to increase the value of the parameter, which specifies the size of the incoming TCP/IP connection's listening queue. The different operating systems have their own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. The default value is 50. For Linux systems It is recommended to set to an integer less than 512.

Key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance. This parameter can be set to 256M or 384M for servers with around 4GB. Note: The value of this parameter set too large will be the overall efficiency of the server down!

Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小. Note: The allocated memory for this parameter is per connection exclusive, and if there are 100 connections, the total sorted buffer size that is actually allocated is 100x6 = 600MB. Therefore, for the existence of around 4GB server recommended set to 6-8m.

Read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

Join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M
# Specifies the size of the MySQL query buffer. Can be observed in the MySQL console, if the value of the qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient, if the value of qcache_hits is very large, it indicates that the query buffer use is very frequent, if the value is small but will affect efficiency, Then consider not querying the buffer; qcache_free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer.

Tmp_table_size = 256M
Max_connections = 768
#指定MySQL允许的最大连接进程数. You need to increase the value of a too Many connections error message when you visit the forum frequently.

Max_connect_errors = 10000000
Wait_timeout = 10
#指定一个请求的最大连接时间, a server with about 4GB of memory can be set to 5-10.

Thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量 *2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4*2=8

Skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP connection, do not turn this option on if the Web server is accessing the MySQL database server in a remote connection way! Otherwise you will not be able to connect properly!

table_cache=1024
#物理内存越大, the larger the setting. Defaults to 2402, to 512-1024 best

innodb_additional_mem_pool_size=4m
#默认为2M

Innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, defaults to 1

Innodb_log_buffer_size=2m
#默认为1M

Innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几, it is recommended to use the default generally 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m
#默认为16M, tune to 64-256.
read_buffer_size=4m
#默认为64K
read_rnd_buffer_size=16m
#默认为256K
sort_buffer_size=32m
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32m

It is noteworthy that many situations require concrete analysis.

If the key_reads is too big, then should my.cnf in the key_buffer_size, keep key_reads/key_read_requests at least 1/100, the smaller the better.

Recommendations for improving performance

1. If the opened_tables is too large, the table_cache in the my.cnf should be bigger.
2. If the key_reads is too large, the my.cnf key_buffer_size should be larger. Can calculate the cache failure rate with key_reads/key_read_requests
3. If the handler_read_rnd is too large, then many queries in the SQL statement you write are to scan the entire table without playing the role of the key
4. If the threads_created is too large, it is necessary to increase the value of thread_cache_size in MY.CNF. You can use Threads_created/connections to calculate cache hit rates
5. If the created_tmp_disk_tables is too large, it is necessary to increase the value of tmp_table_size in the MY.CNF and replace the disk-based based temporary table

In addition to machine optimization, our SQL statements can also be optimized

1. Optimize your query for query caching

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and it is handled by the MySQL database engine. When many of the same queries are executed many times, the query results are placed in a cache so that subsequent queries do not directly access the cached results without the action table.

The main problem here is that it's easy to ignore for programmers. Because some of our query statements will let MySQL not use caching. Take a look at the following example:

Query Cache not open
$r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");

Open Query Cache
$today = Date ("y-m-d");
$r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");

The difference between the two SQL statements above is curdate (), and MySQL's query cache does not work on this function. So, like now () and RAND () or some other SQL functions, they don't open the query cache because the return of these functions is variable. So all you need is to use a variable instead of the MySQL function to turn on the cache.

2. EXPLAIN your SELECT query

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze your query statements or the performance bottlenecks of the table structure.

EXPLAIN's query results will also tell you how your index keys are being used, how your data tables are searched and sorted ... Wait, wait.

Pick one of your SELECT statements (recommend picking the most complex, multiple table joins) and add the keyword explain to the front. You can use phpMyAdmin to do this. Then, you'll see a table. In the following example, we forget to add the group_id index and have a table join:

When we index the group_id field:

As we can see, the previous result shows a search of 7883 rows, and the last one searches only 9 and 16 rows of two tables. Viewing the Rows column allows us to identify potential performance issues.

3. Use LIMIT 1 when only one row of data is used

There are times when you query the table and you already know that there will only be one result, but because you may need to fetch the cursor, or you might check the number of records returned.

In this case, adding LIMIT 1 can increase performance. In this way, the MySQL database engine stops searching after finding a piece of data, rather than continuing to look back at the next record-keeping data.

The following example, just to find out if there are "China" users, it is obvious that the latter will be more efficient than the previous one. (Note that the first one is select * and the second is select 1)

Not efficient:
$r = mysql_query ("SELECT * from user WHERE country = '");
if (mysql_num_rows ($r) > 0) {
// ...
}

To be efficient:
$r = mysql_query ("Select 1 from user WHERE country = ' LIMIT 1");
if (mysql_num_rows ($r) > 0) {
// ...
}

4. Jianjian Index for search word

An index is not necessarily a primary key or a unique field. If you have a field in your table that you will always use to do a search, then make an index of it.

From above you can see the search string "last_name like ' a% '", one is indexed, one is no index, performance is about 4 times times worse.

In addition, you should also need to know what kind of search can not use the normal index. For example, when you need to search for a word in a large article, such as "WHERE post_content like '%apple% '", indexing can be meaningless. You may need to use a MySQL Full-text index or make an index yourself (say: search keywords or tag or something)

5. Use an example of a similar type in a join table and index it

If your application has a lot of join queries, you should be sure that the fields of join in the two tables have been indexed. In this way, within MySQL, you will start a mechanism to optimize the SQL statements for your join.

Also, these fields that are used for joins should be of the same type. For example, if you were to join a DECIMAL field with an INT field, MySQL would not be able to use their index. For those string types, it is also necessary to have the same character set. (The character set of two tables may not be the same)

Find company in State
$r = mysql_query ("Select Company_Name from Users
Left JOIN companies on (users.state = companies.state)
WHERE users.id = $user _id ");

The two state fields should be indexed, and should be of comparable type, the same character set.

6. Never order by RAND ()

Want to disrupt the returned data row? Pick a random data? I don't know who invented the usage, but many beginners like it. But you don't know what a terrible performance problem this is.

If you really want to disrupt the data rows that you've returned, there are n ways you can achieve that. Using this allows you to have an exponential decline in the performance of your database. The problem here is that MySQL will have to perform the rand () function (CPU time), and this is for each row of records to be recorded and then sorted. Even if you use the limit 1 it doesn't help (because you want to sort)

The following example randomly picks a record

Don't do this:
$r = mysql_query ("Select username from user order by RAND () LIMIT 1");

This is going to be better:
$r = mysql_query ("SELECT count (*) from user");
$d = Mysql_fetch_row ($r);
$rand = Mt_rand (0, $d [0]-1);

$r = mysql_query ("Select username from user LIMIT $rand, 1");

7. Avoid SELECT *

The more data you read from the database, the slower the query will become. And, if your database server and Web server are two separate servers, this will also increase the load on the network transport.

So, you should develop a good habit of taking whatever you need.

Not recommended
$r = mysql_query ("SELECT * from user WHERE user_id = 1");
$d = Mysql_fetch_assoc ($r);
echo "Welcome {$d [' username ']}";

Recommended
$r = mysql_query ("Select username from user WHERE user_id = 1");
$d = Mysql_fetch_assoc ($r);
echo "Welcome {$d [' username ']}";

8. Always set an ID for each table

We should set an ID for each table in the database as its primary key, and the best is an int type (recommended to use unsigned) and set up an automatically added Auto_increment flag.

Even if it's you. The Users table has a field with a primary key called "email," and you don't let it be the primary key. Using the VARCHAR type to use as a primary key can degrade performance. In addition, in your program, you should use the ID of the table to construct your data structure.

Also, under the MySQL data engine, there are some operations that require the use of primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ...

Here, only one exception is the "foreign key" of the association table, that is, the primary key of the table, which is composed of the primary keys of several other tables. We call this the "foreign key". For example: There is a "student table" with the student ID, there is a "timetable" with the course ID, then the "score sheet" is the "association table", which is associated with the student table and curriculum, in the score sheet, the student ID and course ID is called "foreign key" which together form the primary key.
9. Use ENUM instead of VARCHAR

The ENUM type is very fast and compact. In practice, it saves TINYINT, but its appearance is displayed as a string. As a result, using this field to make a list of options becomes quite perfect.

If you have a field, such as "gender", "Country", "nation", "state" or "department", you know that the values of these fields are limited and fixed, then you should use an ENUM instead of a VARCHAR.

MySQL also has a "suggestion" (see article tenth) that tells you how to rearrange your table structure. When you have a VARCHAR field, this suggestion will tell you to change it to an ENUM type. Use PROCEDURE analyse () you can get relevant advice.
10. Recommendations from PROCEDURE analyse ()

PROCEDURE analyse () will let MySQL help you analyze your field and its actual data, and will give you some useful advice. These recommendations become useful only if you have actual data in the table, because there are some big decisions that need to be based on data.

For example, if you create an INT field as your primary key, however there is not much data, then PROCEDURE analyse () will suggest that you change the type of the field to Mediumint. Or you use a VARCHAR field, because there's not much data, you might get a suggestion to change it to an ENUM. All these proposals may be due to the fact that there is not enough data, so the decision-making is not accurate enough.

In phpMyAdmin, you can view these suggestions by clicking "Propose table Structure" while viewing the table.

It is important to note that these are only suggestions that will become accurate only when the data in your table is growing. Be sure to remember that you are the last person to make a decision.
11. Use not NULL as much as possible

Unless you have a very special reason to use null values, you should always keep your fields not NULL. This may seem a bit controversial, please look down.

First, ask yourself how different the "Empty" and "null" are (if it is int, that is 0 and null)? If you feel that there is no difference between them, then you should not use NULL. (You know what?) In Oracle, NULL and Empty strings are the same! )

Do not assume that NULL does not require space and that it requires extra space, and that your program will be more complex when you compare it. Of course, this is not to say that you cannot use NULL, the reality is very complex, there will still be some cases, you need to use null values.

The following is excerpted from MySQL's own documentation:

"NULL columns require additional spaces in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra and rounded up to the nearest byte.

Prepared statements

Prepared statements is like a stored procedure, a collection of SQL statements running in the background, and we can derive a lot of benefits from using Prepared statements, whether it's a performance issue or a security issue.

Prepared statements can check some of the variables you bind so that you can protect your program from "SQL injection" attacks. Of course, you can also manually check your variables, however, manual checks are prone to problems and are often forgotten by programmers. This is a better problem when we use some framework or ORM.

In terms of performance, when an identical query is used more than once, this can give you a considerable performance advantage. You can define some parameters for these prepared statements, and MySQL will parse it only once.

Although the latest version of MySQL in the transmission prepared statements is using the binary situation, so this will make the network transport very efficient.

There are, of course, some cases where we need to avoid using prepared statements because it does not support query caching. But it is said that version 5.1 was supported later.

To use prepared statements in PHP, you can view its manual: Mysqli extensions or use of database abstraction layers, such as: PDO.

Create PREPARED statement
if ($stmt = $mysqli->prepare ("Select username from user WHERE state=?")) {

Binding parameters
$stmt->bind_param ("s", $state);

Perform
$stmt->execute ();

Binding results
$stmt->bind_result ($username);

Move a cursor
$stmt->fetch ();

printf ("%s is from%sn", $username, $state);

$stmt->close ();
}

13. No buffer query

Normally, when you execute an SQL statement in your script, your program stops there until the SQL statement is returned, and then your program continues to execute. You can use the no buffer query to change this behavior.

In this case, there is a very good description in the PHP Documentation: the Mysql_unbuffered_query () function:

"Mysql_unbuffered_query () sends the SQL query query to MySQL without automatically fetching and buffering the result rows As mysql_query () does. This saves a considerable amount of memory with SQL queries that produce large the result sets, and the can start working on t He result set immediately after the the "I" has been retrieved as you don ' t have to wait until the complete SQL query ha s been performed. "

The above sentence translates to say that mysql_unbuffered_query () sends an SQL statement to MySQL instead of automatically fethch and caches the results like mysql_query (). This can save a lot of considerable memory, especially those that produce a lot of results, and you don't have to wait until all the results are returned, and you can start working on the query results right away when the first row of data returns.

However, there are some limitations. Because you either read all the rows or you want to call Mysql_free_result () to clear the results before you make the next query. Also, mysql_num_rows () or Mysql_data_seek () will not be available. Therefore, you need to consider carefully whether to use a query without buffering.
14. Save IP Address as UNSIGNED INT

Many programmers create a VARCHAR (15) field to hold a string of IP instead of a reshaped IP. If you use plastic to store, you need only 4 bytes, and you can have a fixed-length field. Also, this gives you the advantage of the query, especially if you need to use the Where condition: IP between Ip1 and IP2.

We have to use the unsigned INT because the IP address uses the entire 32-bit unsigned integer.

And your query, you can use Inet_aton () to convert a string IP into an integer and use Inet_ntoa () to turn an integer into a string IP. In PHP, there are also functions such as Ip2long () and Long2ip ().
1 $r = "UPDATE users SET IP = Inet_aton (' {$_server[' remote_addr ']} ') WHERE user_id = $user _id";
15. Fixed-length table will be faster

If all the fields in the table are of fixed length, the entire table is considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as you include one of these fields, the table is not a "fixed-length static table", so that the MySQL engine handles it in another way.

Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths can easily compute the next data offset, so the readings will be quick. And if the field is not fixed long, then, each time to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more easily cached and rebuilt. However, the only side effect is that fixed-length fields waste a bit of space, because a set of long fields, whether you use it or not, he has to allocate so much space.

Using the vertical split technique (see next), you can split your table into two that are fixed-length and one that is indefinite.
16. Vertical Segmentation

Vertical segmentation is a way to change a table in a database into several tables, which can reduce the complexity of the table and the number of fields to achieve optimization. (Previously, in a bank project, saw a table has more than 100 fields, very scary)

Example one: There is a home address in the Users table, this field is an optional field, and you do not need to read or overwrite this field frequently, except for personal information while you are operating the database. So why not put him in the other table? This will make your table have better performance, we think is not, a lot of time, I for the user table, only the user ID, username, password, user role, etc. will be often used. Smaller tables always have good performance.

Example two: You have a field called "Last_login" that will be updated every time a user logs on. However, each update causes the table's query cache to be emptied. So, you can put this field in another table so that it doesn't affect your constant reading of the user ID, username, and user role, because the query cache will help you to add a lot of performance.

Also, you need to be aware that the tables that are formed by the separated fields do not frequently join them, otherwise, this performance will be worse than the undivided, and it will be a drop in the extreme number.
17. Split large DELETE or INSERT statement

If you need to perform a large DELETE or INSERT query on an online Web site, you need to be very careful to avoid your actions so that your entire site stops accordingly. Because these two operations will lock the table, the table is locked, no other operation can enter.

Apache will have a lot of child processes or threads. So, it works fairly efficiently, and our servers don't want to have too many child processes, threads and database links, which are great for server resources, especially memory.

If you lock your watch for a while, for example 30 seconds, then for a highly visited site, this 30-second accumulation of access processes/threads, database links, the number of open files, may not only let you park Web services crash, but also may make your entire server immediately? Soy, excuse me?/P >

So, if you have a big deal, you're going to have to split it up, and using the LIMIT condition is a good way to do it. Here is an example:

while (1) {
Only 1000 at a time.
mysql_query ("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT 1000");
if (mysql_affected_rows () = = 0) {
There's no delete, quit!
Break
}
I have to take a break every time.
Usleep (50000);
}

18. The smaller the column, the quicker it will be.

For most database engines, hard disk operations can be the most significant bottleneck. So, getting your data compact can be very helpful in this case, because it reduces access to the hard disk.

See the MySQL documentation Storage Requirements View all data types.

If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to make the key, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If you don't need to record time, using DATE is much better than DATETIME.

Of course, you also need to leave enough space for expansion, otherwise, you will do this later, you'll die very difficult to see, refer to the example of Slashdot (November 06, 2009), a simple ALTER TABLE statement took 3 hours, because there are 16 million data.
19. Choose the right storage engine

There are two storage engines MyISAM and InnoDB in MySQL, and each engine has its pros and cons. Cool Shell before the article "Mysql:innodb or MyISAM?" Discussion and this matter.

MyISAM is suitable for some applications that require a large number of queries, but it is not good for a large number of write operations. Even you just need to update a field, the entire table will be locked up, and other processes, the read process can not operate until the read operation is complete. In addition, MyISAM is extremely fast for calculations such as SELECT COUNT (*).

The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM. He is it supports "row lock", so in write operation more of time, will be more outstanding. And, he also supports more advanced applications, such as: transactions.

Here's a MySQL manual.

* target= "_blank" MyISAM Storage Engine
* InnoDB Storage Engine

20. Use an Object-relational mapper (relational Mapper)

Using ORM (Object relational Mapper), you can gain a reliable performance increase. All the things an ORM can do can be written out manually. However, this requires a senior expert.

ORM's most important is "Lazy Loading", that is to say, only when the need to fetch value of the time will go to really do. But you also need to be careful about the side effects of this mechanism, because it's very likely that it will degrade performance by creating lots and lots of small queries.

ORM can also package your SQL statements into a single transaction, which is much faster than executing them alone.

At present, the personal favorite of the PHP orm is: Doctrine.
21. Beware of "permanent links"

The purpose of "permanent link" is to reduce the number of times you re-create a MySQL link. When a link is created, it will always be in the state of the connection, even if the database operation is over. And since our Apache began reusing its subprocess-that is, the next HTTP request will reuse the Apache subprocess and reuse the same MySQL link.