As we know different storage engines in MySQL have different file structures. Every table in MySQL 5.6 must have a .frm file in the database directory matching the table name. But where the rest of the data resides depends on the storage engine.

For MyISAM we have .MYI and .MYD files in the database directory (unless special settings are in place); for InnoDB we might have data stored in the single table space (typically ibdata1 in the database directory) or as file per table (or better said file per partition) producing a single file with .ibd extension for each table/partition. TokuDB as of this version (7.1.7) has its own innovative approach to storing the table contents.

I have created the table in the database test having the following structure:

CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL,
  `c` varchar(15) NOT NULL, …

One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

There are number of causes for running out of connections, the most common ones involving when the Web/App server is creating unexpectedly large numbers of connections due to a miss-configuration or some script/application leaking connections or creating too many connections in error.

The solution I see some people employ is just to increase max_connections to some very high number so MySQL “never” runs out of connections. This however can cause …

I recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here. At the end of this article you’ll find a full transcript of the Q&A from the live session.

Details:

View this webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can …

XA transactions are an open standard for distributed transactions. A distributed or global transaction can spawn multiple databases. XA transactions come handy, for example, when data sets are sharded over multiple servers and a business transaction affects multiple shards. The free MySQL Fabric tools help to setup and manage a sharded MySQL cluster. The development version of PECL/mysqlnd_ms 1.6 helps with XA transactions. It abstracts SQL details and acts as a transaction manager. The PHP MySQL driver kicks in when things go wrong and XA transactions show their nasty side: blocked servers. Good news: this is a rare case. Bad news: a deep dive …

Using GTID to attach an asynchronous Slave sounds promising. Lets have a look at the two existing GTID implementations and their integration with Galera.

GTID@MariaDB

There is one GTID used by the cluster and every node increments the common seqno by itself. This works well as long all transactions are replicated by Galera (simplified InnoDB). Because Galera takes care of the Commit Order of the transactions on all nodes. So having identical GTID/seqno from the start there are no problems.

  node1> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 |
  +-----------------+---------+

  node2> show global variables like 'gtid_binlog_pos';
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | gtid_binlog_pos | 0-1-504 | …

Galera the synchronous Master-Master replication is quite popular. It is used by Percona XtraDB Cluster, MariaDB Galera Cluster and even patched MySQL binaries exist. Quite often you want to add a slave to a Galera Cluster. This is going to work quite well. All you need is at least configure log_bin, log_slave_updates and server_id on the designated Masters and attach your Slave.

GTID@MariaDB

Even you can use traditional (non GTID) replication. Using non GTID replication is a hassle. As you need to search for the right offset on the new Master to attach your Slave on.

Using GTID promises to be easier. As you simply switch to the new Master and the replication framework finds the new position based on the GTiD automatically.

As a fact we have two GTID implementations

• GTID@MySQL/Percona
• GTID@MariaDB

…

Hi, I recently had the experience of assisting with a migration of a customer MySQL installation to Amazon RDS (Relational Database Service). Amazon RDS is a great platform for hosting your MySQL installation and offers the following list of pros and cons:

• You can scale your CPU, IOPS, and storage space separately by using Amazon RDS. Otherwise you need to take downtime and upgrade physical components of a rack-mounted server.
• Backups, software version patching, failure detection, and (some) recovery is automated with Amazon RDS.
• You lose shell access to your DB instance
• You lose SUPER privilege for regular users. Many SUPER-type statements and commands are provided for as a Stored Procedure.
• It is easy to set up multiple read replicas (slaves in READ_ONLY=1 mode).
• You can set up a secondary sychronous …

A bit of history

The latest version of Red Hat Enterprise Linux, one of the most popular and respected Linux distributions in the server market, was released in June 2014, followed by CentOS 7 and Oracle Linux releases in July of the same year.

There are very interesting changes for database administrators in these new releases, among which I would like to highlight the fact that installer now chooses XFS as its filesystem by default, which substitutes ext4 as the preferred format for local data storage. Red …

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why. Most of the clients that we support run on the Amazon cloud using either RDS … Continue reading →

看到叶金荣的一篇关于mysql分页的文章,结合雅虎之前发的一篇PDF 谈谈自己的看法

在叶子的文章里谈到了使用inner join 从而减少了对page的扫描也就是减少了所谓的回表 例如：

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通过直接对id的操作 而不是整张表的扫描 通过id 的join 抓出符合条件id 然后通过ID 再去做数据的抓取。这样就避免了对不需要的页面的扫描。

不过这样也不是最佳的方法 还可以通过对id 的 range更加缩小范围 例如：

我们要分100条记录分一页 可以写成

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;