MySQL Server内存使用中包括了用于管理与客户端之间进行沟通和处理数据库内容Server所建立的数据结构。它会在运行时为许多类型的信息分配内存，如：线程(Thread Handler):线程在MySQL（或其它程序）中被用于将应用的执行分成多个同步运行的任务。对每个连接到MySQL Server的客户端，Server都会建立一个独立的线程来进行连接处理。MySQL Server为了避免一些可能产生的性能问题，会分配一块小的缓存给这些线程，在客户端断开后，被分配的缓存仍然会被保持。任何新的连接会首先通过此缓存来实例化线程，而不是重新建立一个新线程。这样就能确保内存消耗始终处于最低状态。缓存(Buffers / Cache)MySQL Server会使用多个buffers（或者称为caches），作为内存集以临时保存数据的输入或输出，目的是避免高成本的磁盘I/O访问。这些缓存主要由缓存管理子系统来进行操作，可对支持MyISAM索引块的索引缓存和持有已打开表的表述符的授权表缓存进行快速访问。此外，MySQL Server还包含了一个查询缓存(Query Cache)用于加速重复查询的处理速度。MEMORY存储引擎MySQL Server的一种存储引擎，此引擎允许表被存储在内存上。MEMORY表默认使用哈希索引，这对建立临时表及提高临时表处理速度很有用。然而，当服务器被关闭后，ME ...继续阅读 (24)

一、通过软连接的方式迁移部分表空间到其他硬盘优点：对数据没有任何影响,反而可以适当增加IO能力,使用多个磁盘的IOPS缺点：需要停机处理步骤：1、关掉mysql实例2、cp big.ibd /new/big.ibd3、rename big.ibd big.ibd.remove4、ln -s big.ibd /new/big.ibd5、chow -R mysql:mysql /new/big.ibd6、启动数据库,检查是否异常7、删掉 remove的文件.二、通过blackhole引擎,清理掉一些不重要,但是占用空间较大的表优点：不需要停机缺点：只能适用于slave,会缺少数据处理步骤:1、确认要处理的机器是slave,要处理的表不会有写入操作.2、set sql_log_bin = off; stop slave;3、rename table xx to test.xx;4、create table xxx engine = blockhole;5、start slave;6、观察数据库是否异常7、drop table if exists test.xxx猜您喜欢2014 年 11 月 26 日 --几招省磁盘空间的方法 ...继续阅读 (22)

