MySQL database operating system and configuration parameter optimization

• Database structure Optimization

  Horizontal split of the table
  The common horizontal splitting methods are:
  1. Hash the customer_id, if you want to split into 5 tables use mod (customer_id,5) to remove 0-4 values
  2. Save the data to a different table for different Hashid.
  Challenge:
  1. Data query across partitioned tables
  2. Statistics and Background report operation

• Operating system Configuration Optimizations

  Database is based on the operating system, most of MySQL is currently installed on the Linux system, so for some of the operating system parameter configuration will also affect the performance of MySQL, listed below are some of the system configuration.
  Network configuration, to modify the/etc/sysctl.conf file
  Increase the number of queues supported by TCP
  Net.ipv4.tcp_max_syn_backlog = 65535
  Resource recycling when disconnecting is reduced
  Net.ipv4.tcp_max_tw_buckets = 8000
  Net.ipv4.tcp_tw_reuse = 1
  Net.ipv4.tcp_tw_recycle = 1
  Net.ipv4.tcp_fin_timeout = 10

• Restrictions on opening files
  You can use the Ulimit-a directory of the tentative restrictions, you can modify the/etc/security/limits.conf file to add the following to modify the number of open files limit
  *soft Nofile 65535
  *hard Nofile 65535
  In addition, it is best to turn off firewall software such as Iptables,selinux on MySQL server.

• MySQL configuration file

  MySQL configuration files in Linux systems are typically located in/etc/my.cnf
  MySQL configuration file a common parameter description
  innodb_buffer_pool_size
  A very important parameter for configuring the InnoDB buffer pool, if there are only InnoDB tables in the database,
  The recommended configuration is 75% of total memory (this assumes that the server is used only as a MySQL database server).
  SELECT ENGINE,
  ROUND (SUM (data_length + index_length)/1024/1024,1) as ' Total MB ', from INFORMATION_SCHEMA. TABLES WHERE Table_schema Not in
  ("Information_schema", "Performance schema")
  GROUP by ENGINE;
  Innodb_buffer_pool_size>=total MB
  innodb_buffer_pool_instances
  New in MySQL5.5, you can control the number of buffer pools, by default there is only one buffer pool.
  innodb_log_buffer_size
  The size of the InnoDB log buffer is usually not too large because the log is refreshed for the longest time per second.
  Innodb_flush_log_at_trx_commit
  The key parameters have a great impact on the IO of InnoDB. The default value is 1, you can take 0,1,2 three values, the general recommendation is set to 2, but if the data security requirements are higher, use the default value of 1.
  innodb_read_io_threads
  innodb_write_io_threads
  The above two parameters determine the number of IO processes InnoDB read and write, and the default is 4.
  innodb_file_per_table
  Key ParametersControl InnoDB Each table uses a separate tablespace, which is off by default, that is, all tables are built into the shared table space, and are recommended to be on.
  Innodb_stats_on_metadata
  Determines under what circumstances MySQL will refresh the statistics of the InnoDB table.
  max_connections
  Many developers will meet the "Mysql:error 1040:too many connections" anomalies, one of the reasons for this situation is that the traffic is too high, the MySQL server can not resist, this time to consider increasing the decentralized reading pressure from the server Another reason is that the max_connections value in the MySQL configuration file is too small.
  First, let's look at the maximum number of connections for MySQL:
  Mysql> Show variables like '%max_connections% ';
  +-----------------+-------+
  | variable_name | Value |
  +-----------------+-------+
  | max_connections | 151 |
  +-----------------+-------+
  1 row in Set (0.00 sec)

Second, view the maximum number of connections that the server responds to:
Mysql> show global status like ' Max_used_connections ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| max_used_connections | 2 |
+----------------------+-------+
1 row in Set (0.00 sec)

You can see that the maximum number of connections to the server response is 2, which is much lower than the maximum allowable connection value for the MySQL server.
For MySQL server maximum connection value of the set range is ideal: The server response to the maximum connection value of the server is higher than the number of connection values above 10%, if under 10%, the MySQL server maximum connection limit value is set too high.
Max_used_connections/max_connections 100% = 2/151 100%≈1%

We can see that the ratio is much lower than 10% (because this is a local test server, the result value is not too much reference, you can set the maximum number of connections according to the actual situation).
Look at Yourself Linode VPS now (time: 2013-11-13-23:40:11) result value:
mysql> show variables like '%max_connections% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in Set (0.19 sec)
Mysql> show global status like ' Max_used_connections ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| max_used_connections | 44 |
+----------------------+-------+
1 row in Set (0.17 sec)

The maximum number of connections here is about 30% of the total number of connections.
Above we know how to view the maximum number of MySQL server connection, and know how to determine whether the value is reasonable, let us explain how to set the maximum value of the connection.
Method 1:
Mysql> set GLOBAL max_connections=256;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show variables like '%max_connections% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
1 row in Set (0.00 sec)

Method 2:
Modify the MySQL configuration file my.cnf, add or modify the Max_connections value in the [Mysqld] segment:
max_connections=128
Restart the MySQL service.

Reference:
MySQL optimized connection count method to prevent excessive traffic
Performance optimized MySQL optimization

From for notes (Wiz)

MySQL database operating system and configuration parameter optimization