Even though I have come late to the party of professional development, relatively speaking, I am acutely aware of the conflict that seems to pervade the developer-DBA relationship. This is what I gather about why this is: DBAs used to be paid better that developers, and often this was because they were able to reduce the overall license and hardware costs of large database installations. Both the size and proprietary nature of databases made them incredibly expensive, so paying an individual gobs of money to make sure they ran efficiently and that the data was preserved was worth it.

Several trends have changed the playing field. The first is the arrival of small, commodity server hardware that makes mainframes or large servers unnecessary, and thus the cost is pushed down dramatically for most installations, while salaries for trained database professionals remained the same.

The second is the explosion of databases that do not (in …

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.

Do you want to closely follow what MySQL is doing around the globe? We've created a Lanyrd guide just for that. It's curated by the MySQL Community, Marketing, Presales and Product Management teams and with some other contributions too.
Tomorrow we'll host the MySQL Innovation Day (in streaming too) and MySQL Connect is also quickly approaching. If you'd like showcase your products and services to the MySQL Community you can take advantage of the various sponsorship opportunities.

Stay tuned for the latest and greatest updates from the MySQL team at Oracle and from our vibrant community!
…

We've recently seen some great announcements of MySQL Cluster delivering amazing results for both selects and updates. The posts (see related articles below) are full of juicy technical details and proofs, but today I'd like to change the perspective a bit. Let's compare those figures with real-world data and imagine what could be done. Please note that I'm not using any scientific method here, just dreaming about the unlimited opportunities offered by MySQL Cluster today.

Cluster can deliver 1B+ selects per minute with 8 nodes …

New versions of MySQL are always interesting to try out. Often they have features which I may have asked for myself so it’s satisfying to see them eventually appear on a system I use. Often other new features make life easier for the DBA. Finally we hope overall performance will improve and managing the server(s) will be come easier.

So I had a system which needs to make heavy writes, and performance was a problem, even when writing to SSDs. Checkpointing seemed to be the big issue and the ib_logfile size in MySQL 5.5 is limited to 4 GB. That seems a lot, but once MySQL starts to fill these files (and this happens at ~70% of the total I believe),  checkpointing kicks in heavily, and slows things down.  So the main reason for trying out MySQL 5.6 was to see how things performed with larger ib_logfiles. (Yes, MariaDB 5.5 can do this too.)

Things improved a lot for my specific workload which was great news, but one thing …

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: …

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.

Over the last few months, on different systems I have been modifying the ib_log_files and their sizes, increasing them due to the increase in load they have been experiencing. These files are used to contain information which has been committed, but not yet written to the ibdata files or the individual .ibd files if using innodb_file_per_table.  And these files are used on recovery if the server shuts down unexpectedly during recovery processing to get the system back into a consistent state again.

The checkpointing in MySQL is well documented, but I was surprised just how much difference the throughput can change for heavy write applications if the sizes of these files is changed.  It may seem obvious but given how disruptive it can be to shut down a system, change these values and start it up again, this process is not done frequently.  These files help improve performance as changes are written linearly into the file, …

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

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 …