MySQL数据库配置参数优化及参数对应说明

MySQL数据库配置参数优化及参数对应说明

* 来源: * 作者: * 发表时间: 2019-12-03 1:13:50 * 浏览: 0
对于新手来说,MySQL参数优化相对困难。实际上,此参数优化是一件非常复杂的事情。对于不同的网站,它们的在线访问量,访问量,帖子,网络状况和机器硬件配置都是如此。与之相关的是,一次不能完成优化,需要不断地进行观察和调试才能获得最佳结果。 MySQL数据库配置参数优化及参数对应说明。首先讨论服务器的硬件和论坛。 CPU:2个四核IntelXeon2.00GHz。内存:4GBDDR。硬盘:SCSI146GB。论坛:在线成员通常大约有5000人,最高记录是13264。下面,我们根据上述硬件配置进行合并。对my.cnf进行分析后,对其进行了优化,结果表明某些参数可能必须根据更改进行重新调整。在论坛和程序员的程序中。 Mysql [mysqld]端口= 3306 serverid = 1套接字= / tmp / mysql.sock跳过锁定避免了MySQL的外部锁定,减少了出错的机会,并增强了稳定性。 skip-name-resolve禁用MySQL对外部连接执行DNS解析。使用此选项可以消除MySQL的DNS解析时间。但是,应注意,如果启用此选项,则所有远程主机连接授权都必须使用IP地址方法,否则MySQL将无法正常处理连接请求! Back_log = 500需要MySQL可以拥有的连接数。当主MySQL线程在短时间内收到大量连接请求,然后主线程花费一些时间(尽管很短)来检查连接并启动新线程时,此方法起作用。 back_log值指示在MySQL暂时停止回答新请求之前的短时间内可以在堆栈上存储多少个请求。仅当您期望在短时间内有许多连接时,才需要增加它,换句话说,此值是传入TCP / IP连接的侦听队列的大小。您的操作系统对此队列的大小有自己的限制。尝试将back_log设置为超出操作系统限制将不起作用。当您观察主机进程列表并找到大量264084时|未经身份验证的用户| xxx.xxx.xxx.xxx | NULL |连接| NULL |登录| NULL,则需要增加back_log的值。默认值为50,我将其更改为500。Key_buffer_size = 384M key_buffer_size指定用于索引的缓冲区的大小。增加它可以获得更好的索引(适用于所有读取和多次重写)。如果将其设置得太大,系统将开始页面更改,并且实际上会变慢。对于具有大约4GB内存的服务器,此参数可以设置为384M或512M。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。 key_reads / key_read_requests之比应尽可能低,至少应为1:100、1:1000(以上状态值可以使用SHOWSTATUSLIKElsquo,key_read%rsquo,来获得)。注意:如果此参数的值设置太大,则服务器的整体效率会降低! Max_allowed_pa​​cket = 32M可以安全地增加此变量的值,因为仅在需要时才分配额外的内存。例如,只有在发出长查询或mysqld必须返回较大的结果行时,mysqld才会分配更多的内存。此变量的较小默认值是一种预防措施,可捕获客户端和服务器之间的错误数据包,并确保不会因意外使用大数据包而导致内存溢出。 Table_cache = 512 table_cache指定表缓存的大小。每当MySQL访问表时,如果表缓冲区中仍然有空间,则会打开表并将其放置在表缓冲区中,这样可以更快地访问表内容。通过在高峰时间检查状态值Open_tables和Opened_tables,您可以决定是否需要增加table_cache的值。如果发现open_tables等于table_cache并且open_tables正在增长,则需要增加table_cache的值(以上状态值可以使用SHOWSTATUSLIKElsquo,Open%tablersquo,获得)。请注意,您不能盲目地将table_cache设置为较大的值。如果设置得太高,可能会导致文件描述符不足,从而导致unst性能或连接失败。 sort_buffer_size = 4M:可用于查询排序的缓冲区大小。注意:与该参数对应的已分配内存在每个连接中都是独占的!如果有100个连接,则实际分配的总排序缓冲区大小为100倍,即4 = 400MB。因此,对于具有4GB内存的服务器,建议设置为4-8M。 Read_buffer_size = 4M可用于读取查询操作的缓冲区的大小。与sort_buffer_size一样,与该参数对应的已分配内存对于每个连接都是互斥的! Join_buffer_size = 8M可用于联合查询操作的缓冲区大小。像sort_buffer_size一样,与该参数对应的已分配内存对于每个连接也是独占的! Myisam_sort_buffer_size = 64M当表更改时,重新排序所需的MyISAM缓冲区query_cache_size = 64M指定MySQL查询缓冲区的大小。您可以通过在MySQL控制台中执行以下命令来观察:gt,SHOWVARIABLESLIKE'%query_cache%',gt,SHOWSTATUSLIKE'Qcache%。如果Qcache_lowmem_prunes的值很大,则表明通常没有足够的缓冲。如果Qcache_hits的值很大,则表明查询缓冲区的使用频率很高。如果该值很小但会影响效率,则可以考虑不使用查询缓冲区Qcache_free_blocks。如果该值非常大,则表明缓冲区已碎片化。 (Thread_cache_size = 64)可以重用并存储在其中的线程数。如果存在,则从缓存中获取新线程。如果断开连接时有空间,则将客户端的线路放置在缓存中。如果有许多新线程,则可以使用此变量值来提高性能。通过比较Connections和Threads_created状态的变量,可以看到此变量的作用(tmp_table_size = 256M)max_connections =1000。指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现TooManyConnections错误消息,则需要增加此参数的值。 Max_connect_errors = 10000000对于同一主机,如果中断错误连接数超过此参数的值,则将禁止该主机进行连接。要取消禁用主机,请执行:FLUSHHOST。 Wait_timeout = 10指定请求的最大连接时间,对于具有大约4GB内存的服务器,可以将其设置为5-10。 thread_concurrency = 8该参数的值是服务器的逻辑CPU数2。在此示例中,服务器有2个物理CPU,每个物理CPU支持HT超线程,因此实际值为4倍,2 = 8 skip -networking启用此选项可完全禁用MySQL的TCP / IP连接方法。如果WEB服务器通过远程连接访问MySQL数据库服务器,则不要启用该选项!否则,将无法正常连接! long_query_time = 10日志慢查询=日志查询未使用索引。打开慢查询日志。慢查询日志对于跟踪有问题的查询很有用。它记录所有已检查long_query_time的查询,并在必要时记录不使用索引的记录。以下是慢速查询日志的示例:要启用慢速查询日志,您需要设置参数log_slow_queries,long_query_times,log-queries-not-using-indexes。 Log_slow_queries指定日志文件。如果不提供文件名,MySQL将生成一个默认文件名。 long_query_times指定慢速查询的阈值。默认值为10秒。 log-queries-not-using-indexes是在4.1.0之后引入的参数,它指示记录不使用索引进行查询。设置long_query_time =10。此外,使用showstatus命令查看mysql status的值及其含义:showstatus命令的使用具有以下含义:aborted_clients非法客户端连接中断的数目aborted_connects失败的mysql连接com_xxxxxx的数目在执行命令的次数上,有许多连接可以连接到mysql Created_tmp_disk_tables在磁盘上创建临时表Created_tmp_tables在内存中创建临时表Created_tmp_files临时文件数Key_read_requests Thenumberofrequeststoreadakeyblock fromcache Key_readsT​​henumberofphysicalcalsosofakeyblockfromdisk_Max_used_connections这个值。应该增加。 Uptime服务器为提高性能建议而工作的秒数:1.如果opened_tables太大,则应更改my.cnf table_cache会变大。2.如果Key_reads太大,则应增加my中的key_buffer_size。 cnf。您可以使用Key_reads / Key_read_requests来计算缓存失败率3。如果Handler_read_rnd太大,则您编写的SQL语句中的许多查询将扫描整个表而不充当索引键4的作用。如果Threads_created太大,增加my.cnf中thread_cache_size的值。您可以使用Threads_created / Connections来计算高速缓存命中率5。如果Created_tmp_disk_tables太大,请使用基于内存的临时表(而不是基于磁盘的临时表)增加my.cnf中tmp_table_size的值。