February 7th, 2010
The next presentation by Piotr Biel from Percona was on Multi-Master Manager for MySQL.
The introduction included a discussion of the popular MySQL HA solutions including:
- MySQL Master-slave replication with failover
- MMM managed bi-directional replication
- Heartbeat/SAN
- Heartbeat/DRBD
- NDB Cluster
A key problem that was clarified in the talk is the discussion of Multi-Master and this IS NOT master-master. You only write to a single node. With MySQL is this critical because MySQL replication does not manage collision detection.
The MMM Cluster Elements are:
- monitoring node
- database nodes
And the Application Components are:
MMM works with 3 layers.
- Network Layer – uses a virtual IP address, related to servers, not a physical machine
- Database Layer
- Application Layer
MMM uses two roles for management with your application.
- exclusive – also known as the writer
- balanced – also known as the reader
There are 3 different statuses are used to indicate node state
- proper operation
- maintenance
- fatal errors
The mmm_control is the tool used to manage the cluster including:
- move roles
- enable/disable individual nodes
- view cluster status
- configure failover
The Implementation challenges require the use of the following MySQL settings to minimize problems.
- auto_increment_offset/auto_increment_increment
- log_slave_updates
- read_only
FOSDEM 2010 MySQL Developer Room Schedule
FOSDEM 2010 Website
Brussels, Belgium
February 7, 2010
Tags: fosdem, high availability, mmm, MySQL
Posted in Databases, FOSDEM 2010, MySQL, Professional | No Comments »
February 7th, 2010
The slides for my presentation at FOSDEM 2010 are now available online at slideshare. In this presentation I describe a successful client implementation with the result of 10x performance improvements. My presentation covers monitoring, reviewing and analyzing SQL, the art of indexes, improving SQL, storage engines and caching.
The end result was a page load improvement from 700+ms load time to a a consistent 60ms.
Tags: caching, explain, indexes, InnoDB, memcached, MySQL, performance
Posted in Databases, FOSDEM 2010, InnoDB, MySQL, Professional, Uncategorized | No Comments »
February 7th, 2010
Following the opening keynote “Dolphins, now and beyond”, Marc Delisle presented on “State of phpMyAdmin”.
phpMyAdmin is an DBA administration tool for MySQL available today in 57 different languages. This is found today in many distributions, LAMP stack products and also in cpanel. The product is found at http://phpmyadmin.net.
There are current two versions, the legacy 2.x version to support older php 3.x & 4.x, The current version 3.x is for PHP 5.2 or greater.
The current UI includes some new features including.
- calendar input for date fields
- meta data for mime types e.g images, which is great for showing the output as an image, otherwise blob data
- Relational designer with the able to show and create foreign keys
The New features in 3.3 (currently in beta) include:
- Replication support including configuring master/slave, start/stop slave.
- Synchronization model showing structure and data differences between two servers and ability to sync.
- New export to php array, xslx, mediawiki, new importing features including progress bar.
- Changes tracking for changes on per instance or per table. Providing change report and export options.
FOSDEM 2010 MySQL Developer Room Schedule
FOSDEM 2010 Website
Brussels, Belgium
February 7, 2010
Tags: fosdem, MySQL, Open Source, phpmyadmin
Posted in Databases, FOSDEM 2010, MySQL, Professional | No Comments »
February 7th, 2010
I had the honor of opening the day at the MySQL developer room at FOSDEM 2010 where I had a chance to talk about the MySQL product and community, now and what’s happening moving forward.
For those that missed the talk, my slides are available online at Slideshare however slides never due justice to some of the jokes including:
- What do you consider? the Blue Pill, or the Red Pill
- Why think two dimensionally, how about the Green Pill
- Emerging Breeds with performance enhancing modifications
Posted in Databases, FOSDEM 2010, MySQL, Professional | 1 Comment »
January 26th, 2010
The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.
[mysqld]
...
log-bin=mysql-bin
server-id=1
query_cache_size = 100M
query_cache_type = 1
...
[mysqld_safe]
...
key_buffer_size=600M
skip-innodb
...
In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.
mysql> show global variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+
Be sure to add the right options to the [mysqld] section.
What I didn’t know until yesterday was that some programs read from multiple groups. From the 5.1.2. Server Command Options MySQL reference manual page. In helping the describe the problem for the benefit of readers I actually learned something new myself.
mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads options from the [mysqld] and [mysql.server] groups.
I have for example always put log-error in both the [mysqld_safe] and [mysql]d sections because both of these write different errors. Seems that is unnecessary.
Tags: configuration, my.cnf, MySQL
Posted in Databases, MySQL, Professional, Uncategorized | 3 Comments »
January 26th, 2010
I hit this interesting problem last night on 5.0.51a.
mysql> use information_schema;
mysql> desc routines;
ERROR 1 (HY000): Can't create/write to file '/home/tmp/#sql_fea_1.MYD' (Errcode: 24)
mysql> show create table routines\G
*************************** 1. row ***************************
Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
`SPECIFIC_NAME` varchar(64) NOT NULL default '',
`ROUTINE_CATALOG` varchar(512) default NULL,
`ROUTINE_SCHEMA` varchar(64) NOT NULL default '',
`ROUTINE_NAME` varchar(64) NOT NULL default '',
`ROUTINE_TYPE` varchar(9) NOT NULL default '',
`DTD_IDENTIFIER` varchar(64) default NULL,
`ROUTINE_BODY` varchar(8) NOT NULL default '',
`ROUTINE_DEFINITION` longtext,
`EXTERNAL_NAME` varchar(64) default NULL,
`EXTERNAL_LANGUAGE` varchar(64) default NULL,
`PARAMETER_STYLE` varchar(8) NOT NULL default '',
`IS_DETERMINISTIC` varchar(3) NOT NULL default '',
`SQL_DATA_ACCESS` varchar(64) NOT NULL default '',
`SQL_PATH` varchar(64) default NULL,
`SECURITY_TYPE` varchar(7) NOT NULL default '',
`CREATED` datetime NOT NULL default '0000-00-00 00:00:00',
`LAST_ALTERED` datetime NOT NULL default '0000-00-00 00:00:00',
`SQL_MODE` longtext NOT NULL,
`ROUTINE_COMMENT` varchar(64) NOT NULL default '',
`DEFINER` varchar(77) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I tried in on my MySQL 5.1 server without incident. In a few minutes of thinking about it, I was unable to determine the problem. Anybody care to guess?
Posted in Databases, MySQL, Professional, Uncategorized | 4 Comments »
January 20th, 2010
My talk on 10x performance improvements – A case study has just been approved for the 2010 MySQL Conference. This will be my 5th straight year speaking at the MySQL conferences. For those in Europe wanting a sneak peek I am also speaking at FOSDEM 2010 in Brussels on Feb 7th where I’ll be giving an abridged version.
As an independent MySQL consultant, my work generally covers performance tuning and scalability and sometimes database architecture. Often however my work involves a review of a given problem and recommendations (immediate, short and long term). I’m rarely involved in the full implementation and generally do not see the full fruits of the proposed work.
Recently however I was able to work with a client, first in resolving critical performance issues and then in a review of the application architecture and MySQL environment, provide recommendations and also helping internal resources in the successful implementation. The result was a very successful engagement and an ideal case study on a strategy for tackling improving performance of an application using MySQL.
With an existing environment that included a MySQL master, 3 database slaves and 6 web servers this application provided the grounds of a well sized configuration and the need for greater availability and scalability.
The Call for Papers for the 2010 MySQL conference is still open. If you would like to share your experiences with MySQL submit your talk today.
Tags: conference, MySQL, performance
Posted in Databases, MySQL, Open Source, Professional, mysqluc2010 | 1 Comment »
January 4th, 2010
For those in the US the annual MySQL UC is taking place again in April. For those in Europe we have dedicated room for MySQL and MySQL related products/variants/branches at FOSDEM 2010 being held in Brussels, Belgium on 6-7 Feb.
This conference will feature a full day of talks with a format of 20 minutes presentation and 5 minutes Q&A. More information about submissions can be found at Call for Papers for “MySQL and Friends” Developer Room at FOSDEM 2010 now open!
Other references:
Updated
Wednesday January 6th is the last date for submissions. Extension for FOSDEM MySQL
Tags: conference, europe, fosdem, MySQL
Posted in Databases, MySQL, MySQL Events, MySQL User Conferences, Professional | 2 Comments »
December 23rd, 2009
When looking at a set of SQL statements in isolation with tools such as the slow query log, processlist and tcpdump/mk-query-digest it is easy to identify queries that are slow.
It is more difficult however to identify the frequency of the query, and whether the slow query is actually acceptable in your overall system design.
I very simple technique to help you is to comment your queries. For example:
SELECT /* 10m cache */ ...;
When I’m working for a client, this process enables me to realize the purpose of the query more quickly and to help prioritize which queries I need to improve first. This does not mean I simply ignore these less frequent queries, however my choices for reviewing and indexing queries on given tables is adjusted generally for OLTP queries first, and batch second.
As with all practices it is important to implement across your full code base.
Tags: moniting, MySQL, slow query
Posted in Databases, MySQL, Professional | 3 Comments »
December 21st, 2009
It finally dawned on me while reflecting on the year past this Sunday that the missing voice since the announcement of the Oracle acquisition of Sun Microsystems (and therefore MySQL) has been the MySQL employees.
When I worked as an employee for MySQL Inc, the acquisition by Sun Microsystems in 2008 lead to several requirements about the acquisition.
- You were not allowed to talk about the acquisition publically.
- You were not allowed to communicate with any Sun (i.e. the acquirer) resources.
In other words it was “business as usual” which is really an oxymoron, because business will never be exactly as it was before the announcement. The ongoing delay in pending acquisition by Oracle Corporation is really hurting everybody with getting on with doing their jobs, being happy with their work, and making a difference in open source and in the lives of all the benefit from using MySQL.
I’m sure many that have words to say are disappointed, worried or even fearful of their own future careers. Comments are always welcome via Mr Anonymous using 10 minute email.
Tags: acquisition, MySQL, Oracle, Sun
Posted in Databases, MySQL, Oracle, Professional, Sun, Uncategorized | 11 Comments »