本文整理了一些MySQL的通用优化方法，做个简单的总结分享，旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作，至于具体的SQL优化，大部分通过加适当的索引即可达到效果，更复杂的就需要具体分析了，可以参考本站的一些优化案例或者联系我，下方有我的联系方式。这是下篇。3、MySQL层相关优化3.1、关于版本选择官方版本我们称为ORACLE MySQL，这个没什么好说的，相信绝大多数人会选择它。我个人强烈建议选择Percona分支版本，它是一个相对比较成熟的、优秀的MySQL分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方ORACLE MySQL版本基本完全兼容，并且性能大约有20%以上的提升，因此我优先推荐它，我自己也从2008年一直以它为主。另一个重要的分支版本是MariaDB，说MariaDB是分支版本其实已经不太合适了，因为它的目标是取代ORACLE MySQL。它主要在原来的MySQL Server层做了大量的源码级改进，也是一个非常可靠的、优秀的分支版本。但也由此产生了以GTID为代表的和官方版本无法兼容的新特性(MySQL 5.7开始，也支持GTID模式在线动态开启或关闭了)，也考虑到绝大多数人还是会跟着官方版本走，因此没优先推荐MariaDB。3.2、关于最重要的参数选项调整建议建议调整下面几个关键参数以获得较好的性能(可使用本站提供的my.cn ...继续阅读 (17)

本文整理了一些MySQL的通用优化方法，做个简单的总结分享，旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作，至于具体的SQL优化，大部分通过加适当的索引即可达到效果，更复杂的就需要具体分析了，可以参考本站的一些优化案例或者联系我，下方有我的联系方式。这是上篇。1、硬件层相关优化1.1、CPU相关在服务器的BIOS设置中，可调整下面的几个配置，目的是发挥CPU最大性能，或者避免经典的NUMA问题：1、选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了； 2、关闭C1E和C States等选项，目的也是为了提升CPU效率； 3、Memory Frequency(内存频率)选择Maximum Performance(最佳性能)； 4、内存设置菜单中，启用Node Interleaving，避免NUMA问题；1.2、磁盘I/O相关下面几个是按照IOPS性能提升的幅度排序，对于磁盘I/O可优化的一些措施：1、使用SSD或者PCIe SSD设备，至少获得数百倍甚至万倍的IOPS提升； 2、购置阵列卡同时配备CACHE及BBU模块，可明显提升IOPS(主要是指机械盘，SSD或PCIe SSD除外。同时需要定期检查CACHE及BBU模块的健康状况，确保意外时不至于丢失数据)； 3 ...继续阅读 (13)

备注：插图来自discuz!官方LOGO，如果觉得不当还请及时告知 ：)写在前面：discuz!作为首屈一指的社区系统，为广大站长提供了一站式网站解决方案，而且是开源的(虽然部分代码是加密的)，它为这个垂直领域的行业发展作出了巨大贡献。尽管如此，discuz!系统源码中，还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎，以及基于MyISAM引擎的抢楼功能，session表采用memory引擎等，可以参考后面几篇历史文章。本次我们要说说discuz!在应对热们帖子翻页逻辑功能中的另一个问题。在我们的环境中，使用的是 MySQL-5.6.6 版本。在查看帖子并翻页过程中，会产生类似下面这样的SQL：mysql> desc SELECT * FROM pre_forum_post WHERE tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pre_forum_post type: ref possible_keys: tid,displayorder,first key: ...继续阅读 (128)

备注：插图来自淘宝苏普的博客并保留水印，如果觉得不当还请及时告知 ：)写在前面：MySQL的query cache大部分情况下其实只是鸡肋而已，建议全面禁用。当然了，或许在你的场景下还是挺好的，还能发挥作用，那就继续使用吧，把本文当做参考就好。不过，可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了，可以忽略 query_cache_type 参数的值，反正它也是可以在线调整的。事实果真如此吗？让我们来实际模拟测试下就知道了。我们模拟了以下几种场景：1、初始化时，同时设置 query_cache_size 和 query_cache_type 的值为 0； 2、初始化时，设置 query_cache_size = 0，但设置 query_cache_type = 1； 3、初始化时，设置 query_cache_size = 0，query_cache_type = 1，但是启动后立刻 修改 query_cache_type = 0 4、初始化时，设置 query_cache_size = 0，query_cache_type = 0，但是启动后立刻 修改 query_cache_type = 1 5、初始化时，设置 query_cache_size = xMB，query_cache_type = 1，但是启动后立刻 修改 query_ca ...继续阅读 (32)

问题 当前，绝大多数业务场景用InnoDB已经完全能搞定了，越来越多的业务从MyISAM转向InnoDB引擎，那么有哪些注意事项呢？ 分析 当了解完两种引擎的不同之处，很轻松的就能知道有哪些关键点了。总的来说，从MyISAM转向InnoDB的注意事项有：1、MyISAM的主键索引中，可以在非第一列(非第一个字段)使用自增列，而InnoDB的主键索引中包含自增列时，必须在最前面；这个特性在discuz论坛中，被设计用于“抢楼”功能，因此，若有类似的业务，则无法将该表从MyISAM转成InnoDB，需要自行变通实现(我们则是将其改到Redis中实现)； 2、不带条件频繁统计全表总记录数时(SELECT COUNT(*) FROM TAB)，InnoDB相对较慢，而MyISAM则飞快；不过，如果是基于索引条件的统计，则二者相差不大； 3、InnoDB在5.6以前不支持全文索引，不过这个相信无所谓，没什么人会在MySQL里直接跑全文索引，尤其是对中文的全文索引(前阵子有开发同学提需求直接被我否了)，确实有需要的话，可以采用Sphinx、Lucene等其他方案实现； 4、一次性导入大量数据并且后续还要进行加工处理的，可以先导入到MyISAM引擎表中，经过一通加工处理完后，再导入InnoDB表(我曾经在业务中用此方法提高数据批量导入及处理效率)； 5、InnoDB不支持LOAD TABLE F ...继续阅读 (32)

