面包店网站建设规划书,宁波网站建设详细策划,苏州网站建设功能,石家庄做网站百度推广1 优化连接池 连接池运行机制 MySQL连接器中的连接池#xff0c;用以提高数据库密集型应用程序的性能和可扩展性#xff0c;默认启用。MySQL连接器负责管理连接池中的多个连接#xff0c;自动创建、打开、关闭和破坏连接#xff0c;多个连接的创建#xff0c;可满足多客户…1 优化连接池 连接池运行机制 MySQL连接器中的连接池用以提高数据库密集型应用程序的性能和可扩展性默认启用。MySQL连接器负责管理连接池中的多个连接自动创建、打开、关闭和破坏连接多个连接的创建可满足多客户端的频繁连接连接的重复使用获得最佳性能。 MySQL连接器 每三分钟运行一次后台作业并从池中删除闲置未使用超过三分钟的连接。池清理释放客户端和服务器端的资源。这是因为在客户端每个连接都使用一个Socket而在服务器端每个连接都使用一个Socket和一个线程。 max_connectionsMySQL最大并发连接数默认值是151最大连接数上限是16384 经验实际连接数是最大连接数的 85% 较为合适。 设置 max_used_connections 方法 查询数据库目前设置的最大并发连接数是多少
SHOW VARIABLES LIKE ‘max_connections’;查询数据库目前实际连接的并发数是多少 SHOW STATUS LIKE ‘max_used_connections’; 在MySQL配置文件 /etc/my.cnf 中设置 max_connections3000表示修改最大连接数为3000。 注意需要重启 MySQL 才能生效。 – MySQL为每个连接创建缓冲区所以不应该盲目上调最大连接数。 如果最大连接数达到了上面设置的 3000会消耗大约 800M 内存。
其他连接池设置 开启连接池 Poolingtrue默认开启 复用时重置连接状态 ConnectionResetTrue 保持连接设置 CacheServerPropertiesTrue 连接超时回收(秒) ConnectionLifeTime300 支持的最大连接数量 Max Pool Size100 保持最小的连接数量 Min Pool Size10
2. 优化请求堆栈
back_log存放执行请求的堆栈大小默认值是50。
该值设置为最大并发连接数的 20%30% 较为合适。 设置 back_log 方法在MySQL配置文件 /etc/my.cnf 中设置 back_log600修改后需要重启 MySQL 才能生效。 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的整数。 修改系统内核参数可以编辑/etc/sysctl.conf去调整它。 如net.ipv4.tcp_max_syn_backlog 2048改完后执行sysctl -p 让修改立即生效。 查看mysql 当前系统默认back_log值命令 show variables like ‘back_log’; 3. 修改连接超时时间
wait-timeout超时时间单位是秒连接默认超时为8小时连接长期不用不销毁比较浪费资源。 查看
SHOW VARIABLES LIKE wait_timeout%;经验设置超时时间为 10 分钟 wait-timeout600 4. 优化内存缓冲池
缓冲池运行机制
在MySQL5.5之前广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存所以非常需要有足够的InnoDB buffer pool空间。缓冲区分为 热数据区 / 冷数据区两者空间占比约为 7/3每区中的数据集依使用频率按顺序依次排列。当一个新的查询结果出现后首先考虑存放到冷数据区当冷数据区的结果集使用达到一定频率会被改存到热数据区使用频率最好的数据集会被存放到热区的首位当然也有热区转到冷区的状况。
InnoDB 缓冲池不仅仅是一个缓存MySQL InnoDB buffer pool 包含四部分 1. 数据缓存InnoDB 数据页面 2. 索引缓存索引数据 3. 缓冲数据脏页在内存中修改尚未写入到磁盘的数据 4. 内部结构如自适应哈希索引行锁等。
innodb_buffer_pool_instances 内存缓冲池。 buffer_pool 把需要缓冲的数据 hash 到不同的缓冲池中这样可以并行的内存读写。通过减少争用不同线程对缓存页面进行读写的争用将缓冲池划分为多个单独的实例可以提高并发性。 MySQL 5.7、MySQL 8.0 下 innodb_buffer_pool_instances 默认为 1若 MySQL 存在高并发和高负载访问设置为 1 则会造成大量线程对 buffer_pool 的单实例互斥锁竞争这样会消耗一定量的性能的。 innodb_buffer_pool_instances 建议设置为 cpu核心数。 innodb_buffer_pool_chunk_size缓冲池每块大小默认128M。 pool_chunk_size 一般不做改动使用默认值就可以。 innodb_buffer_pool_size缓冲池的承载总量。 innodb_buffer_pool_size 可以缓存索引和行数据值越大、IO读写就越少
设置规则innodb_buffer_pool_size innodb_buffer_pool_chunk_size * {N}块 *innodb_buffer_pool_instances
如果单纯的做数据库服务该参数可以设置到电脑物理内存的80% 为了更好的配合 pool_instancepool_size 需要设置为 pool_instance 和 pool_chunk_size 的整数倍这样可以被 pool_instance 整除为每个 buffer pool 实例平均分配内存。如果设置的值不是倍数MySQL会自动将 pool_size 调整为 pool_chunk_size 的倍数。
5. 优化并发线程数
innodb_thread_concurrency代表并发线程数。 默认是0表示没有设置线程数量的上限。不是分配给 MySQL 的线程越多越好线程多反而会损耗cpu性能导致速度变慢。 经验并发线程数应该设置为 cpu 核心数的两倍。 注意这个变量特定于Solaris 8和更早的系统MySQL 5.7.2中删除了这个变量。 设置 innodb_thread_concurrency 方法
在MySQL配置文件 /etc/my.cnf 中设置 innodb_thread_concurrency8。
– 查看cpu型号
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
– 查看cpu核心数
cat /proc/cpuinfo | grep “cores”|uniq6. 优化线程池
客户端发起连接到 MySQL Server 后MySQL Server监听进程监听到新的请求然后 Sever 会为其分配一个新的 thread去处理此请求。 从建立连接之开始CPU要给它划分一定的 thread stack然后进行用户身份认证建立上下文信息最后请求完成关闭连接同时释放资源。 在高并发的情况下这个过程将给系统带来巨大的压力不能保证性能。MySQL服务器的线程数需要在一个合理的范围之内这样才能保证MySQL服务器健康平稳地运行。
6.1 查看线程池的状态
mysql show variables like ‘thread%’;
±-------------------±--------------------------
| Variable_name | Value |
±-------------------±--------------------------
| thread_cache_size | 64 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
±-------------------±--------------------------
thread_cache_size
thread_cache_sizeThreads_cached 中存放的最大连接线程数。
在短连接的应用中Threads_cached 的功效非常明显因为在应用中数据库的连接和创建是非常频繁的。如果不使用 Threads_cached那么消耗的资源是非常频繁的。在长连接中虽然带来的改善没有短连接的那么明显但是好处是显而易见的。但并不是越大越好大了反而浪费资源这个的确定一般认为和物理内存有一定关系。Mysql默认值为9。 设置 thread_cache_size 方法参考下面额对照表根据物理内存设置对应的 thread_cache_size 数值 1G — 8 2G — 16 3G — 32 3G — 64 6.2 在 mysql 命令行中设置
mysql set global thread_cache_size64thread_concurrency
thread_concurrency 应设为 CPU核数的2倍。 比如有一个双核的CPU那么thread_concurrency的应该为4。这个变量是针对Solaris系统的如果设置这个变量的话mysqld就会调用thr_setconcurrency()。这个函数使应用程序给同一时间运行的线程系统提供期望的线程数目。但是在5.7以后就已经抛弃了。 设置 thread_concurrency 方法 – 在 mysql 命令行中设置
mysql set global thread_concurrency4thread_handling
运用 Thread_Cache 处理连接的方式从 5.1.19 添加的新特性有两个值可选 no-threads、one-thread-per-connection。
no-threads 服务器使用一个线程one-thread-per-connection 服务器为每个客户端请求使用一个线程
thread_stack
每个连接被创建的时候mysql分配给它的内存。这个值一般认为默认就可以应用于大部分场景了除非必要非则不要动它。上面表示是256kb。
6.2 查看线程使用情况
mysql show global status like ‘Thread%’;
±------------------±------
| Variable_name | Value |
±------------------±------
| Threads_cached | 41 |
| Threads_connected | 53 |
| Threads_created | 541 |
| Threads_running | 4 |
±------------------±------Threads_cached
MySQL里面为了提高客户端请求创建连接过程的性能提供了一个连接池也就是 Thread_cache 池大小是thread_cache_size将空闲的连接线程放在连接池中而不是立即销毁。 这样的好处就是当又有一个新的请求的时候mysql不会立即去创建连接 线程而是先去 Thread_Cache 中去查找空闲的连接线程如果存在则直接使用不存在才创建新的连接线程。Thread_cache 值表示已经被线程缓存池缓存的线程个数。
Threads_connected
当前处于连接状态的线程个数等于 show processlist。
Threads_created
Threads_created 表示创建过的线程数如果发现 Threads_created 值过大的话表明MySQL服务器一直在创建线程这也是比较耗资源可以适当增加配置文件中 thread_cache_size 值。
Threads_running
处于激活状态的线程的个数这个一般都是远小于Threads_connected的。
7.优化日志
日志运行机制 MySQL在运行时会有各种不同日志的记录大量的各种类型的日志产生会对资源的开销产生严重的影响必要的时候我们选择性的开启。 但在生产环境时有些日志并不是必须以下列出MySQL各种日志信息
错误日志启动、关闭、运行时 产生的异常记录建议开启设置 log_error 查询日志客户端连接和执行的脚本建议关闭设置 general_log慢查询日志记录超时的查询记录不适用索引的查询等建议关闭设置 slow_query_log二进制日志用于数据同步复制需发送的数据日志多用于集群如需开启设置 log_bin中继日志用于数据同步复制时接收到的数据日志多用于集群如需开启设置 relay_log
8. 锁优化
8.1. innodb 锁优化
Innodb 存储引擎由于实现了行级锁定虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些但是在整体并发处理能力方面要远远优于MyISAM 的表级锁定的。
尽可能让所有的数据检索都通过索引来完成从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定 合理设计索引让Innodb 在索引键上面加锁的时候尽可能准确尽可能的缩小锁定范围避免造成不必要的锁定而影响其他Query 的执行 尽可能减少基于范围的数据检索过滤条件避免因为间隙锁带来的负面影响而锁定了不该锁定的记录 尽量控制事务的大小减少锁定的资源量和锁定时间长度 在业务环境允许的情况下尽量使用较低级别的事务隔离以减少MySQL 因为实现事务隔离级别所带来的附加成本 减少 innodb 死锁产生概率的建议 类似业务模块中尽可能按照相同的访问顺序来访问防止产生死锁 在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率 对于非常容易产生死锁的业务部分可以尝试使用升级锁定颗粒度通过表级锁定来减少死锁产生的概率
9.2. MyISAM 锁优化
在MyISAM里读写操作是串行的但当对同一个表进行查询和插入操作时为了降低锁竞争的频率根据concurrent_insert的设置MyISAM是可以并行处理查询和插入的
缩短锁定时间 尽两减少大的复杂Query将复杂Query 分拆成几个小的Query 分布进行 尽可能的建立足够高效的索引让数据检索更迅速 尽量让MyISAM 存储引擎的表只存放必要的信息控制字段类型 利用合适的机会优化MyISAM 表数据文件 max_write_lock_count 缺省情况下写操作的优先级要高于读操作的优先级即便是先发送的读请求后发送的写请求此时也会优先处理写请求然后再处理读请求。这就造成一 个问题一旦我发出若干个写请求就会堵塞所有的读请求直到写请求全都处理完才有机会处理读请求。此时可以考虑使用 max_write_lock_count max_write_lock_count1 有了这样的设置当系统处理一个写操作后就会暂停写操作给读操作执行的机会。 low-priority-updates 我们还可以更干脆点直接降低写操作的优先级给读操作更高的优先级。 low-priority-updates1 综合来看concurrent_insert2是绝对推荐的至于max_write_lock_count1和low-priority- updates1则视情况而定如果可以降低写操作的优先级则使用low-priority-updates1否则使用 max_write_lock_count1。 set-variable max_allowed_packet1M set-variable net_buffer_length2K
