Find duplicates To find duplicate records you can use GROUP BY and count on the attributes which you want to check for duplicates and use HAVING to select all records with a count greater then one: Asume we have the following table and data: CREATE TABLE IF NOT EXISTS test ( column_pk INT(11) NOT NULL [...]

I compared the performance of MySQL 5.6.11 versus 4.0.30 using a read-only workload with sysbench. Performance was much better for 5.6.11 in most cases. At low-concurrency MySQL 4.0 was a bit faster. MySQL 5.6.11 was faster at high-concurrency and when doing many page reads per second. The product has improved a lot since I started using MySQL.

I followed most of my advice on building and configuring MySQL 5.6 for peak performance. I used two test servers - one for the sysbench clients and the other for mysqld. Each server has 16 real cores and 32 with HT enabled. Each test server has 144 GB of memory and fast storage that can do ~150,000 16 KB page reads/second.

The tests selected 1 row by primary key per query and all queries used auto-commit mode.



  [Read more...]

This is a long due maintenance release of openark-kit. This release includes bugfixes and some enhancements, mainly to oak-online-alter-table.

oak-online-alter-table Changes / bug fixes include:

• Support for keyword-named columns
• Use of FORCE INDEX due to lack of MySQL's ability for figure out the chunking key at all times
• --sleep-ratio option added; allows for sleep time proportional to execution time (as opposed to constant sleep time with --sleep)
• Support for chunk-retry (in case of deadlock) via --max-lock-retries)
• Fixed order of cleanup
• Fixed bug with verbose messages with non-integer

  [Read more...]

This little guide will describe the steps you have to take to use utf-8 in a complete web application using MySQL and PHP. The Database You have to specify the utf8 character set on all tables and text columns in your database. This will ensure that MySQL stores and retrieves the values encoded in UTF-8. [...]

In my previous post, I wrote about when data gets loaded in and out of cache. The goal of caching is to avoid having to perform disk IO, which if we are talking about hard drives, each operation is about 20 million times slower than CPUs or about 100 thousand times slower than memory.

So today I want to cover the subject of when are we required to do IO? But before I get there I first have to describe how IO works on all our modern operating systems.

An Introduction to Buffered IO

When you read or write to a file, by default you are speaking to a layer in between you and your disks called buffered IO. This layer is designed to increase performance and adds important features such as caching. Without going into this in detail, it’s important to

  [Read more...]

This is the required post about things I observed during this years MySQL conference.

Things that are awesome:

• The tables in sessions. I think these were here last year. They are still awesome this year.
• The new style power plugs. They solved the problem of people tripping over daisy chained power strips and the strips being accidentally turned off.
• Massive quantities of coffee and real cream.

Things that can be improved:

• Lunch tickets. I overheard the same conversation a dozen times about people not being able to find their lunch tickets or not really knowing about them.
• Make badges reversible. A badge under observation will be facing the wrong way.

Things that just bumped me:

• The music is different this year. Now it makes me feel like a teenager struggling with a breakup.

  [Read more...]

MySQL Sandbox has been updated again. The latest version is 3.0.38, which was just released. There were four releases in the space of one week, and this last one is just a polished edition.

Cherry-picking from the Change log:

• Added option --bind_address to complement the effects of --remote_access;
• The script 'enable_gtid' (for MySQL 5.6 +) now is durable. Previously the changes did not survive a restart.
• Now you can install MariaDB with its bizarre version '10.0'
• It also works well with MySQL 5.7. A bug prevented the creation of 'enable_gtid', but it is

  [Read more...]

In my ongoing quest to completely understand InnoDB’s data storage, I came across a quite small and inconsequential waste, which is nevertheless fun to write about. I noticed the following block of pages which were allocated very early in the ibdata1 system tablespace but apparently unused (unnecessary lines removed from output):

$ innodb_space -f ibdata1 space-page-type-regions

start       end         count       type                
13          44          32          ALLOCATED           

Background on the doublewrite buffer

Most people using InnoDB have heard of the “doublewrite buffer”—part of InnoDB’s page flushing strategy. The doublewrite buffer is used as a “scratch area” to write (by

  [Read more...]

I attended the OpenWest Conference in Orem, Utah, and have to say its one of the best community organised conferences. There were over 840+ people at the conference (with more walk-in’s), representing a greater than 100% growth rate compared to last year’s conference.

I gave a talk about MariaDB, and its safe to say that we’ve got many new features that that it’s getting very hard to go in-depth in a span of an hour.

One of the highlights for me was attending talks. I give so many talks, and spend a lot of time talking to people about MariaDB and MySQL, that I very

  [Read more...]

A few days ago I was doing some cleanup on a passive master database using the MySQL client. I didn't want my commands to be replicated so I executed set sql_log_bin=0 in my session.

One of my queries dropped an unused schema that I knew was corrupt, so I wasn't too surprised when the drop database command crashed the MySQL server. After the crash, the server came back up quickly, and my client automatically reconnected, so it was safe to keep running queries right?

Wrong.

When the client reconnected I lost my session state, so sql_log_bin reverted to 1, and any commands I ran from that point forward would be replicated, which I did not want.

This behavior makes sense, and it's documented in the manual:

  [Read more...]

Teradata Q1 disappoints. Actian acquires ParAccel. And more.

For 451 Research clients: Acquisitive Actian adds ParAccel to its growing database portfolio bit.ly/YgeY7k

— Matt Aslett (@maslett) April 29, 2013

For 451 Research clients: Tokutek releases TokuDB database storage engine as open source bit.ly/102xsSW

— Matt Aslett (@maslett) May 1, 2013

For 451 Research clients: Codership replicates partnership success with Galera Cluster for MySQL bit.ly/15dI8Hh

— Matt Aslett (@maslett)

  [Read more...]

News and views are an integral part of our modern daily life. When it comes to the information-hungry roles such as database professionals, such need becomes more pressing. Log Buffer is one way to keep abreast of news and views from the world of Oracle, SQL Server, and MySQL.

Oracle:

There is a significant update SLOB 2, Kevin Closson informs.

Charles Hooper is grouping Data Sets by Week Number of the Month.

Chris Antognini has shared a script that is used to demo ITL deadlocks.

As

  [Read more...]

This afternoon, Arjen Lentz and I were discussing InnoDB’s behavior without a declared PRIMARY KEY, and the topic felt interesting enough and undocumented enough to warrant its own short post.

Background on InnoDB clustered keys

In The physical structure of InnoDB index pages I described how “Everything is an index in InnoDB”. This means that InnoDB must always have a “cluster key” for each table, which is normally the PRIMARY KEY. The manual has this to say in Clustered and Secondary Indexes:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered

  [Read more...]

A cold cache, or a poorly tuned cache can be responsible for a number of performance problems. If we look at the data and indexes of InnoDB, the cache responsible is called the InnoDB buffer pool.

In earlier versions of MySQL the default value for the setting innodb_buffer_pool_size was 8M - which is a little out of date when you consider the recommended value to be 50-80% of system memory. In MySQL 5.5 the default value was increased to 128M, which is a comprimise made for users that may install MySQL on a development machine and not have it running as a dedicated server. In production, it is unlikely that you will buy a new server with less than 64GB of RAM, so it is quite typical that this setting is 50GB+

So lets talk about the behavior of the InnoDB buffer pool -

Up until and including MySQL 5.5

When MySQL

  [Read more...]

SAP HANA , having entered the data 2.0/3.0 space at the right time, has been getting traction lately; and there will be lot of users like me who wants to[...]