摘要：When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good under... 阅读全文

摘要：Peter wrote a post a while ago aboutchoosing a good InnoDB log file size. Not to pick on Peter, but the post actually kind of talks about a lot of th... 阅读全文

摘要：Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount of writes in the redo logs. You can ... 阅读全文

摘要：Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with fore... 阅读全文

摘要：1. 背景介绍什么是semi-join？所谓的semi-join是指semi-join子查询。 当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点 的表也只会返回一条记录。另外，右节点的表一条记录也不会返回... 阅读全文

摘要：一 建表和现象的过程如下CREATE TABLE t1 (id1 INT, a1 INT, b1 INT, PRIMARY KEY(id1));CREATE TABLE t3 (id3 INT UNIQUE, a3 INT, b3 INT);INSERT INTO t1 VALUES (1, 1, ... 阅读全文

摘要：文/何登成 导读： 来自网易研究院的MySQL内核技术研究人何登成，把MySQL数据库InnoDB存储引擎的多版本控制（简称：MVCC）实现原理，做了深入的研究与详细的文字图表分析，方便大家理解InnoDB存储引擎实现的多版本控制技术（简称：MVCC）。基本知识假设对于多版本控制(MVCC)的基础知... 阅读全文

摘要：Yesterday I had fun time repairing 1.5Tb ext3 partition, containing many millions of files. Of course it should have never happened – this was decent PowerEdge 2850 box with RAID volume, ECC memory and reliable CentOS 4.4 distribution but still it did. We had “journal failed” message in kernel log a 阅读全文

摘要：What does working with large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables and tricks of executing queries exactly as you want. I already wrote about development and configuration side of the problem so I will not go to deta 阅读全文

摘要：If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Tech 阅读全文

摘要：Q:I read the following on this pagehttp://dev.mysql.com/doc/mysql/en/server-system-variables.htmlbulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size o 阅读全文

摘要：delay_key_writeThis option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index u 阅读全文

摘要：There have been a few posts by Sheeri and Baron today on the MySQL sort_buffer_size variable. I wanted to add some more information about this buffer, what is impacted when it is changed, and what to do about it?The first thing you need to know is the sort_buffer_size is a per session buffer. That i 阅读全文

摘要：Q:I am MySQL on server with 6GB RAM. I need to know what is the difference between myisam_sort_buffer_size and sort_buffer_size?I have following size set to them:myisam_sort_buffer_size = 8Msort_buffer_size = 256MPlease also mention if these values are fine or need adjustments?ThanksA:sort_buffer_si 阅读全文

摘要：最近，在一个系统的慢查询日志里发现有个insert操作很慢，达到秒级，并且是比较简单的SQL语句，把语句拿出来到mysql中直接执行，速度却很快。这种问题一般不是SQL语句本身的问题，而是在具体的应用环境中，由于并发等原因导致的。最可怀疑的地方就是在等待表级锁。加上监控的日志来看，很多SQL是在同一时间完成的，下面的第三列是结束时间，第四列是开始时间：14:27:30 bizId30905 1355812050 135581204514:27:30 bizId28907 1355812050 135581204314:27:30 bizId30905 1355812050 1355812... 阅读全文

摘要：在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题，所以最近打算写一个MySQL数据库性能优化方面的系列文章，希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助。这是MySQL数据库性能优化专题系列的第一篇文章：MySQL 数据库性能优化之缓存参数优化数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。本文先从 MySQL 阅读全文

摘要：INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here 阅读全文

摘要：Baronwrotenice article comparing locking hints in MySQL and SQL Server.In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is simple example: 1 SESSION1: 2 mysql> begin; 3 Query OK, 0 rows affected (0.00 sec) 阅读全文

摘要：MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually packed indexes not a bit longer rows is frequent reason of MyISAM performing better than Innodb. In this article I’ll get in a bit more de 阅读全文

摘要：好多人在调优Mysql的时候，总是对open_tables和opend_tables两个参数分别不清。网上好多解释都是这样的：open_tables:当前打开表的数量opened_tables:当前已经打开表的数量很简单的解释，可是这两句话看起来有点类似。下面我来解释一下：open_tables:是当前在缓存中打开表的数量。opened_tables:是mysql自启动起，打开表的数量。我们知道，假如没有缓存的话，那么mysql服务在每次执行一个语句的时候，都会先打开一个表。当sql语句执行完成后，则把这个表关掉。这就是opend_tables中的值。而open_tables这个值，是mysq 阅读全文