`

Mysql数据库的优化(参数优化)

 
阅读更多

MySQL运行参数,修改/etc/my.cnf文件调整mysql运行参数重启MySQL后生效,在MySQL4版本以后,一部分内部变量可以在MySQL运行时设置,不过重启MySQL就失效了。
mysqld程序--目录和文件
basedir      = path          # 使用给定目录作为根目录(安装目录)。
datadir      = path          # 从给定目录读取数据库文件。
pid-file     = filename      # 为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统);

[mysqld]                       
socket = /tmp/mysql.sock     # 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)
port             = 3306      # 指定MsSQL侦听的端口
key_buffer       = 384M      # key_buffer是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写)。
                               索引块是缓冲的并且被所有的线程共享,key_buffer的大小视内存大小而定。
table_cache      = 512       # 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销
sort_buffer_size = 2M        # 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
                               注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100×6=600MB

read_buffer_size = 2M        # 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
query_cache_size = 32M       # 指定MySQL查询结果缓冲区的大小
read_rnd_buffer_size    = 8M # 改参数在使用行指针排序之后,随机读用的。
myisam_sort_buffer_size =64M # MyISAM表发生变化时重新排序所需的缓冲
thread_concurrency      = 8  # 最大并发线程数,取值为服务器逻辑CPU数量×2,如果CPU支持H.T超线程,再×2
thread_cache            = 8  # #缓存可重用的线程数
skip-locking                 # 避免MySQL的外部锁定,减少出错几率增强稳定性。
[mysqldump]
max_allowed_packet      =16M # 服务器和客户端之间最大能发送的可能信息包

[myisamchk]
key_buffer   = 256M
sort_buffer  = 256M
read_buffer  = 2M
write_buffer = 2M

其他可选参数:
back_log = 384
    指定MySQL可能的连接数量。
    当MySQL主线程在很短时间内接收到非常多的连接请求,该参数生效,主线程花费很短时间检查连接并且启动一个新线程。
    back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。
    如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。
    试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
max_connections = n
    MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。超过限制后会报 Too many connections 错误
key_buffer_size = n
    用来存放索引区块的RMA值(默认设置是8M),增加它可得到更好处理的索引(对所有读和多重写)
record_buffer: 

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。
    如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128K)
wait_timeout:
    服务器在关闭它之前在一个连接上等待行动的秒数。
interactive_timeout:
    服务器在关闭它前在一个交互连接上等待行动的秒数。
    一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。
    默认数值是28800,可以把它改为3600。
skip-name-resolve           
    禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。
    但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
log-slow-queries = slow.log 
    记录慢查询,然后对慢查询一一优化
skip-innodb
skip-bdb
    关闭不需要的表类型,如果你需要,就不要加上这个
         
         
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;
如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;
如果Qcache_free_blocks的值非常大,则表明缓冲区中碎片很多。


##########################################
######      max_allowed_packet      ######
##########################################
通信信息包是发送至MySQL服务器的单个SQL语句,或发送至客户端的单一行。
在MySQL 5.1服务器和客户端之间最大能发送的可能信息包为1GB。
当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时,将发出“信息包过大”错误,并关闭连接。对于某些客户端,如果通信信息包过大,在执行查询期间,了能回遇到“丢失与MySQL服务器的连接”错误。 

客户端和服务器均有自己的max_allowed_packet变量,因此,如你打算处理大的信息包,必须增加客户端和服务器上的该变量。
如果你正在使用mysql客户端程序,其max_allowed_packet变量的默认值为16MB。要想设置较大的值,可用下述方式启动mysql:
mysql> mysql --max_allowed_packet=32M
它将信息包的大小设置为32MB。
服务器的默认max_allowed_packet值为1MB。如果服务器需要处理大的查询,可增加该值(例如,如果准备处理大的BLOB列)。例如,要想将该设置为16MB,可采用下述方式启动服务器:
mysql> mysqld --max_allowed_packet=16M

也能使用选项文件来设置max_allowed_packet。要想将服务器的该变量设置为16MB,可在选项文件中增加下行内容:
[mysqld]
max_allowed_packet=16M
增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存。例如,仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施,以捕获客户端和服务器之间的错误信息包,并确保不会因偶然使用大的信息包而导致内存溢出。
如果你正是用大的BLOB值,而且未为mysqld授予为处理查询而访问足够内存的权限,也会遇到与大信息包有关的奇怪问题。如果怀疑出现了该情况,请尝试在mysqld_safe脚本开始增加ulimit -d 256000,并重启mysqld。
##########################################
#####   MySQL怎样打开和关闭数据库表  #####
##########################################
table_cache, max_connections和max_tmp_tables影响服务器保持打开的文件的最大数量。如果你增加这些值的一个或两个,你可以遇到你的操作系统每个进程打开文件描述符的数量上强加的限制。然而,你可以能在许多系统上增加该限制。请教你的OS文档找出如何做这些,因为改变限制的方法各系统有很大的不同。
table_cache与max_connections有关。例如,对于200个打开的连接,你应该让一张表的缓冲至少有200 * n,这里n是一个联结(join)中表的最大数量。
打开表的缓存可以增加到一个table_cache的最大值(缺省为64;这可以用mysqld的-O table_cache=#选项来改变)。一个表绝对不被关闭,除非当缓存满了并且另外一个线程试图打开一个表时或如果你使用mysqladmin refresh或mysqladmin flush-tables。
当表缓存满时,服务器使用下列过程找到一个缓存入口来使用:

不是当前使用的表被释放,以最近最少使用(LRU)顺序。
如果缓存满了并且没有表可以释放,但是一个新表需要打开,缓存必须临时被扩大。
如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态,它被关闭并从缓存中释放。
对每个并发存取打开一个表。这意味着,如果你让2个线程存取同一个表或在同一个查询中存取表两次(用AS),表需要被打开两次。任何表的第一次打开占2个文件描述符;表的每一次额外使用仅占一个文件描述符。对于第一次打开的额外描述符用于索引文件;这个描述符在所有线程之间共享

 

经典配置
主机 
[mysqld]
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci


datadir=/db/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_data_home_dir=/db
default-table-type=INNODB
#Replication
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=oxdb
server-id=1
#OS: tuning
table_cache = 1024
key_buffer = 256M
record_buffer = 16M
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=500
sort_buffer = 128M
innodb_log_file_size = 50M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 1300MB
innodb_thread_concurrency=50
innodb_flush_method=O_DSYNC
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0



max_allowed_packet=32M
tmp_table_size=96M
thread_concurrency=10
thread_cache_size=500

#result set and query cache
query_cache_type = 0
query_cache_size = 0

long_query_time = 1
log_slow_queries = /var/log/mysql/slow-queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从机
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
innodb_data_home_dir=/pp
default-table-type=InnoDB

###REPLICATION PROPERTIES####
server-id=2
master-host=10.0.0.1
master-user=replication
master-password=replication
master-connect-retry=60
replicate-do-table=ox.name
replicate-do-table=ox.User
#replicate-do-db=oxb
#replicate-ignore-db=oxb
#replicate-ignore-db="oxb"
skip-slave-start
slave-skip-errors=1062

log-bin = /var/log/mysqld/bin.log
log-bin-index = /var/log/mysqld/log-bin.index
log-error = /var/log/mysqld/error.log

relay-log = /var/log/mysqld/relay.log
relay-log-info-file = /var/log/mysqld/relay-log.info
relay-log-index = /var/log/mysqld/relay-log.index

###OS TUNNING####
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci
default-table-type=INNODB
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=50
sort_buffer = 28M
group_concat_max_len=65536
innodb_buffer_pool_size=200MB
innodb_additional_mem_pool_size=20M
innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_thread_concurrency=500
innodb_flush_method=O_DSYNC
table_cache=800
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0
max_allowed_packet=32M
tmp_table_size=96M
thread_concurrency=10
thread_cache_size=500

long_query_time = 1
log_slow_queries = /var/log/mysqld/slow-queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

普通$ cat /etc/my.cnf
[mysqld]

default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci

wait_timeout=6000
connect_timeout=25
interactive_timeout=6000

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_allowed_packet=32M
max_connections=200
key_buffer              = 16M
thread_stack            = 256k
thread_cache_size       = 500
#thread_stack            = 128K
#thread_cache_size       = 8
sort_buffer = 128M
innodb_buffer_pool_size=300MB

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

数据库有7G数据,随着数据的增大,现在每天出现一次崩溃。
mysqladmin processlist 查询进程,发现locked进程超多,而且一直locked不会解锁,到最后整个mysql被locked进程占满,崩溃。

是否和my.cnf里如下配置有关。

根据网上资料,做了如下配置,效果不是很明显:
MySQL调优性能的系统变量

(1)、back_log:
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

(2)、interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800,我把它改为7200。

(3)、key_buffer_size:
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8M),我的MySQL主机有2GB内存,所以我把它改为402649088(400MB)。

(4)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 Too many connections 错误。 默认数值是100,我把它改为1024 。

(5)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128K),我把它改为16773120 (16M)

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M),我把它改为 16777208 (16M)。

(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

(、thread_cache_size:
可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800,我把它改为7200。 

为了您的安全,请只打开来源可靠的网址
打开网站    取消
来自: http://hi.baidu.com/seoyingjie/blog/item/187955ad2a7753e81e17a2c3.html 
 
分享到:
评论

相关推荐

    MYSQL数据库优化秘籍

    MYSQL数据库优化秘籍,大牛出的,值得你反复研读 MySQL在Linux环境下的安装 文件引擎MyISAM与InnoDB比较 LOAD DATA INFILE/mysqldump DBA的分析命令 MySQL的系统配置参数、诊断操作系统的状态 MySQL的分库分表,分区...

    MySQL数据库优化实践

    MySQL数据库优化实践主要涵盖硬件配置、操作系统层面的调整以及文件系统的优化等方面,这些优化措施能够显著提高MySQL数据库的性能和响应速度。 首先,硬件优化是基础。开启BBWC(Battery Backed Write Cache)能...

    Mysql数据库性能优化

    - `my.cnf` 文件是MySQL数据库的配置文件,其中包含了服务器的启动参数和设置。例如,`innodb_buffer_pool_size` 设置决定了InnoDB存储引擎缓存的数据量,对性能有很大影响。 - 优化配置还包括调整`max_...

    2G内存的MYSQL数据库服务器优化

    在IT行业中,对于资源有限的环境进行数据库优化是一项挑战性工作,尤其是在仅有2GB内存的情况下对MySQL数据库服务器进行优化。这种优化旨在提高性能的同时确保系统的稳定运行。 #### 标题解读:2G内存的MySQL数据库...

    MySql数据库性能优化

    MySql数据库性能优化是指通过调整和优化数据库的各种参数、结构和查询语句,提高数据库的运行速度和效率,减少资源占用和系统瓶颈。下面将详细介绍MySql数据库性能优化的相关知识点。 什么是优化? 优化是指通过...

    基于MySQL数据库的查询性能优化研究.pdf

    1. 优化数据库连接池:根据实际情况合理配置数据库连接池的大小和参数,避免出现连接池溢出和等待的情况。 2. 适当使用索引:合理的索引设计可以大大提高查询速度,例如使用组合索引、避免使用 SELECT \* 等。 3. ...

    mySQL数据库性能优化pdf.pdf

    对于MySQL数据库来说,性能优化可以从多个层面来进行,包括但不限于硬件层面、系统参数配置、数据库结构设计、查询语句优化以及索引优化等。 一、硬件层面 在硬件层面,我们可以通过增加内存、使用更快的硬盘(如...

    深入浅出-MySQL数据库开发、优化与管理维护

    三、MySQL数据库优化 优化是提高数据库性能的关键,涉及查询优化、索引优化、服务器配置等多个方面。 1. 查询优化:通过改进SQL语句,避免全表扫描,使用JOIN优化,减少子查询,以及合理使用索引来提高查询效率。 ...

    Mysql数据库优化总结

    MySQL数据库优化是一个涉及多方面的复杂任务,主要包括硬件配置优化、数据库设计优化、软件优化和配置优化。以下是对这些方面详细的知识点说明: 1. **硬件配置优化**: - **CPU选择**:选择多核、高主频的CPU,以...

    MYSQL数据库技术分享.ppt

    MYSQL数据库参数优化是提高MYSQL数据库性能的重要手段。常用的MYSQL数据库参数优化方法包括: 1. 数据库参数优化:通过调整数据库参数,例如innodb_buffer_pool_size、innodb_additional_mem_pool_size、innodb_log...

    MYSQL数据库优化与表设计

    ### MySQL数据库优化与表设计详解 #### 一、数据库结构设计的重要性 在系统开发初期,一个合理且高效的数据库模型设计至关重要。它不仅能简化客户端和服务器端程序的开发及维护工作,还能显著提升系统运行时的性能...

    c# mysql数据库连接池实现

    本文将深入探讨如何在C#中使用MySQL数据库连接池。 首先,我们需要了解什么是数据库连接池。数据库连接池是一种资源管理技术,它预先创建并维护一定数量的数据库连接,当应用需要时,可以从池中获取连接,使用完毕...

    MySQL数据库相关课件PPT

    MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一,它以其高效、可靠和易于维护的特点,在Web开发、企业级应用和数据分析等领域广泛应用。本课件PPT涵盖了MySQL的基础知识到高级特性的深入理解,旨在帮助...

    Mysql数据库优化学习笔记

    MySQL数据库优化学习笔记 在数据库管理系统中,MySQL是一款广泛应用的关系型数据库,因其开源、免费、高效的特点,被广泛用于各种规模的项目。然而,随着数据量的增长和业务复杂度的提高,性能优化变得至关重要。本...

    mysql数据库优化方案

    MySQL数据库优化方案旨在提高数据库性能,特别是在处理大量数据时。以下是一些关键的优化策略: 1. **索引优化**:创建适当的索引是优化查询性能的关键。在频繁用于WHERE和ORDER BY子句的列上建立索引可以显著减少...

    秒杀应用的MySQL数据库优化 (转载)

    秒杀应用的MySQL数据库优化是一个重要的议题,尤其是在高并发、数据处理速度要求极高的场景下。这类应用常常面临巨大的压力,如瞬间涌入的大量请求、数据读写速度、以及资源的有效利用。本篇文章将深入探讨如何针对...

    国家开放大学 数据库运维 形考一 MySQL数据库服务器配置

    MySQL数据库服务器配置 MySQL数据库服务器配置是国家开放大学数据库运维 形考一的重要组成部分。通过这个实验,我们将掌握 MySQL 的安装方法,了解 MySQL 服务器的组成,掌握 MySQL 服务器的配置方法。 MySQL安装...

Global site tag (gtag.js) - Google Analytics