Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 33 Next 3 Older Entries

Displaying posts with tag: yves (reset)

Filtering by table is now possible with WaffleGrid
+1 Vote Up -0Vote Down

Since I have been a home recently, I put some time correcting bugs in WaffleGrid and adding new features. Thanks to gdb, I have been able to understand a silly bug that was affecting WaffleGrid with sysbench but, weird enough, not with dbt2. Everything is in the way connections are established. I will blog more about that soon.

Regarding the new features, it is now possible to choose which tables you want to push to memcached. For that purpose, two new parameters have been introduce:

innodb_memcached_table_filter_enable = 0 | 1   (default to 0)

to enable the filtering and

innodb_memcached_table_list = db1/table1,db2/table2   

to list the tables. This feature is filtering based on the space id so, innodb_file_per_table has to be set. Right now, the association table space_id is done only at startup so, the table has to exist. Also, since an alter table

  [Read more...]
MyISAM with key_buffer larger than 4 GB
+0 Vote Up -0Vote Down

In many applications, MyISAM can be used successfully if the proportion of write operations is only a small fraction of the read operations. As the tables grow, the 4 GB limitation in the key_buffer size caused performance issues and other strategies. Wait… did I miss something… From a recent comment to a post from Matt I re-read the MySQL documentation and realized I missed a big change that occurred with versions 5.0.52 and 5.1.23 (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size)

      As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms

So, key_buffer as large or even larger than 32 GB are possible. That is an important design consideration that needs to be taken into account when tuning a database.

What’s up with WaffleGrid?
+0 Vote Up -0Vote Down

You probably haven’t noticed but I have not blogged since the UC. It is not because I am upset by the perspective of working for Oracle, I have simply been busy tracking down an issue we have with WaffleGrid. We discovered that under high load, with DBT2 on a tmpfs, we end up with error in a secondary index. In the error of MySQL, we have entries like this one:

InnoDB: error in sec index entry update in
InnoDB: index `myidx1` of table `dbt2`.`new_order`
InnoDB: tuple DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000005; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000004; asc     ;;

TRANSACTION 14469, ACTIVE 1 sec, process no 7982, OS thread id 2995481488 updating or deleting
mysql tables in use 1, locked
  [Read more...]
Counting down the days before UC Time!!
+0 Vote Up -0Vote Down

One last reminder, we ( Yves and I ) will be appearing all over the place at the UC. Here is a quick run down of our schedule:

  • Tuesday 10:50 am - 11:35 pm (Free MySQL Camp) I will be presenting “Learning from others’ MySQL Performance Mistakes” … a journey into the sometimes silly things we all end up doing.
  • Tuesday 2-3pm Yves and I are co-presenting “Distributed Innodb Caching with memcached” … this will be an intro into the Waffle Grid project, what it’s about and what it means to you.
  • Tuesday 4:25 pm - 5:10 pm (Free MySQL Camp) Yves will be presenting “NBD (MySQL Cluster) performance tuning and pitfalls”
  • Wednesday 2-3pm I will be presenting: “SAN Performance on a Internal Disk Budget: The Coming Solid State Disk Revolution” … the title sounded better when I
  [Read more...]
Heterogeneous replication with NDB cluster
+0 Vote Up -0Vote Down

Recently, I was asked if it is possible to replicate an NDB cluster to a non-NDB MySQL database. So, I tried!

I created the following table on the MySQL master:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

and on the slave:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)

Of course, for obvious reasons, NDB only supports row based replication so I configured the master to use row based:

mysql> show global variables like 'binlog_format';
| Variable_name | Value |
| binlog_format | ROW   |
1 row in set (0.00 sec)

Then I tried and go the following error:

Last_Error: Error 'Incorrect information in file:
  [Read more...]
MySQL High availability with VCS cluster
+0 Vote Up -0Vote Down

At MySQL/Sun we do a lot of high availability setup using Heartbeat but recently I was involved, along with Harold Mayfield (a VCS expert), in project using Symantec VCS on Solaris. Of course, MySQL works perfectly well with VCS, here are a few highlights of the installation.

