江苏城嘉建设工程有限公司网站,黑龙江建设工程网,什么情况自己建设网站,建设网站必须用dns目录
1、mysql查看数据大小
2、mysql配置优化
3、mysql压力测试
4、mysql主从复制原理
5、mysql主从延迟如何解决
6、主从切换操作 1、mysql查看数据大小 怎样查询总数据量 SELECT table_schema AS Database,CONCAT(ROUND(SUM((data_length index_length) / (1024 * 102…目录
1、mysql查看数据大小
2、mysql配置优化
3、mysql压力测试
4、mysql主从复制原理
5、mysql主从延迟如何解决
6、主从切换操作 1、mysql查看数据大小 怎样查询总数据量 SELECT table_schema AS Database,CONCAT(ROUND(SUM((data_length index_length) / (1024 * 1024)), 2), M) AS Total Size
FROM information_schema.TABLES
GROUP BY table_schema; 怎样查询某个库中表的数据量 SELECT table_schema AS Database,table_name AS Table,CONCAT(ROUND((DATA_LENGTH INDEX_LENGTH) / (1024 * 1024), 2), MB) AS Size
FROM information_schema.TABLES
WHERE table_schema your_database_name; 2、mysql配置优化 内存配置优化
innodb_buffer_pool_size将此参数设置为适当的值以确保InnoDB存储引擎能够缓存常用的数据和索引。建议分配给innodb_buffer_pool_size的内存大小为总内存的70-80%。
key_buffer_size对于使用MyISAM存储引擎的表将此参数设置为合适的值以便缓存索引。建议分配给key_buffer_size的内存大小为总内存的10-25%。
并发连接优化
max_connections根据数据库的负载情况和可用的系统资源适当调整最大连接数。确保设置的值能够满足并发连接需求但不会过度消耗系统资源。
查询缓存优化
query_cache_type将此参数设置为1以启用查询缓存。但请注意查询缓存在高并发环境下可能带来争用和性能问题因此在特定情况下可能需要禁用查询缓存。
query_cache_size根据查询缓存的使用情况和可用内存适当设置查询缓存的大小。建议将查询缓存大小限制在128MB至512MB之间。
日志优化
log_slow_queries启用慢查询日志以记录执行时间超过设定阈值的查询语句。根据应用的需要和性能要求设置适当的阈值。
log_queries_not_using_indexes启用此参数可以记录未使用索引的查询语句以帮助优化查询性能。
log_error配置错误日志路径用于记录MySQL的错误和警告信息。
临时表优化
tmp_table_size设置临时表的最大内存大小。如果常见的查询需要大量使用临时表可以适当增加此参数的值以减少磁盘临时表的使用。
max_heap_table_size设置基于内存的临时表的最大大小。如果使用了大型临时表可以适当调整此参数的值。
InnoDB存储引擎优化
innodb_file_per_table启用此选项可以使每个InnoDB表使用独立的表空间文件有助于优化磁盘空间的使用和维护。
innodb_flush_log_at_trx_commit根据应用的需要和数据安全性要求调整此参数以控制事务日志的刷新频率。
硬件和基础设施优化
使用更快的存储设备考虑使用SSD或NVMe等更快的存储设备来提高磁盘I/O性能。
分布式架构对于高负载和大规模应用可以考虑使用MySQL主从复制、MySQL集群或MySQL分片等分布式架构以提高可扩展性和性能。 3、mysql压力测试 MySQL提供了一个官方的基准测试工具称为MySQL Benchmark。 # 自动生成sql语句测试
mysqlslap -uroot -pwangxia123 --auto-generate-sql
# --only-print 来打印实际的测试过程不会实际执行
# 解析
Average number of seconds to run all queries运行所有语句的平均秒数
Minimum number of seconds to run all queries运行所有语句的最小秒数
Maximum number of seconds to run all queries运行所有语句的最大秒数
Number of clients running queries客户端数量
Average number of queries per client每个客户端运行查询的平均数
--concurrency100指定同时有 100 个客户端连接
--number-of-queries1000指定总的测试查询次数并发客户端数 * 每个客户端的查询次数这样本样例平均每个客户端查询 10 次
# 指定并发连接的客户端和测试查询次数
mysqlslap -uroot -pwangxia123 --concurrency100 --number-of-queries10000 --auto-generate-sql
--iterations 参数可以设置迭代执行的次数即重复的次数相同的测试进行 N 次求一个平均值。该次数指的是整个步骤的重复次数包括准备数据、测试 load、清理。
# 添加迭代次数配置
mysqlslap -uroot -pwangxia123 --iterations3 --concurrency100 --number-of-queries10000 --auto-generate-sql
--number-int-cols5指定生成 5 个 int 类型的列
--number-char-cols20指定生成 20 个 char 类型的列
# 指定列类型
mysqlslap -uroot -pwangxia123 --iterations3 --concurrency100 --number-of-queries10000 --auto-generate-sql --number-int-cols5 --number-char-cols20
--create-schematest指定的测试库是 test测试完毕不会自动删除
--querySELECT * FROM t1;使用自定义的测试语句
# 指定库
mysqlslap -uroot -pwangxia123 --concurrency3 --number-of-queries100 --create-schematest --querySELECT * FROM t1
# 生成数据
mysqlslap -uroot -pwangxia123 --concurrency3 --number-of-queries1000000 --create-schematest --queryINSERT INTO t1(name,age) VALUES (uuid(), RAND() * 100) 4、mysql主从复制原理 MySQL的主从复制是一种常用的数据复制和数据同步技术它允许将一个MySQL数据库服务器主服务器上的数据复制到其他一个或多个MySQL服务器从服务器。主从复制提供了数据冗余、读写分离、数据备份等重要功能。下面是MySQL主从复制的工作原理 主服务器Master主服务器是数据源它负责处理客户端的读写请求并记录所有的数据变更操作如插入、更新、删除到二进制日志Binary Log中。 从服务器Slave从服务器是主服务器的复制目标它从主服务器获取二进制日志文件并将这些日志文件中的数据变更操作依次应用到自己的数据库中从而保持与主服务器的数据一致性。 复制过程MySQL主从复制过程分为三个关键步骤 a. 同步初始化Sync Initialization在初始阶段从服务器与主服务器建立连接并请求获取主服务器当前的数据快照Snapshot。主服务器将快照的数据以二进制日志的形式发送给从服务器从服务器将这些数据应用到自己的数据库中。 b. 主服务器二进制日志传输一旦同步初始化完成主服务器将持续记录数据变更操作到二进制日志中并将这些日志文件以连续的方式传输给从服务器。主服务器将二进制日志文件发送给从服务器的过程中使用的是基于TCP/IP的二进制日志传输协议。 c. 从服务器应用日志从服务器接收到主服务器的二进制日志文件后将这些日志文件中的数据变更操作逐条应用到自己的数据库中保持与主服务器的数据一致性。从服务器会在本地记录已经应用的二进制日志位置以便在后续的复制过程中从正确的位置继续应用数据变更操作。 复制延迟和主从同步由于网络延迟、主服务器负载等原因从服务器的数据更新可能会有一定的延迟即复制延迟。MySQL提供了主从同步机制确保从服务器尽可能与主服务器保持数据的一致性。通过主从同步从服务器会定期向主服务器发送心跳信号主服务器会记录从服务器的复制位置并确保从服务器在断线重连后可以继续从正确的位置进行复制。 高可用性和读写分离通过配置多个从服务器可以实现MySQL的高可用性和读写分离。当主服务器故障时可以将其中一个从服务器提升为新的主服务器确保系统的可用性。同时通过将读操作分发到从服务器可以减轻主服务器的负载提高系统的性能和吞吐量。 总结起来MySQL的主从复制通过将主服务器上的数据变更操作记录到二进制日志中并将这些日志文件传输给从服务器实现了数据的复制和同步。主从复制提供了数据冗余、读写分离、高可用性等重要功能是MySQL中常用的数据复制和数据同步技术。 5、mysql主从延迟如何解决 查看MySQL主从复制的延迟情况可以采取以下方法 使用命令查看延迟在从服务器上使用MySQL命令 SHOW SLAVE STATUS\G 可以查看主从复制的状态信息。在输出结果中可以查看到 Seconds_Behind_Master 字段该字段表示从服务器当前与主服务器之间的复制延迟时间以秒为单位。如果该值为0则表示没有延迟如果该值大于0则表示存在延迟。 使用工具监控延迟可以使用一些第三方的MySQL监控工具如Percona Toolkit、pt-heartbeat等来监控主从复制的延迟情况。这些工具可以提供更详细的延迟信息和统计数据并支持设置告警机制以便及时发现和解决延迟问题。 要解决MySQL主从复制的延迟问题可以考虑以下几个方面 网络优化延迟问题可能与网络带宽和延迟有关。确保主从服务器之间的网络连接稳定并优化网络带宽可以减少复制延迟。 优化复制线程MySQL主从复制中有一个I/O线程负责读取主服务器的二进制日志另一个SQL线程负责应用日志到从服务器。优化这些复制线程的性能参数和配置如增加线程数、调整缓冲区大小等可以提高复制的效率。 减少主服务器的负载如果主服务器的负载较高可能会导致复制延迟。考虑优化主服务器的性能和负载如调整查询优化、增加硬件资源等以减轻主服务器的负载提高复制性能。 优化从服务器的配置从服务器的性能和配置也会影响复制延迟。确保从服务器具备足够的资源如CPU、内存、磁盘等并根据实际情况进行适当的配置优化以提高复制性能。 监控和调优定期监控主从复制的延迟情况并分析延迟发生的原因。根据监控结果进行调优如调整复制参数、优化查询语句、增加硬件资源等以缩小复制延迟。 6、主从切换操作 选取新的主服务器在进行主从切换之前需要选择一个新的主服务器。这可以是当前的从服务器或者是另一个备用服务器。确保选取的服务器具备足够的资源和性能来承担新的主服务器的角色。 停止主服务器的写操作在进行主从切换之前需要停止当前的主服务器上的写操作以确保在切换过程中不会有新的数据变更操作。可以使用MySQL命令 FLUSH TABLES WITH READ LOCK 来锁定所有表阻止写操作。 确认主从复制的同步状态在主服务器停止写操作之后需要确认主从复制的同步状态。使用MySQL命令 SHOW SLAVE STATUS\G 检查从服务器的 Seconds_Behind_Master 字段是否为0确认数据已经与主服务器同步。 修改从服务器的配置将之前的从服务器配置为新的主服务器。需要修改从服务器的配置文件如my.cnf中的 server-id 参数为一个新的唯一值并确保其他相关的复制参数配置正确。 启动新的主服务器启动新的主服务器并确保数据库服务正常运行。在确认新的主服务器正常运行后可以继续进行后续的步骤。 配置其他从服务器对于之前的主服务器现在需要将其配置为从服务器并指向新的主服务器。需要修改其配置文件中的 server-id 参数和主服务器的连接信息。 开放写操作在新的主服务器和其他从服务器都正常配置后可以解除之前停止的写操作。在新的主服务器上使用MySQL命令 UNLOCK TABLES 解锁所有表允许写操作继续。 检查主从复制状态在主从切换完成后使用MySQL命令 SHOW SLAVE STATUS\G 再次检查主从复制的状态确保复制正常运行并且没有延迟。