写在前面：我们在使用mysqldump备份数据时，请一定记住要加上-q参数，后果可能是很严重的，不要给自己挖坑哦。到底为什么呢，且听我慢慢道来！先来看看 mysqldump -help 中，关于 -q 参数的解释：-q, --quick Don't buffer query, dump directly to stdout.简言之，就是说加上 -q 后，不会把SELECT出来的结果放在buffer中，而是直接dump到标准输出中，顶多只是buffer当前行结果，正常情况下是不会超过 max_allowed_packet 限制的，它默认情况下是开启的。如果关闭该参数，则会把SELECT出来的结果放在本地buffer中，然后再输出给客户端，会消耗更多内存。在mysqldump.c中也能看到二者的对比(现在流行深入源码，虽然我不是专注开发的，找几行源码能力还尚存，用来装B的，大家知道就好，哈哈)：if (quick) res=mysql_use_result(sock); else res=mysql_store_result(sock);有理论，也要有实践不是，我们来看看在实际场景中，加不加 -q 的区别有多大。部分备份(启用-q)部分备份(禁用-q)完整备份(启用-q)完整备份(禁用-q)备份总耗时27.882秒22.665秒277.387秒217.074秒占 ...继续阅读 (28)

MySQL的自动提交默认是开启的，用惯了Oracle，习惯了自己去提交或回滚。我们可以通过set来设置autocommit，过程如下：Step 1：使用select @@autocommit来查看当前autocommit设置情况，如下图1：图1：查看autocommit1表示自动提交是开启的，0表示自动提交是关闭的，图1可看出，当前是处于开启状态。Step 2：通过set autocommit = 0来关闭autocommit，如下图2：图2：设置autocommit还有别的方法来设置，目前我只用了上面的方法。猜您喜欢2014 年 5 月 27 日 --autocommit=0引起的业务hang住 ...继续阅读 (20)

实际上本次故障的素材来自于朋友的朋友，虽然我并不是故障的亲身经历者，但即便只是作为旁观者，依然感觉有所收获，于是乎记录下来以馈读者。故障的来龙去脉大致是这样的：在一个月黑风高的晚上，苦逼的程序员被一阵急促的报警短信声惊醒，原来是数据库的某个表出问题了，虽然查询操作都正常，但创建操作却都失败了，经过调试，发现原因是表被插入了一行问题数据，其自增字段的值被显式的设置为整型的最大值，导致后续缺省插入的数据不能获取到一个合法的主键值。我们不妨创建一个测试表说明问题：CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;然后插入一行问题数据：INSERT INTO test (id, name) VALUES (2147483647, 'x');结果导致不能执行正常的插入语句：INSERT INTO test (name) VALUES ('y');此时数据库会报错：#1062 – Duplicate entry ‘2147483647’ for key ‘PRIMARY’换句话说，InnoDB 表类型会在内部维护一个 Auto Increment ...继续阅读 (16)

