MySQL性能优化之参数配置
通过根据服务器目前状况,修改MySQL的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。
1、目的:
通过根据服务器目前状况,修改MySQL的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。
2、服务器参数:
32G内存、4个CPU,每个CPU 8核。
3、MySQL目前安装状况。
MySQL目前安装,用的是MySQL默认的最大支持配置。拷贝的是my-huge.cnf.编码已修改为UTF-8.具体修改及安装MySQL,可以参考>帮助文档 。
4、修改MySQL配置
打开MySQL配置文件my.cnf
vi /etc/my.cnf
4.1 MySQL非缓存参数变量介绍及修改
4.1.1修改back_log参数值:由默认的50修改为500.(每个连接256kb,占用:125M)
back_log=500
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效,查看当前系统的TCP/IP连接的侦听队列的大小命令:cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。修改系统内核参数,)
查看mysql 当前系统默认back_log值,命令:
show variables like 'back_log'; 查看当前数量
4.1.2修改wait_timeout参数值,由默认的8小时,修改为30分钟。(本次不用)
wait_timeout=1800(单位为妙)
我对wait-timeout这个参数的理解:MySQL客户端的数据库连接闲置最大时间值。
说得比较通俗一点,,就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout 值为8个小时,可以通过命令show variables like 'wait_timeout'查看结果值;。
设置这个值是非常有意义的,比如你的网站有大量的MySQL链接请求(每个MySQL连接都是要内存资源开销的 ),由于你的程序的原因有大量的连接请求空闲啥事也不干,白白占用内存资源,或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态(可用show processlist),如果经常发现MYSQL中有大量的Sleep进程,则需要 修改wait-timeout值了。
interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
wait_timeout:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).
这两个参数必须配合使用。否则单独设置wait_timeout无效
4.1.3修改max_connections参数值,由默认的151,修改为3000(750M)。
max_connections=3000
max_connections是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。
MySQL服务器允许的最大连接数16384;
查看系统当前最大连接数:
show variables like 'max_connections';
4.1..4修改max_user_connections值,由默认的0,修改为800
max_user_connections=800
max_user_connections是指每个数据库用户的最大连接
针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
目前默认值为:0不受限制。
这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同一时刻并行连接数的最大值。它不是指当前的连接情况,而是一个比较值。如果在过去某一个时刻,MYSQL服务同时有1000个请求连接过来,而之后再也没有出现这么大的并发请求时,则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。
查看max_user_connections值
show variables like 'max_user_connections';
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
--------------------------------------分割线 --------------------------------------
更多详情见请继续阅读下一页的精彩内容:

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to optimize MySQL connection speed? Overview: MySQL is a widely used relational database management system that is commonly used for data storage and management in a variety of applications. During development, optimization of MySQL connection speed is critical to improving application performance. This article will introduce some common methods and techniques for optimizing MySQL connection speed. Table of Contents: Use connection pools to adjust connection parameters and optimize network settings. Use indexing and caching to avoid long idle connections. Configure appropriate hardware resources. Summary: Use connection pools.

In the current Internet era, the importance of data is self-evident. As one of the core components of Internet applications, database backup and recovery work is particularly important. However, as the amount of data continues to increase and business requirements become increasingly complex, traditional database backup and recovery solutions can no longer meet the high availability and high performance requirements of modern applications. Therefore, optimizing the backup and recovery performance of MySQL database has become an urgent problem that needs to be solved. In practice, we have adopted a series of project experiences to effectively improve MySQL data

With the rapid development of the Internet, MySQL database has become the core data storage technology for many websites, applications and even enterprises. However, with the continuous growth of data volume and the sharp increase in concurrent access, MySQL's performance problems have become increasingly prominent. PHP's PDO class is also widely used in the development and operation of MySQL because of its efficient and stable performance. In this article, we will introduce how to use the PDO class to optimize MySQL performance and improve the database's response speed and concurrent access capabilities. 1. Introduction to PDO class

In modern applications, the MySQL database is a common choice. However, as data volumes grow and business needs continue to change, MySQL performance may suffer. In order to maintain the high performance of MySQL database, dynamic SQL statements have become an important technical means to improve the performance of MySQL. What is a dynamic SQL statement? A dynamic SQL statement refers to the technology of generating SQL statements by a program in an application. In layman's terms, it means treating SQL statements as strings. For large applications,

MySQL Performance Optimization Practical Guide: In-depth Understanding of B+ Tree Indexes Introduction: As an open source relational database management system, MySQL is widely used in various fields. However, as the amount of data continues to increase and query requirements become more complex, MySQL's performance problems are becoming more and more prominent. Among them, the design and use of indexes are one of the key factors affecting MySQL performance. This article will introduce the principle of B+ tree index and show how to optimize the performance of MySQL with actual code examples. 1. Principle of B+ tree index B+ tree is a

MySQL performance optimization: Master the characteristics and advantages of the TokuDB engine Introduction: In large-scale data processing applications, performance optimization of the MySQL database is a crucial task. MySQL provides a variety of engines, each with different features and advantages. This article will introduce the features and advantages of the TokuDB engine and provide some code examples to help readers better understand and apply the TokuDB engine. 1. Characteristics of TokuDB engine TokuDB is a high-performance, high-compression rate storage engine.

With the rapid development of the Internet, the scale of data continues to expand, and the demand for database storage and query efficiency is also increasing. As the most commonly used open source database, MySQL's performance optimization has always been the focus of developers. This article will introduce an effective MySQL performance optimization technology - vertical partition table, and explain in detail how to implement and apply it. 1. What is a vertical partition table? Vertically partitioned tables refer to dividing a table according to column characteristics and storing different columns on different physical storage devices to improve query efficiency.

As a lightweight relational database management system, MySQL database is widely used in Internet applications and enterprise-level systems. In enterprise-level applications, as the amount of data increases, the size of data tables also continues to increase. Therefore, effective management of data table sizes is crucial to ensuring the performance and reliability of the database. This article will introduce data table size management techniques in MySQL. 1. Data table division As the amount of data continues to increase, the size of the data table also continues to increase, which will cause database performance to decrease and query operations to slow down.
