What compression do you use?

July 13th, 2012

The following is an evaluation of various compression utilities that I tested when reviewing the various options for MySQL backup strategies. The overall winner in performance was pigz, a parallel implementation of gzip. If you use gzip today as most organizations do, this one change will improve your backup compression times.

Details of the test:

  • The database is 5.4GB of data
  • mysqldump produces a backup file of 2.9GB
  • The server is an AWS t1.xlarge with a dedicated EBS volume for backups

The following testing was performed to compare the time and % compression savings of various available open source products. This was not an exhaustive test with multiple iterations and different types of data files.

Compression
Utility
Compression Time
(sec)
Decompression Time
(sec)
New Size
(% Saving)
lzo (-3) 21 34 1.5GB (48%)
pigz (-1) 43 33 995MB (64%)
pigz (-3) 56 34 967MB (67%)
gzip (-1) 81 43 995MB (64%)
fastlz 92 128 1.3GB (55%)
pigz [-6] 105 25 902MB (69%)
gzip (-3) 106 43 967MB (67%)
compress 145 36 1.1GB (62%)
pigz (-9) 202 23 893MB (70%)
gzip [-6] 232 78 902MB (69%)
zip 234 50 902MB (69%)
gzip (-9) 405 43 893MB (70%)
bzip2 540 175 757MB (74%)
rzip 11 minutes 360 756MB (74%)
lzo (-9) 20 minutes 82 1.2GB (58%)
7z 33 minutes 122 669MB (77%)
lzip 47 minutes 132 669MB (77%)
lzma 58 minutes 180 639MB (78%)
xz 59 minutes 160 643MB (78%)

Observations

  • The percentage savings and compression time of results will vary depending on the type of data that is stored in the MySQL database.
  • The pigz compression utility was the surprising winner in best compression time producing at least a size of gzip. This was a full 50% faster than gzip.
  • For this compression tests, only one large file was used. Some utilities work much better with many smaller files.

Find our more information of these tests and the results in Effective MySQL: Backup and Recovery

Recent Presentations Buenos Aires MySQL/NoSQL/Cloud Conference

July 12th, 2012

The first annual Latin America MySQL/NoSQL/Cloud Conference was held in Buenos Aires Argentina from June 26-28. Kudos to Santiago Lertora from Binlogic who had the vision for the conference in his country and made it happen. I look forward to the second annual event.

My first presentation was “Improving Performance with Better Indexes”. This presentation details the six steps to SQL performance analysis, Capture, Identify, Confirm, Analyze, Optimize and Verify. An explanation of MySQL EXPLAIN, and working examples to create indexes and better covering indexes in several examples are provided. A production example of a 13 table join is used to detail how covering indexes and partial column indexes can make a dramatic improvement in performance. Download Presentation (PDF).

More detailed information about EXPLAIN and creating indexes is available in book Effective MySQL: Optimizing SQL Statements.

My second presentation was “MySQL Backup and Recovery Essentials”. This presentation covers the most common options for MySQL backup and the respective restore options. Also covered is the importance of the master binary logs and point in time recovery capabilities. Download Presentation (PDF)

More detailed information about the right backup and recovery strategy and associated tools is available in book Effective MySQL: Backup and Recovery.

References

Latin America MySQL/NoSQL/Cloud Conference Program.

Upcoming MySQL Connect Presentations

July 11th, 2012


The MySQL Connect 2012 conference event being held in San Francisco on Sep 29-30 has a long list of quality MySQL speakers including myself. I will be giving 2 presentations on:

CON8322 – Lessons from Managing 500+ MySQL Instances

In this presentation, learn about the issues of managing a large number of instances of MySQL, supporting 50 billion SQL statements per day. Topics covered:
• The need for monitoring and instrumentation
• How to automate installations, upgrades, and deployments
• Issues with MySQL’s Replication feature with 300 slaves per master
• Traffic minimization techniques
• Creating high availability with regions and zones
• Real-time traffic stats (aggregated every five seconds)

CON8320 – Improving Performance with Better Indexes