配置MySQL SSLMySQL默认的数据通道是不加密的，在一些安全性要求特别高的场景下，我们需要配置MySQL端口为SSL，使得数据通道加密处理，避免敏感信息泄漏和被篡改。当然，启用MySQL SSL之后，由于每个数据包都需要加密和解密，这个对MySQL的性能是有不小影响的，读者们在使用的时候，要根据实际情况斟酌。MySQL客户端登录服务器时候的密码不是明文传输，有加密策略处理。笔者是在 ubuntu12.04 系统上使用MySQL 5.5版本测试的，其他环境请读者自行匹配。配置MySQL服务器证书编辑 /etc/mysql/my.cnf 文件# ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem把上面三行默认证书配置注释打开，使用自己的证书。笔者就使用上次在搭建自己的CA服务 – OpenSSL CA 实战文章中生成的证书ssl-ca=/home/yunweipai/user_certs/ca_cert.cer ssl-cert=/home/yunweipai/user_certs/web.cer ssl-key=/home/yunweipai/user_certs/web_key_plain.pem这里需要注意的 ...继续阅读 (19)

前言对于一个web网站的性能来说，瓶颈多半是来自于数据库。一般数据库查询会在某个请求的整体耗时中占很大比例。如果能提高数据库查询的效率，网站的整体响应时间会有很大的下降。如果能实现mysql查询的异步化，就可以实现多条sql语句同时执行。这样就可以大大缩短mysql查询的耗时。异步为啥比同步快？与异步查询相反的时同步查询。通常情况下mysql的query查询都是同步方式。下面我们对两种方式做下对比。对比的例子是，请求两次select sleep(1)。这条语句在mysql服务器端大概耗时1000ms。同步方式的执行流程：第一步，向mysql服务器端发送第一次查询请求。大概耗时 1ms第二步，mysql服务器端返回第一次查询的结果。大概耗时 1000ms第三步，向mysql服务器再次发送请求。大概耗时 1ms第四步，mysql服务器端返回第二次查询的结果。大概耗时 1000ms同步的方式执行两次select sleep(1)，大概耗时 2002ms。异步方式的执行流程：第一步，向mysql服务器端发送第一次查询请求。大概耗时1ms第二步，在等待第一次请求返回数据的同时，向服务器端发送第二次查询请求。大概耗时 1ms第三步，接受mysql服务器端返回的两次查询请求。大概耗时 1000ms。对比分析异步查询比同步查询速度快，是因为多条查询语句在服务器端同时执行，大大缩短了服务器端的响应时 ...继续阅读 (28)

一.语法explain select …例如: explain select * from user where name=’ranger’;二.explain输出解释mysql> explain select * from user where name=’ranger’\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: usertype: constpossible_keys: uniq_namekey: uniq_namekey_len: 152ref: constrows: 1Extra:1 row in set (0.01 sec)1.idSELECT语句的ID编号,优先执行编号较大的查询,如果编号相同,则从上向下执行例如:mysql> explain select * from (select * from ( select * from share where id =11) a) b;+—-+————-+————+——-+—————+———+———+——+——+——-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | ...继续阅读 (14)

持续可用本文主要针对金融数据库，认为金融数据库的持续可用包含两点：一个是强一致性；另外一个是高可用性。数据库系统必须是强一致性的系统，这是因为数据库系统有事务ACID的基本要求，而弱一致系统无法做到。业内也有一些流行的NOSQL系统，例如各种类Dynamo系统，如开源的Cassandra，对同一个最小数据单位(同一行数据)允许多台服务器同时写入，虽然采用NWR机制处理冲突，但是由于不可能解决多台服务器之间的时序问题，而只能支持弱一致语义。弱一致语义的问题很多，例如无法支持复杂功能，无法构建严谨的测试体系，无法应用到核心场景。虽然弱一致性系统也有一定的应用场景，但本文认为其不符合核心业务持续可用的要求，不予讨论。高可用性可以有很多种解释，实践中最常见的解释为：在一台服务器，一个交换机，一个机房，或者一个地区整体故障后，系统能够在多长时间内恢复服务。当然，这里的恢复服务是以保证强一致性作为前提条件的。如果能够在秒级(10秒左右)恢复服务，本文认为这个系统是高可用的，绝大部分应用系统都能够容忍硬件故障导致的秒级不可用。CAP理论CAP理论网上传了很多版本，大致的意思是：一致性，可用性和分区可容忍性三者只能取其二，不可兼得。由于分区可容忍性是不可选择的，因此，系统设计时只能在一致性和可用性之间权衡。这就带来了一个很悲观的结论：持续可用无法实现。然而，事实是这样吗？首先，我们回到CAP理论的 ...继续阅读 (21)

