Oracle VM Consulting
view counter

MySQL Feed

Oracle MySQL resources, news, and support articles.

The hidden mistake

There are mistakes that drive you crazy when you try to understand what went wrong.

One of the most annoying and hard to catch was this, apparently harmless line:

tungsten-sandbox -m 5.5.24 --topology all-masters -n 2 -p 7300 -l 12300 -r 10300 –t $HOME/mm -d tsb-mm

The person reporting the error told me that the installation directory (indicated by "-t") was not taken into account.

Techweek Chicago

Techweek started today in Chicago with the mayor promising to change the name of the town from ‘he Second City’ to ‘The Startup City’. Those who want to start with MySQL should attend my MySQL Hands On Lab Saturday from 3:00 to 4:45 in 4-7 A/B.

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently (like every other hour), or when there was no such thing as off-peak hours, in such cases you need consistent performance.

On Character Sets and Disappearing Tables

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we have these two tables:

pt-online-schema-change and default values

When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default value for varchar column. This however does not work for pt-online-schema-change:

Who is hogging my MySQL connections?

Got "too many connections" this morning. New attempts continuously abort. Every once in a while some slipped through, but overall behavior was unacceptable.

max_connections is set to 500, well above normal requirements.

Immediate move: raise max_connections to 600, some urgent connections must take place. But, this is no solution: if 500 got hogged, so will the extra 100 I've just made available.

Find and remove duplicate indexes

Having duplicate keys in our schemas can hurt the performance of our database:

    • They make the optimizer phase slower because MySQL needs to examine more query plans
    • The storage engine needs to maintain, calculate and update more index statistics
    • DML queries are also slower because MySQL needs update much more data
    • Our data needs more disk space so our backups will be bigger and slower
  • In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

    Percona XtraDB Cluster reference architecture with HaProxy

    This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and it could serve as a reference architecture.

    You will need 4 virtual machines. 3 for PXC and 1 for the client, which will have HaProxy. I used CentOS 6 as the operating system, the instructions are similar for any Linux distribution.

    Techweek Chicago & Lonestar PHP

    If you are attending Chicago Tech Week Chicago Tech Weekthis weekend, be sure to drop by the MySQL Hands On Lab Saturday (3:00pm – 4:45pm @ 4 – 7 A/B (222 Merchandise Mart Plaza, Chicago, IL)) as I have some promotional items that I need to give away.

    Clarification on MySQL security vulnerability

    Contrary to initial reports here and here, further investigation has revealed that under some specific and limited circumstances, Percona Server and Percona XtraDB Cluster binaries, similar to other MySQL variants, are susceptible to the security vulnerability in MySQL/M

    view counter