Learn how to use one simple advanced technique to make better indexes in MySQL and improve your queries by 500 percent or more. Even with a highly indexed schema, you can achieve significant improvements in performance by creating better indexes. This presentation introduces an approach to correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then it discusses not only how to apply indexes to improve query performance but also how to apply better indexes and provide even greater performance gains.

You can also read more information with my Interview about MySQL Connect.

Recent Presentations at Charlotte South East LinuxFest

July 10th, 2012

At the recent South East LinuxFest in June 2012 I gave two MySQL presentations.

The first was on Explaining the MySQL Explain. This presentation details the MySQL Query Execution Plan (QEP) of an SQL statement and how to understand and interpret the information from the EXPLAIN command. Also discussed are additional commands and tools that exist to add supplementary information. These are essential skills that will be used daily in production operations. Download Presentation (PDF)

Effective MySQL: Optimizing SQL StatementsMore detailed information about EXPLAIN and associated commands is available in book Effective MySQL: Optimizing SQL Statements.

Effective MySQL:Backup and Recovery
The second was on MySQL Disasters, and how to avoid yours. Organizations are always making improvements for scalability, however disaster preparedness is the poor cousin. This presentation will show you how to easily avoid the most common MySQL disaster situations.
Backup and recovery is critical for business continuity, many websites run the risk of data loss or corruption because existing procedures (if any) are generally flawed.
Download Presentation (PDF

More detailed information about the right backup and recovery strategy and associated tools is available in book Effective MySQL: Backup and Recovery.

References

South East Linux Fest Agenda

REPOST: A Tragically Comedic Security Flaw in MySQL

June 11th, 2012

“In short, if you try to authenticate to a MySQL server affected by this flaw, there is a chance it will accept your password even if the wrong one was supplied. The following one-liner in bash will provide access to an affected MySQL server as the root user account, without actually knowing the password.”

$ for i in `seq 1 1000`; do mysql -u root --password=bad -h 127.0.0.1 2>/dev/null; done
mysql>

The following are confirmed distributions that are vulnerable:

  • Ubuntu Linux 64-bit ( 10.04, 10.10, 11.04, 11.10, 12.04 ) ( via many including @michealc )
  • OpenSuSE 12.1 64-bit MySQL 5.5.23-log ( via @michealc )
  • Debian Unstable 64-bit 5.5.23-2 ( via @derickr )
  • Fedora ( via hexed and confirmed by Red Hat )
  • Arch Linux (unspecified version)

Full details can be found at https://community.rapid7.com/community/metasploit/blog/2012/06/11/cve-2012-2122-a-tragically-comedic-security-flaw-in-mysql

The black vodka MySQL tradition

June 6th, 2012

Many do not need any further introduction to this Monty tradition at MySQL events. For the New York Effective MySQL Meetup group this was a new experience for many that I had the opportunity to share at our recent meeting. In 12 months the group has grown to over 280 members, and now recent attendees have experienced black vodka first hand.

A special thanks to Monty Program AB and Colin Charles for providing the alcohol.


South America Speaking Events

June 5th, 2012

Following my 2 presentations at SouthEast LinuxFest on Friday and Open DB Camp on Sunday in Charlotte, NC, I will then be speaking at the first Latin America MySQL event in Buenos Aires, Argentina later this month. This will include at least six MySQL Alumni and key presentations from MariaDB and Tokutek.

I will then be attending the OTN Tour 2012 event in Cali, Colombia the following week and also a dedicated 2 day MySQL Training Days following.

UTF-8 with MySQL and LAMP

May 23rd, 2012

A recent question on a mailing list was the best practices for UTF-8 and PHP/MySQL. The following are the configurations I used in my multi-language projects.

MySQL UTF-8 Configuration

# my.cnf
[mysqld]
default_character_set = utf8
character_set_client       = utf8
character_set_server       = utf8
[client]
default_character_set = utf8

PHP UTF-8 Configuration

#php.ini
default_charset = "utf-8"

Apache UTF-8 Configuration

#httpd.conf
AddDefaultCharset UTF-8
<VirtualHost>
    AddCharset UTF-8   .htm
</VirtualHost>

HTML file UTF-8 Configuration

 <meta charset="utf-8">

PHP file UTF-8 Configuration

header('Content-type: text/html; charset=UTF-8');

MySQL connection (extra precaution)

SET NAMES utf8;

Shell UTF-8

And last but not least, even editing files in shell can be affected (.e.g UTF-8 data to be inserted into DB from file). Ensure at least

#~/.profile
export LC_CTYPE=en_US.UTF-8
export LANG=en_US.UTF-8

Amateurs – They give us professionals a bad name

April 26th, 2012

Any person with half a brain would see from the error messages below that the MySQL server is not operating optimally, or more specifically the MySQL upgrade has not completely successfully and let users can go happily use the website. It amazing me when web hosting providers tell their paying client that an upgrade has been performed yet they did not have the intelligence to actually look at the error log for confirmation. Got a mysql> prompt, it’s all good. One of the first things I check is the error log.

When will people learn the MySQL error log is a valuable resource both for what it contains, and what it should not contain.

120426 17:36:00 [Note] /usr/libexec/mysqld: Shutdown complete

120426 17:36:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120426 17:36:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120426 17:36:00 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
120426 17:36:00 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120426 17:36:00 InnoDB: The InnoDB memory heap is disabled
120426 17:36:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120426 17:36:00 InnoDB: Compressed tables use zlib 1.2.3
120426 17:36:00 InnoDB: Using Linux native AIO
120426 17:36:00 InnoDB: Initializing buffer pool, size = 128.0M
120426 17:36:00 InnoDB: Completed initialization of buffer pool
120426 17:36:00 InnoDB: highest supported file format is Barracuda.
120426 17:36:00  InnoDB: Waiting for the background threads to start
120426 17:36:01 InnoDB: 1.1.8 started; log sequence number 232577699
120426 17:36:01 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
120426 17:36:01 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
120426 17:36:01 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
120426 17:36:01 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50077, now running 50523. Please use mysql_upgrade to fix this error.
120426 17:36:01 [ERROR] mysql.user has no `Event_priv` column at position 29
120426 17:36:01 [ERROR] Cannot open mysql.event
120426 17:36:01 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
120426 17:36:01 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.23-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp
120426 17:46:01 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
120426 17:46:01 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
120426 17:46:01 [ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50523. Please use mysql_upgrade to fix this error.

Some more light reading at Have you checked your MySQL error log today? and Monitoring MySQL – The error log

I want a mysqldump –ignore-database option

April 18th, 2012

While working with RDS and Google Cloud SQL I have come to realize that excluding the mysql schema from a mysqldump is important. However with many databases, the –all-databases option enables you only to select all or none. There is however an easy solution to exclude one or more databases in mysqldump with this little gem I created.

$ time mysqldump --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/rds2.sql

An you can exclude as many schemas as you want.

I checked the mysqldump –help, there was no option in MySQL 5.1, asked a colleague just to be sure I wasn’t wasting my time, and it took all of 2 minutes to create and test a working solution.

When is a database schema not a database schema?

April 18th, 2012
mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop schema innodb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './innodb/', errno: 17)

This is an additional schema that is included in an AWS RDS installation. You should not put directories in the MySQL data directory.

An excellent conference (5 out of 5 stars)

April 14th, 2012

I wanted to extend thanks as others have also about the excellent annual MySQL Conference, now a Percona Live event. This was easily the best run, attended and energetic event in at least the past 3 years. With over a 1000 attendees a well stocked exhibitors hall (and good involvement in the hall), and good talks; there was just a great community vibe. To Terry, Kortney and all Percona staff involved, well done. The event ran on time, I personally did not see or hear of any issues. The only complaint was from many that wanted to attend multiple talks at the same time, another indication of the quality of speakers for the event.

Thank you to those that attended my two sessions on Explaining the MySQL Explain and MySQL Idiosyncrasies that BITE. Many people thanked me after presentations, along also with people coming up to me to say they appreciated the first book of the Effective MySQL Series. My desires to speak and write are only for the benefit of the MySQL community to hopefully learn and appreciate how to best use MySQL.

It was of course great to see many MySQL alumni, and old friends I have seen since meeting at my first MySQL conference in 2006.

MySQL now has two user conferences (*)

April 5th, 2012

PC World has written a post with this title(*) about the upcoming MySQL Connect conference and references the Percona Live conference and an official Percona comment. As this is not syndicated in Planet MySQL I encourage you to read the full article.

This is the MySQL conference to get technical presentations by the many great Oracle/MySQL technical staff who will not be in attendance at Percona Live. There will also be a strong community presence in speaking at Oracle Connect in September. While Oracle was organizing a dedicated MySQL event in April for the community with all vendors including Percona to replace the conference dropped by long term partner O’Reilly (kudos for many years of great events), Percona decided to go at it without including Oracle, the owners and developers of MySQL. The statement quoted in the PC World article regarding “lack of momentum around the [annual community] event” is clearly inaccurate and not a true representation of actual events.

It is difficult to keep up with all the community events Oracle is now running including multiple OTN MySQL Developer days per month across the US and Europe. I will be speaking at the upcoming OTN NY event in April, the Rocky Mountain Training Day in May, and hopefully the MySQL Innovation day in June. Get the full list at Upcoming MySQL Events.

Indeed MySQL content and presentations have also been represented at Oracle Open World for a number of years. 2011 was a very large turnout and many MySQL presentations. As a senior consultant for MySQL Inc I manned a MySQL booth at OOW exhibition hall back in 2007, prior to both Sun and Oracle acquisitions.

TIMESTAMP data types and CURRENT_TIMESTAMP attribute

March 16th, 2012

In the yet to be released MySQL 5.6.6 DMR, there has been a change to the restriction of just one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP syntax. It is now possible for any TIMESTAMP to have either column defintion.

More information at http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html

SQL_MODE and storage engines

March 9th, 2012

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

  1. Dump all InnoDB tables
  2. Drop all InnoDB tables
  3. Shutdown MySQL
  4. Change the my.cnf to include innodb-file-per-table
  5. Remove the InnoDB ibdata1 tablespace file
  6. Remove the InnoDB transactional log files
  7. Start MySQL
  8. Verify the error log
  9. Create and load new InnoDB tables

However, step 6 was not performed correctly due to a sudo+shell wildcard issue. The result was MySQL started, and tables were subsequently created incorrectly. What should have happened was:

mysql> CREATE TABLE `album` (
    ->   `album_id` int(10) unsigned NOT NULL,
    ->   `artist_id` int(10) unsigned NOT NULL,
    ->   `album_type_id` int(10) unsigned NOT NULL,
    ->   `name` varchar(255) NOT NULL,
    ->   `first_released` year(4) NOT NULL,
    ->   `country_id` smallint(5) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`album_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1286 (42000): Unknown table engine 'InnoDB'

However, because by default MySQL will fallback to the legacy default of MyISAM, no actual error occurred. In order for this to produce an error, an appropriate SQL_MODE is necessary.

mysql> SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION';

A check of the MySQL error log shows why InnoDB was not available.

120309  0:59:36  InnoDB: Starting shutdown...
120309  0:59:40  InnoDB: Shutdown completed; log sequence number 0 1087119693
120309  0:59:40 [Note] /usr/sbin/mysqld: Shutdown complete

120309  1:00:16 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=ip-10-190-238-14-bin' to avoid this problem.
120309  1:00:16 [Note] Plugin 'FEDERATED' is disabled.
120309  1:00:16  InnoDB: Initializing buffer pool, size = 500.0M
120309  1:00:16  InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
120309  1:00:16  InnoDB: Setting file ./ibdata1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: all log files must be created at the same time.
InnoDB: All log files must be created also in database creation.
InnoDB: If you want bigger or smaller log files, shut down the
InnoDB: database and make sure there were no errors in shutdown.
InnoDB: Then delete the existing log files. Edit the .cnf file
InnoDB: and start the database again.
120309  1:00:17 [ERROR] Plugin 'InnoDB' init function returned error.
120309  1:00:17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120309  1:00:17 [Note] Event Scheduler: Loaded 0 events
120309  1:00:17 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

NOTE: This was performed on Ubuntu using the standard distro MySQL version of MySQL 5.1.

As previously mentioned, SQL_MODE may not be perfect, however what features do exist warrant correctly configuration your MySQL environment not to use the default.

More Information.

IOUC Leaders Summit Presentation

March 8th, 2012

At the recent leaders summit of world leaders in Oracle, Java and MySQL user communities I gave a presentation on Why Upgrade to MySQL 5.5

This is a more high level overview presentation, specifically designed for Oracle resources with little to no knowledge about MySQL, however it provides a great management approach to the consideration of using the current MySQL GA version.

I have a more detailed technical presentation from last year on Reasons to Use MySQL 5.5.

Why SQL_MODE is essential even when not perfect

February 16th, 2012

In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that comes out of the database without some SQL_MODE settings.

I would ask two more important questions.

  1. How in the first place can such a critical feature of silent data truncation ever be permitted in MySQL? Who made that decision and why?
  2. When is the owner of MySQL codebase realize this is rather ridiculous and enforce essential minimual data integrity that can be obtain with options including STRICT_ALL_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE and NO_ENGINE_SUBSTITUTION.

References

Colorado MySQL Users Group Presentation

February 16th, 2012

In addition to speaking at RMOUG event in Denver, I also spoke today in Broomfield on “Improving MySQL Performance with Better Indexes”.

This presentation included details on :

  • Effective examples of capture SQL via application logging and TCP/IP analysis necessary for identifying the best candidates. Slow is not always the best query to start with.
  • All the commands necessary to identify why you may need an index, how to create varying types of indexes, and how to confirm there true effectiveness.
  • How the number of table rows and different storage engines can greatly effect the optimization choice and query performance
  • The presentation shows how to determine/create and verify covering indexes for a single table example, a master/child example and a production 13 table join.

You can obtain the slides from Improving MySQL Performance With Better Indexes Presentation.

Event Details

MySQL Security Essentials Presentation

February 15th, 2012

Today at the RMOUG Training Days 2012 event I gave an introduction presentation on MySQL Security Essentials covering the following topics:

  • MySQL Security defaults
  • MySQL Security Improvements
  • OS Security
  • User Privileges
  • Data Integrity
  • Installation Practices
  • Auditing Options
  • Better Security
  • Further References

Download slides for MySQL Security Essentials.

Determining consulting rates

February 13th, 2012

It can be hard sometimes, particularly with startups to determine what to charge. I have tried various models over the years from nothing, to greatly reduced, to full-price. Nothing works well.

As one of the top consultants in MySQL, I kept my rates down as an individual to compete competitively with the 3 or 4 other companies world wide that provide relative services, this in the end hurt my bottom line.

I charge a premium rate that matches my skills, expertise and competitors. I charge that for all customers, large, small, old and new. When the value of my work in performance tuning, disaster management, scalability and architecture is offset by the loss of potential or future business it is not difficult to justify a reasonable rate. I also continue to speak extensively, write and publish materials that provides detailed practical knowledge for organizations and individuals that can invest the time, but not the money.

I am still shocked when large established companies want a discount, just last week for a few hours work a company wanted 33% off.

An extract from “3 Things Entrepreneurs Should Never Depend On When Starting A Company” provides a great re-enforcement about what is appropriate pricing.

Fearful Pricing

When I started my business, I undercharged for my service. I didn’t have the confidence to ask for a decent price, and I thought I had to have the lowest price in order to get business.

What did these practices get me? Low profits and poor cash flow.

In order to survive as a startup—both financially and mentally—it’s crucial that you make sure you’re receiving maximum reward for your maxed out efforts. If you don’t see the true value in your business, how do you expect your clients to do so? Your work is worth it; adjust your prices accordingly.

Read more: Business Insider

Exciting upcoming MySQL events

January 24th, 2012

At the IOUC leaders’ summit in San Francisco this week, key leaders from Oracle, Java and MySQL user groups world wide have been meeting. This has included the key Oracle MySQL resources from the community, marketing and product teams. The Java User Groups and MySQL User Groups have been well represented and there has been very welcoming discussion with the IOUC about how we can become active within the Oracle Community.

There has been key discussions of upcoming and proposed MySQL events including the great outreach by the Oracle MySQL team with existing Open Source conferences this year including Scale, FOSDEM and South East Linuxfest just to name a few.

You can see the upcoming events at http://mysql.com/news-and-events/events/. In February alone there will be events in North Carolina, California, Texas, Frankfurt Germany and Stockholm Sweden. I will also be speaking in Denver at 2 events and Salt Lake City.

There is a much longer list then what is shown here, and we are working on getting the full list more available.

More info by Dave Stokes at SCaLE’s MySQL Day a big hit and Keith Larson More User Groups.

Why is searching the manual so hard

January 14th, 2012

As a consultant I often use the MySQL Reference Manual to provide additional information for clients. I am very happy to recognize the quality of the content in the MySQL documentation, but why is the searching of the manual so, so bad?

While reading the General Security Issues section of the MySQL 5.5 manual, I performed a search for “CREATE USER”. I was not asking for anything abstract, this is an actual SQL command. I was rather horrified to find that the results could not even list the appropriate manual page in the first page of results.

I am not an expert in full-text search, however it does not take a rocket scientist to realize that a SQL keyword, the title of a page, in the language of the current page (English) and the current version of the Manual (5.5) should be an easy result. This is a simple weighted result right? Wrong.



The most important MySQL Reference Manual page

January 5th, 2012

In my opinion, The Server Option and Variable Reference at http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html rates as my most important page. This is a consolidated index that enables a drill down to the Server Command Options, System Variables, Startup and replication specifics, as well as important information on default values and differences between versions including point releases.

However, there is another page not in the actual manual, but at http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar.html which is an Options/Variables reference akin to the Reference Manual, but includes a 5.x version matrix.

Recently I was asked about some options that had to be removed from an upgrade to MySQL 5.5. Some of these were obvious, however not all. This page enabled me to confirm deprecation (as expected), and also point to important reference material.

These options where:

  • default_table_type
  • log_long_format
  • master-connect-retry
  • default-character-set

The use of table_type was a 3.x/4.x relic, replaced with engine, so I was surprised this option was still even valid. The option replaced with default_storage_engine. The page defined this as deprecated in MySQL 5.0
log_long_format is also old, and definitely modified since MySQL 5.1 with the general_log_xxx options. In fact this has been deprecated since 4.1
I have never liked the master-xxx options, in favor of a CHANGE MASTER command and synchronization issues with the master.info file and master-xx options. master-connect-retry and several other options were deprecated in 5.1.17. On a side note, if you look at this option in the MySQL 5.5 Reference Manual you get Obsolete options. The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO … statement (see Section 12.4.2.1, “CHANGE MASTER TO Syntax”).
Finally default-character-set. Initially I thought that is definitely still applicable, however the handy cross reference reminded me, this is also deprecated in MySQL 5.0 and the Reference Manual again stating default-character-set is also deprecated in 5.0 in favor of character-set-server.. The name has simply changed in newer version.

With so many options and as a consultant I work with many different versions each week, I sometimes need a refresher of the changes in the versions of the past 5 years. Definitely my second most important page.

If you have a favorite page, please let me know.

I would also like to say thank you to the Oracle/MySQL Documentation team that do a great job in providing an excellent resource to an open source product. We would all do well to appreciate this in comparison to other open source documentation in companion technologies and related tools. With every new release of a MySQL product you don’t realize that somebody reviewed, tested and wrote about features without receive the limelight.

Binary Log Replayer

December 22nd, 2011

When using the replication slave stream, or mysql command line client and mysqlbinlog output from a binary/relay log, all statements are executed in a single thread as quickly as possible.

I am seeking a tool to simulate the replay of the binary/relay log for a benchmark at a pace that is more representative to original statements. For a simple example, if the Binary Log has 3 transactions in the first second, 2 transactions in the second second, and 5 transactions in the third second, I am wanting to simulate the replay to take roughly 3 seconds, not as fast as possible (which would be sub-second). The tool should try to wait the remainder of a second before processing SQL statements in the incoming stream.

Does anybody know of a tool that currently provides this type of functionality? Any input appreciated before I create my own.

Speaking in Denver

November 22nd, 2011

Following a heavy schedule in the last month speaking in Tokyo, Beijing, Manila and Auckland it is nice to be on home soil for upcoming speaking. I will be in Denver, Colorado for RMOUG 2012 from February 14-16, 2012 where I will be speaking about the essentials of MySQL security.

I hope to also organize another presentation in the area for the local MySQL users group. More to follow.

And a friendly reminder, the annual MySQL conference is on again, same place (Santa Clara), same dates (April 10-12), same great speakers (too many to say), just a new primary sponsor. Why not submit a paper to present. Call for papers is still open, closing on December 5. More information at Percona Live MySQL Conference & Expo 2012.

Do you have a MySQL horror story to share?

October 11th, 2011

I am looking for a few more unique examples to add to the final chapter of my upcoming book on MySQL Backup & Recovery. If you would like to share your fun experience, receive a mention and a free copy please let me know via comment. If you would like to share but not have your comment published, please note at top of your feedback.

Thanks for helping to contribute to a detailed list of what could go wrong and how to be prepared for a MySQL disaster.

NoSQL from a RDBMS company

October 4th, 2011

Oracle has announced an open source product for the NoSQL space, the Oracle NoSQL Database. Unlike other popular products including Redis, MongoDB, Cassandra, Voldermort and many others, Oracle has set a benchmark on the features that are truly necessary for highly available data systems.

Many products in the NoSQL space have told you that consistency is not needed, eventual consistency is good enough, that transactions are not performant enough to include as a feature. No standards exists, there is no common interface for communication, or key features that products aim to meet or better. With this product, features including transactions, replicated data and failover which are built in, are features other open source NoSQL products will need to match.

Oracle NoSQL Database is a key value store, supporting a major/minor key for co-locating regularly accessed information for more consistent data retrieval. The API (built in Java) supports GET, PUT, and DEL operators. The system is designed to not have a single point of failure, and to support a node failure without impact. The replication factor is reported to enable up to 7 copies of information, which would be a feature to support cross data center management. The database driver is latency aware, so this can support load balancing operations for optimal performance.

I am excited to hear about this and looking forward to evaluating the software. I will be watching more closely how the integration of MySQL and Oracle NoSQL can be an offering for startups and Web 2.0

Oracle Open World Presentations

October 4th, 2011

At Oracle Open World 2011 there has been a large number of MySQL presentations. You can download the slides of my two presentations at Explaining the MySQL Explain and Improving Performance with Better Indexes.

You can find additional supporting information for these presentations in the Effective MySQL: Optimizing SQL Statements book.

The Effective MySQL Book Series

October 3rd, 2011

Effective MySQL: Optimizing SQL StatementsAnnounced on Sunday at Oracle Open World 2011 is the release of the Effective MySQL book series starting with the “Optimizing SQL Statements” title. The goal of the Effective MySQL series is a highly practical, concise and topic specific reference providing applicable knowledge to use on each page. A feedback comment provided today was “no fluff” which is great comment to re-enforce the practical nature of the series.

Details on the Effective MySQL Optimizing SQL Statements page include a sample chapter, code downloads and purchase links for print and e-books at Amazon, McGraw-Hill and Barnes & Noble.

Visualizing reqstat

September 28th, 2011

The reqstat tool was written to provide a vmstat like output of total web requests happening in real time. This really lightweight monitoring leverages memcached and has a trivial impact for immediate benefit.

  $ ./reqstat 5 5
  epoch,time,rps,avg_req,last,%comp,---,threshold,exceed,last_excess
  1307723122,162522,25,125.92,75.25,48,---,150,9,175.55
  1307723127,162527,24,107.33,6.97,48,---,150,6,188.45
  1307723132,162532,25,118.39,97.63,50,---,150,8,151.37
  1307723137,162537,22,120.51,88.62,42,---,150,5,168.56
  1307723142,162542,26,106.62,6.12,51,---,150,6,167.81

While this is useful, I can see 22-26 requests per second, averaging 106-120 ms, visualizing this gives more information immediately available like:

It is easy to look at an average and lose site of the larger picture. What are the outliers, how many are there? Visualizing of larger samples (a later example which will show 10,000 rps across multiple servers) shows that the granularity is also critical.

This graphic is produced with Flot. A very simply javascript library. You can also use gnuplot, an example script is included in github.

This output is the result of benchmarking, this being generated from reqstat output with a script in my monitor git repository.