问题通常一个web应用的性能瓶颈在数据库。因为，通常情况下php中mysql查询是串行的。也就是说，如果指定两条sql语句时，第二条sql语句会等到第一条sql语句执行完毕再去执行。这个时候，如果执行2条sql语句，每条执行时间为50ms，全部执行完毕可能需要100ms。既然，主要原因是sql的串行执行导致。那我们是不是可以改变执行方式来提高性能呢？答案是，可以的。我们可以通过异步执行的方式来提高性能。异步如果通过异步的方式去执行，可能性能会有很大提升。如果是采用异步的方式，两条sql语句会并发执行，可能就需要60ms就可以执行完毕。实现mysqli + mysqlnd。php官方实现的mysqlnd中提供了异步查询的方法。分别是：mysqlnd_async_query 发送查询请求mysqlnd_reap_async_query 获取查询结果这样就可以不必每次发送完查询请求后，一直阻塞等待查询结果了。实现代码如下：php $host = '127.0.0.1'; $user = 'root'; $password = ''; $database = 'test'; /** * 期望得到额结果 * array( * 1 =int, * 2 => int, * 3 => int * ) */ $result = arr ...继续阅读 (11)

MySQL的主从复制广泛用于数据库备份、故障转移、数据分析等场合。MySQL主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。从服务器从主服务器接收已经记录到其二进制日志的更新，当一个从服务器连接主服务器时，主服务器从日志中读取最后一次成功更新的位置，从服务器接收从那时起发生的更新，并在本机上执行相同的更新，然后等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。测试环境Master: 192.168.10.201Slave: 192.168.10.202端口： 3306数据库：test2安装MySQLyum install mariadb mariadb-server systemctl enable mariadb service mariadb start # Reset root password mysqladmin -u root password abc@DEF主服务器配置主服务器配置文件/etc/my.cnf[mysqld] server-id=1 binlog-do-db=test2relay-log=/var/lib/mysql/mysql-relay-bin relay-log-index=/var/lib/mysql/mysql-r ...继续阅读 (26)

wamp服务器是PHP+apache+mysql在windows系统下的集成环境安装程序，可以实现在windows系统中一键搭建PHP网站环境。wamp服务器在安装好以后，默认的mysql数据库的密码是空(root用户的密码是空)，空密码不是很安全，虽然默认的也只能在本机访问mysql数据库，远程是不允许访问mysql数据库的。但是，还是建议修改下mysql数据库的密码。修改mysql数据库的密码可以通过如下方式：图1：打开mysql控制台登录进控制台以后：use mysql;update user set password = PASSWORD('newpassword') where user = 'root';flush privileges; // 不输入这个，修改密码的操作不会生效修改完mysql数据库的密码以后，由于wamp自带了phpmyadmin，也还需要修改下在phpmyadmin中的配置才可以使用，不过，由于我不习惯用这个，我习惯用heidisql这个客户端软件，所以本文就不写怎么改那个了。猜您喜欢2013 年 5 月 15 日 --MySQL 5.6 新增的两个密码安全策略体验2013 年 3 月 7 日 --MySQL 生成随机密码 ...继续阅读 (14)