Apart from the share store resources and the IP resources, MySQL is configured as the following:

	Application insiderDB-app (
		StartProgram = "/san/mysql/bin/mysqld --defaults-file=/etc/my.cnf &"
		StopProgram = "/san/mysql/bin/mysqladmin -u root shutdown"
		MonitorProgram = "/san/vcs_scripts/mysql_monitor"

and the mysql_monitor script is the following:


STATUS=$(/usr/bin/echo status | /san/mysql/bin/mysql -u root 2>/dev/null |/usr/bin/grep -c Uptime) 

if [ "$STATUS" -eq "1" ]
	exit 110
	exit 100
Social networking type queries with NDB (part 3)
+0 Vote Up -0Vote Down

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  [Read more...]
Upcoming webinar on NDB Cluster 7.0 new features
+0 Vote Up -0Vote Down

I just learned that there will be a Webinar “What’s New in the Next Generation of MySQL Cluster?”, April 30th. From what I know, the webinar is supposed to be at a good technical level, it is not a marketing like introduction. If you are interested, just register at: http://www.mysql.com/news-and-events/web-seminars/display-320.html (http://www.mysql.com/news-and-events/web-seminars/display-320.html)

vmplot.sh, a useful tool for MySQL performance tuning
+0 Vote Up -0Vote Down

I don’t know if it is because of my science background, I am a physicist, I do like graphs, especially when I do performance tuning. With UNIX like operating systems, the vmstat command give you an easy way to grab many essential performance counters but, generating graphs from vmstat output with tools like OpenOffice Calc is time consuming and not very efficient. In order to solve this, I wrote a few scripts using gnuplot but they are not very easy to work with. Then, doing some benchmarks with DBT2, I found the vmplot.sh script and… I like that one. I just hacked it little bit to make it keeps the graph on screen, adding the “-persist” parameters to the gnuplot invocations. The script will produce 7 graphs that will be displayed on screen and save in png format in /tmp. The graphs it produces are the following:

  • CPU: graphs idle, user,
  [Read more...]
Efficient sequences with MySQL
+0 Vote Up -0Vote Down

As you probably now if you reading this blog, MySQL does not support SQL sequences. Logically, it is very easy to implement something equivalent to a sequence using transaction with InnoDB with a simple 2 columns table, name and value. The only problem is that a sequence update is usually composed of two SQL statements, a SELECT to read the value and an UPDATE to increment it. Fairly you would say, use a transaction to wrap the 2 statements together but then, you are probably aware that you cannot manage transactions in a function with MySQL. A clean sequence function seemed out of reach up to recently, when Brian Morin showed me a trick that solves the issues. Here how to create a get_next_val function to a sequence.

delimiter //
drop function if exists true_function //
create function true_function(p_param int) returns int
  sql security invoker
  return true
  [Read more...]
The upcoming MySQL UC 2009
+0 Vote Up -0Vote Down

The MySQL UC 2009 is coming and it is time for my own little marketing. As Matt already annonced it a few months ago we (Matt and I) are doing a WaffleGrid presentation, Distributed InnoDB caching with Memcached, Tuesday at 2PM. I am also presenting at the MySQL Camp or unconference, NBD (MySQL Cluster) performance tuning and pitfalls, also Tuesday at 4:25PM.

NDB Cluster one step closer to become a DB killer app!
+0 Vote Up -0Vote Down

If you have been following the development of the NDB Cluster storage engine lately, you are probably as excited as I am. NDB Cluster is becoming a kind of large database killer app. Look at all the nice features that have been added:

  • Replication, if you know MySQL you know what I am talking about
  • Distribution awareness, optimize query execution based on the distribution, a strong scaling factor
  • Disk based data, the possibility of pushing some columns to disk
  • Online add index, among the only online DDL I know of in MySQL
  • Multi-threading, no more need to configure many data nodes per server
  • Realtime, when query execution times matter

and I probably miss some. And now, with version 7 (renamed from 6.4) it is possible to extend a NDB cluster

  [Read more...]
Improvement in subquery execution with the upcoming MySQL-6.0 branch
+0 Vote Up -0Vote Down

Although I know the JOIN syntax very well, IN clauses are often so much easier to read. A few years ago I was developing a custom ERP application on SAPDB/MAXDB (still opensource at the time) and I used extensively IN clauses to make complex queries easier to read. Of course with MySQL I have been fairly disappointed up to now by the performance of the IN clause. Look at the following explain from a 5.1.x database:

mysql> explain select u2.name from user u2 where u2.id IN (select Friend from friend where friend.UserID IN (select id from user where name = 'Yves'));
| id | select_type        | table  | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
  [Read more...]
Social networking type queries with NDB (part 2)
+0 Vote Up -0Vote Down

Recently, I talked about how to optimize social networking type queries for the NDB storage engine using IN clause statements. In clauses are great but they have one fundamental limitation, they work only on one column (Actually, this is not true, I discovered, thanks to Roland’s comment, that MySQL supports multiple columns IN clause). What if the primary key is a composite of let’s say “region_id”, “application_id” and “user_id”? Recently, while on site with a client, Brian Morin showed me a very clever way of dealing these type of primary keys in an IN clause. The main problem is that you cannot return a binary or varbinary from a function. So the idea was to used the return values of a stored proc. First we need to compose the varbinary from the actual values with this stored proc:


  [Read more...]
High performance replacement of the MySQL Memory storage engine with NDB
+0 Vote Up -0Vote Down

People often wants to use the MySQL memory engine to store web sessions or other similar volatile data.
There are good reasons for that, here are the main ones:

  • Data is volatile, it is not the end of the world if it is lost
  • Elements are accessed by primary key so hash index are good
  • Sessions tables are accessed heavily (reads/writes), using Memory tables save disk IO

Unfortunately, the Memory engine also has some limitations that can prevent its use on a large scale:

  • Bound by the memory of one server
  • Variable length data types like varchar are expanded
  • Bound to the CPU processing of one server
  • The Memory engine only supports table level locking, limiting concurrency

Those limitations can be hit fairly rapidly, especially if the session payload data is large. What is less known is that NDB Cluster can creates

  [Read more...]
WaffleGrid ported to the InnoDB plugin 1.0.3
+0 Vote Up -0Vote Down

For the interested, I just ported Wafflegrid to the newest version of the InnoDB pluging, v. 1.0.3. Meanwhile, I also corrected a small bug with the CRC_32 code that wrongly cause MySQL to report corrupted blocks after being retrieved from memcached. To get the new code, simply do:

bzr branch lp:~y-trudeau/wafflegrid/waffleGrid-Innodb-plugin

and replace the directory storage/innobase by what you will retrieve from launchpad. If you are new to WaffleGrid, you will also need a custom version of memcached and a modified build script for MySQL. All these can be obtained at:


You will also need to change the file memcached_constants.h in libmemcached with the following value:


This gives a 20x perf gain… Yes… WaffleGrid is still experimental.

The early end of the MiniWheatFS project for tmpfs with MySQL!!!
+0 Vote Up -0Vote Down

I am happy to say that I found a kernel level way to join a ramdisk (actually not a tmpfs strictly speaking) with a normal file system. This cut my motivation for the MiniWheatFS project by 99.999%. I recall that the goal of the MiniWheatFS project was to provide and efficient filesystem for the the “tmpdir”, where MySQL puts its temporary files and tables. The trick uses the tendancy of ext2 (and probably ext3 and 4) to use the first available block from its bitmap and LVM to join a ramdisk with a normal device. Here are my steps.

1. Give a ramdisk to LVM

root@yves-laptop:/home/yves# pvcreate /dev/ram0
  Physical volume "/dev/ram0" successfully created

By default, my Ubuntu laptop creates 16 ramdisk of 64 MB each. RAM is not allocated until used. To create bigger one, you need to add a ramdisk_size=SizeInKB to the kernel

  [Read more...]
Social Networking type queries with NDB (part 1)
+0 Vote Up -0Vote Down

NDB Cluster is the only integrated sharding framework that I know of (educate me if I am wrong) but it is known to have issues with large joins. These days, large databases that would benefit from a sharding framework are often for social networking type applications that requires large joins.

Actually it is not NDB that is the root cause of the joins problem, it is the way MySQL executes joins. Instead of asking the cluster for a large number of rows for the secondary table it joins to, the current version of MySQL does ask one row at a time. NDB cluster answers those queries very rapidly but, the time to hop over the network kills performance. The MySQL-6.0 branch will implement the Batch Key Access (BKA) algorithm which will solve that issue and might create database application killer with NDB cluster.

Although right now BKA is not available, there are ways to execute those queries in an

  [Read more...]
NDB cluster and Max_connections
+0 Vote Up -0Vote Down

NDB cluster is a strange beast. Usually, performance wise, it is a good idea to limit the number of threads inside MySQL, that’s why there are parameters like thread_concurrency and innodb_thread_concurrency. MySQL is known to show mutexes contention with a number of active threads greater than a hundred (actually even less) but with NDB the situation is quite different since threads have to wait for the network latency. With NDB Cluster, be prepared to use unusually high numbers of connections and be prepared to crank up the number of active workers if you want to push NDB to its limit. The following figure shows some tests I made recently during one my engagements. As one can see, the number of active connections has an important impact on the overall throughput and it peaks at approximately 800 connections!!!

  [Read more...]
InnoDB secondary index file structure
+0 Vote Up -0Vote Down

In my previous Post, we took a look at the file structure of the InnoDB primary key index. With the same table structure, but a slightly different dataset:

mysql> select * from test_innodb_growth limit 10;
| id | data       | spacer |
|  1 | a          | |      |
|  2 | aa         | |      |
|  3 | aaa        | |      |
|  4 | aaaa       | |      |
|  5 | aaaaa      | |      |
|  6 | aaaaaa     | |      |
|  7 | aaaaaaa    | |      |
|  8 | aaaaaaaa   | |      |
|  9 | aaaaaaaaa  | |      |
| 10 | aaaaaaaaaa | |      |
10 rows in set (0.00 sec)

All the rows after id = 10 have data = ‘aaaaa’. Let’s take a look at the secondary index ‘idxdata’. I recently had an argument with a client that claimed that a varchar is fully expanded in

  [Read more...]
InnoDB file structure, a look a the primary key tree structure
+0 Vote Up -0Vote Down

Recently, I found a very handy tool, innodb-recovery, that can break an InnoDB file in pages. I am a visual type person so what a better occasion to try to learn more about the InnoDB file structure. To explore the file structure, I used the following table:

Create Table: CREATE TABLE `test_innodb_index` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(50) DEFAULT NULL,
  `spacer` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxdata` (`data`)

and inserted a bunch of rows like these:

select * from test_innodb_index limit 4;
| id | data  | spacer |
|  1 | aaaaa | |      |
|  2 | aaaaa | |      |
|  3 | aaaaa | |      |
|  4 | aaaaa | |      |
4 rows in set (0.00 sec)

After the insertion, the table status looked like

  [Read more...]
Introducing MiniWheatFS
+0 Vote Up -0Vote Down

It is well known that MySQL can use a lot of temporary files and that, using a ramdisk (tmpfs on Linux) can improve performance drastically as Matt has shown here. The problem with tmpfs is its limited size. You need just one query creating a huge temporary file that happens once per day to screw up the tmpfs scenario. I have done some fuse programming in my previous job and I think it is possible to use fuse to mix a tmpfs filesystem with a regular filesystem, the regular filesystem being used only when the tmpfs is full. Fuse has a lower performance level than a regular filesystem but I think the possibility to use tmpfs the vast majority of the queries needing a temporary file will overcome the performance limitation of fuse.

That is the explanation for the MiniWheatFS name, the frosted side is the tmpfs and the healthy side is the regular filesystem.

  [Read more...]
Using local tables and replication in a clever way with NDB
+0 Vote Up -0Vote Down

Tuning queries for MySQL NDB cluster is way trickier than tuning for any other storage engines. This is especially true for highly normalized schema. Look at the following query trying to retrieve all male account from a given city. Since a city is not unique, we also need to specify the state.

SELECT a.First_name, a.Last_name
FROM Account a INNER JOIN Cities c ON a.City_id = c.Id
  INNER JOIN States s ON c.State_id = s.Id
  INNER JOIN Gender g ON a.Gender_id = g.Id
WHERE c.Name = ‘Columbia’ AND s.Name = ‘South Caroline’ and g.Name = ‘Male’;

If you look at how the query will be executed with NDB, you realize that many hops over the network will be needed, at least one per table. If you push the cluster a lot, these extra hops will limit the performance. Since tables like Cities, States and especially Gender are nearly

  [Read more...]
WaffleGrid new features
+0 Vote Up -0Vote Down

Recently, I have been traveling a lot to the west coast (I live in Quebec) so I had plenty of time to update WaffleGrid while flying. Here are the new features/ports:

  • Dynamic memcached servers list
  • Matt enhanced stats ported from the non-plugin InnoDB version
  • CRC32 checksum for each element sent and retrieved from memcached
  • Better thread protection
  • So now, if you update the innodb_memcached_servers with a statement like “set global innodb_memcached_servers = ’serverA,serverB:11212′;” then MySQL will disconnect from the current servers and reconnect to the new ones. The key prefix is incremented to prevent cache coherency problems.

    You might have noticed that Matt added many WaffleGrid statistic counters like shown here. Those counters have been ported to the InnoDB plugin version.

      [Read more...]
    NDB realtime options, choosing CPU and balancing interrupts
    +0 Vote Up -0Vote Down

    With the NDB realtime options, you can choose on which CPU the execution thread and the maintenance will be running. The point is, which CPUs to use.

    The output of “cat /proc/interrupts” will help you determine which CPU to use. Here is an example of a dual quad-cores box:

    $ cat /proc/interrupts
               CPU0       CPU1       CPU2       CPU3       CPU4       CPU5       CPU6       CPU7

      [Read more...]
    WaffleGrid webinar
    +0 Vote Up -0Vote Down

    For the ones who are interested, there will be a webinar on WaffleGrid next Tuesday, February the 17th at 10:00 am PST (1:00 pm EST, 18:00 GMT). Matt will present benchmarks he did with Dbt2 and Sysbench using GbE, Dolphin, SSD while, for my part, I will present the WaffleGrid concepts, status and roadmap. I will also be a good way to learn how to get started with WaffleGrid if you want to start experimenting!

    Registration (http://www.mysql.com/news-and-events/web-seminars/display-279.html) is free!

    Two heads are better then one…
    +0 Vote Up -0Vote Down

    In case you have not noticed, while I remain the Original Big DBA Head…  Yves Trudeau Has earn the title of Big DBA Head as well.  I have noticed that I was getting credit for a couple of his posts, so I wanted to set the record straight.   While Yves and I actually both started on the same day in the professional services group for MySQL, we are not the same person:)

    Reducing latency of queries with NBD Cluster realtime extensions
    +0 Vote Up -0Vote Down

    I was recently involved in a project where the main requirement was the smallest possible latency for queries. The queries were simple insert and update statements, the update being by primary key. The cluster was using regular Gigabits Ethernet and formed by 3 servers with 8 cores each, one Management node and 2 data nodes. Since throughput was not a concern, a design decision has been made to locate the MySQL daemons on the same servers as the data nodes, in order to save network hops. MySQL chooses the closest NDB data node as its transaction coordinators and the closest on will be reachable on localhost.

    Apart from the architectural decision to host MySQL and the NDB nodes on the same boxes, we decided to benchmark the new real time options of NDB. The options are the following:

  • RealtimeScheduler: enable the real time schedule, was set to 1
  • LockExecuteThreadToCPU: assign
  •   [Read more...]
    WaffleGrid roadmap changes… a storage engine?
    +0 Vote Up -0Vote Down

    If you followed the recent blog posts by Matt, you have noticed the nice performance. This confirms that the Wafflegrid concept is a good one and we need to continue its development and improve it.

    You have also probably noticed there has been no recent WaffleGrid release, the reason is that we were thinking about how to distribute WaffleGrid. The current “patch” like approach is, let’s say, pretty poor. Another approach would be to work on the whole tree of MySQL or Drizzle but then, it would be pretty hard for someone to use InnoDB and WaffleGrid at the same time, not ideal either.

    Hey…. MySQL has a plugin interface for storage engines, why not just add a WaffleGrid storage engine. It would be much easier to add WaffleGrid to MySQL and Drizzle and we would also be far less impacted by new releases of the databases. So, the next release will be a branch of InnoDB

      [Read more...]
    Linux Swappiness
    +0 Vote Up -0Vote Down

    Have you ever been upset by the Linux tendancy to swap… Especially when trying to allocate a large InnoDB buffer pool.. Look at the following output:

    yves@yves-laptop:~$ free
    total used free shared buffers cached
    Mem: 2041888 1991096 50792 0 52 954592
    -/+ buffers/cache: 1036452 1005436
    Swap: 975200 1308 973892

    There is still 50792 + 52 + 954592 = 1005436 of free memory and Linux starts to swap!!! The reason is hidden here:

    yves@yves-laptop:~$ cat /proc/sys/vm/swappiness

    The swappiness controls the Linux to swap for the File cache. For a file server or a web server or even MySQL with MyISAM tables, the file cache is interesting but for InnoDB or NDB Cluster it is

      [Read more...]
    Showing entries 1 to 30 of 33 Next 3 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.