<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Caution: Don’t overwrite your entire my.cnf at once when tuning MySQL.

Caution: Don’t overwrite your entire my.cnf at once when tuning MySQL. One or two changes per restart, benchmark, then continue. Take backups first and be careful!

It took me some time to decide the title for this article. MariaDB has been fast replacing MySQL as a growing number of Linux distributions now default to MariaDB over MySQL. In addition, DBA’s often manually replace MySQL with MariaDB.

MariaDB is an enhanced drop-in replacement for MySQL. Therein lies my largely self-made conundrum, MySQL or MariaDB? Last month I posted a poll on the Linux Google+ communityWhich would you suggest… MySQL or MariaDB? And why?

MySQL or MariaDB and why?

The MySQL tuning advice below applies to both MySQL and MariaDB (and Percona) . After tuning MySQL over the years I can safely say that the following pitfall is one of the most common…

Tuning MySQL (my.cnf) – Avoid arbitrarily increasing per connection buffers

The my.cnf config file is well-known but also often grossly misconfigured. I’m not sure where or when it all started but it has become the norm to keep increasing the size and value of almost every setting in my.cnf without much reasoning behind those increases. Lets look at some important my.cnf parameters, where doing this, will not only hurt performance but also waste large chunks your server’s memory and as a result reduce MySQL’s overall capacity and throughput.

Related:   PHP performance: oPcache Control Panels

Not all buffers in my.cnf are global settings

Buffers such as join_buffer_sizesort_buffer_sizeread_buffer_size and read_rnd_buffer_size are allocated per connection. Therefore a setting of read_buffer_size=1M and max_connections=150 is asking MySQL to allocate – from startup – 1MB per connection x 150 connections. For more than a decade the default remains at 128K. Increasing the default is not only a waste of server memory, but often does not help performance. In nearly all cases its best to use the defaults by removing or commenting out these four buffer config lines. For a more gradual approach, reduce them in half to free up wasted RAM, keep reducing them towards default values over time. I’ve actually seen improved throughput by reducing these buffers. But there’s really no performance gains from increasing these buffers except in cases of very high traffic and/or other special circumstances. Avoid arbitrarily increasing these!

Tuning MySQL join_buffer_size

The join_buffer_size is allocated for each full join between two tables. From MySQL’s documentation the join_buffer_size is described as: “The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.” It goes on to say: “Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.” The join buffer is allocated to cache table rows when the join can’t use an index. If your database(s) suffer from many joins performed without indexes it cannot be solved by just increasing join_buffer_size. The problem is “joins performed without indexes” and thus the solution for faster joins is to add indexes.

Related:   Download LEMP for Raspberry Pi: Nginx + MariaDB (MySQL) + PHP

Tuning MySQL sort_buffer_size

Unless you have data to show otherwise, you should avoid arbitrarily increasing the sort_buffer_size as well. Memory here is also assigned per connection! MySQL’s documentation warns: “On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.” Don’t increase above 2M since there is a performance penalty going higher which sometimes can eliminate any benefits.

Rule of thumb when tuning MySQL, if you can’t provide a valid reason for increasing any of these buffers, keep them set to defaults (comment-out of config). These four often attract large increases from admins when tuning MySQL. If we think about the meaning of the word “buffer” we can easily see why these buffers are not the my.cnf settings which require such heavy attention.

Hopefully you’ve found the MySQL tuning info useful. Also see another pitfall: MySQL Query Cache Size and Performance. (Not mentioned in that article, but if you are using mostly InnoDB tables, then you may be better off just disabling query_cache completely). If using MariaDB you should also use Aria table types over MyISAM and Innodb in some cases. Aria tables perform really well!

目录
打赏
0
0
0
0
11
分享
相关文章
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont
 Connection reset by peer的常见原因: 1)服务器的并发连接数超过了其承载量,服务器会将其中一些连接关闭;    如果知道实际连接服务器的并发客户数没有超过服务器的承载量,看下有没有网络流量异常。
886 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont
NoSuchObjectException(message:There is no database named cloudera_manager_metastore_canary_test_db_hive_hivemetastore_df61080e04cd7eb36c4336f71b5a8bc4) at org.
1105 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont
在统计分析系统中, 维度:指人们分析事物的角度。比如,分析活跃用户,可以从时间的维度,也可以从地域的维度去看,也可以时间、地域两个维度组合去分析。
685 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont
如何建立一个“铁打的营盘”? 中国有句古话,叫做铁打的营盘流水的兵。   我相信,创业初期,当团队里有人离开的时候,肯定有不少创业者拿这句话来安慰自己。
839 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html><head><meta http-equiv="Cont
Spark Streaming 的一些问题,做选型前关注这些问题可以有效的降低使用风险。 checkpoint checkpoint 是个很好的恢复机制。
954 0