上一节中我们介绍了如何在CentOS 7上配置MySQL的主从复制模式，今天Lesca将介绍如何备份和还原MySQL的从服务器，平台仍然是CentOS 7。使用mysqldump进行备份和还原使用mysqldump进行备份mysqladmin stop-slave -uroot -p mysqldump --all-databases > fulldb.dump mysqladmin start-slave -uroot -p tar -czf /tmp/dbdump.tar.gz ./fulldb.dump./mysql-relay-log.info我们除了要备份了整个数据库的dump以外，还需要备份relay-log.info文件(上例为mysql-relay-log.info)，该文件包含类似如下信息：/var/lib/mysql/mysql-relay-bin.000002 720mysql-bin.0000023968红色高亮部分，指明了当前MySQL主服务器上二进制日志的执行状态。这个数据在还原从服务器的时候至关重要。使用mysqldump进行还原mysql -uroot -p < /root/dbdump.db stop slave; CHANGE MASTER TO MASTER_HOST='192.168.10.201', MASTER_USER='slave_u ...继续阅读 (43)

现在有很多的项目，对计数器的实现甚是随意，比如在实现网站文章点击数的时候，是这么设计数据表的，如：”article_id, article_name, article_content, article_author, article_view……在article_view中记录该文章的浏览量。诈一看似乎没有问题。对于小站，比如本博客，就是这么做的，因为小菜的博客难道会涉及并发问题吗？答案显而易见，一天没多少IP，而且以后不会很大。言归正传，对文章资讯类为主的项目，在浏览一个页面的时候不但要进行大量的查(查询上文的记录，已经所属分类的名字、热门文章资讯评论、TAG等)，还要进行写操作(更新浏览数点击数)。把文章的详细内容和计数器放在一张表尽管对开发很方便，但是会造成数据库的压力过大(不然为什么大项目都要分库分表呢)。那么，分两张表存放就好了么？一张表存文章详细信息，另一张表单独存计数器。CREATE TABLE `article_view`(`article_id` int(11) NOT NULL,`view` int(11) NOT NULL,PRIMARY KEY (`article_id`))ENGINE=InnoDB;这种方式，虽然分担了文章表的压力，但是每当有一个进程请求更新的时候，都会产生全局的互斥锁，只能串行，不能并行。在高并发下会有较长的等待时间。另一种比较好的办法 ...继续阅读 (14)

以下是两种slave复制异常的情况。导致的原因都是由于跨机房同步,slave的机房突然掉电导致的。案例一、这个错误大原因是Read_Master_Log_Pos: 1028687822的pos号在主库上是没有的.处理方法:获取这个pos号的前一个pos号,从新开启同步,这里注意如果是row模式的话就没有问题.如果是mix的或者statement的话,就需要去分析binlog,确认具体执行到哪个pos号了,不然可能会导致数据不一致。(andy:db:)[(none)] 11:18:39> show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.11.24Master_User: slaveMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000342Read_Master_Log_Pos: 1028687822Relay_Log_File: localhost-relay-bin.000767Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000342Slave_IO_Running: N ...继续阅读 (20)

对于使用中的已有了一些数据库的 MySQL, 如何改造成双主呢? 这里写一篇文章, 详细记录操作步骤. 听取各位的意见.这里假设有两台服务器: A 和 B. 要让他们互为主, 但实际使用时, 不同的服务器只服务不同的 db, 避免出现数据冲突.1. 修改 nginx 配置, 挂维护页这一步很重要. 你需要告诉你的用户, 你的网站在维护, 同时要确保不会再出现数据库写操作.另外, 还要停止 crontab 任务.2. 备份数据库Server A:mysqldump -uroot -p --all-databases > a_dbdump_all.sql gzip a_dbdump_all.sqlServer B:mysqldump -uroot -p --all-databases > b_dbdump_all.sql gzip b_dbdump_all.sql把线上数据库备份到本地scp test@server_a:~/a_dbdump_all.sql.gz . scp test@server_b:~/b_dbdump_all.sql.gz .3. 新建数据库同步账号Server A:GRANT REPLICATION SLAVE ON *.* TO 'userx'@'server_b' IDENTIFIED BY 'xxxxx';Server B:GRANT REPLICATIO ...继续阅读 (40)