MySQL Newsletter February 2010
Articles in this newsletter:
- MySQL Conference and Expo: Register Now - Save $250
- MySQL Conference and Expo: 15 Half-Day Tutorials
- MySQL Download and Documentation Links
- PlanetMySQL Blog: Reorganizing the documentation
- Upcoming MySQL University Sessions
- MySQL Community Survey
- New Release of MySQL Community Server 5.1.44 (GA)
- New Release of MySQL Cluster 7.1.1 (Beta)
- New Release of MySQL Workbench 5.2.16 (Beta)
- New Release of MySQL Connector/J 5.1.11 (GA)
- PlanetMySQL Blog Posts
- PlanetMySQL Blog: Performance schema overview
- PlanetMySQL Blog: Index search time depends on the value being searched
- PlanetMySQL Blog: Aggregates: subqueries vs. GROUP BY
- PlanetMySQL Blog: innodb_file_per_table, shrinking table spaces and the data dictionary
- PlanetMySQL Blog: MySQL Replicant: Architecture
- PlanetMySQL Blog: Configuring the InnoDB Plugin (1.0.6) in MySQL 5.1.43
- PlanetMySQL Blog: Further Thoughts on MySQL Upgrades
- PlanetMySQL Blog: What is CHECK TABLE doing with InnoDB tables?
- PlanetMySQL Blog: mysql's --xml and some XSLT
- PlanetMySQL Blog: MySQL Cluster Uses
- PlanetMySQL Blog: MySQL Cluster Start Phases
- PlanetMySQL Blog: How MySQL Cluster BLOBs work
- PlanerMySQL Blog: How to Prevent Global Check Point Stops in MySQL Cluster
- PlanetMySQL Blog: Gearman meets MySQL Cluster (NDBAPI)
- PlanetMySQL Blog: MySQL Cluster Disk Data Tables
- Live Webinar: MySQL Cluster Connector/Java: Technology Introduction & Getting Started, Part 2 (March 3)
- Live Webinar: MySQL Cluster on Windows - EMEA (March 5)
- Live Webinar: Performance Part 2: How to Boost Performance with MySQL 5.1 and the new InnoDB Plugin (March 9)
- Live Webinar: Migrating from SQL Server to MySQL EMEA (March 11)
- Windows and MySQL - Part 12: Advanced MySQL Replication on Windows (March 16)
- Achieving Peak Application Performance with MySQL Embedded Server (March 17)
- Running MySQL Securely on Windows - EMEA (March 18)
The MySQL Conference and Expo is a deep technical conference designed to take your skills and know-how to the next level through expert-led sessions, in-depth tutorials, and face-to-face conversations with open source gurus and developers.
Take advantage of this rare opportunity to get hands-on knowledge from MySQL experts on numerous topics. Tutorials are half day presentations that give you the opportunity to learn a topic in-depth, in a collaborative environment. Tutorials include:
- The Replication Tutorial
- Diagnosing and Fixing MySQL Performance Problems
- Using Partitioning in MySQL 5.1 and 5.5
- MySQL Cluster Tutorial
- Scaling Applications with Caching, Sharding and Replication
The most recent MySQL downloads and documentation can be found at the following URLs:
Martin 'MC' Brown
Those of you that know the documentation well will be aware of the old page we used to have for the MySQL documentation. It was huge, and over the years we'd done a number of things to try and improve the layout and make it easier to find what you wanted.
MySQL University is a free educational online program for engineers and developers who are interested in MySQL development and internals. MySQL University sessions are open to anyone, not limited to Sun employees. Sessions are recorded with slides and audio, so if you can't attend the live session you can review the recording anytime after the session.
Please take a few moments to complete our annual user survey. This is an opportunity to tell us how you are using MySQL and to influence the MySQL roadmap.
MySQL Community Server 5.1.44, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.44 is recommended for use on production systems.
A new version of MySQL Cluster 7.1 beta has been released.
The MySQL Workbench Team has published a new beta release of Version 5.2.16.
MySQL Connector/J 5.1.11, a maintenance release of the production 5.1 branch has been released. Connector/J is the Type-IV pure-Java JDBC driver for MySQL. Version 5.1.11 is suitable for use with any MySQL version including MySQL-5.0, MySQL-5.1 or MySQL-5.5.
The following blog posts are from PlanetMySQL. PlanetMySQL is an aggregation of blogs and news from MySQL developers, users and employees. It is an excellent source of all things MySQL, including technical tips and best practices.
This paper is an introduction to the new 'performance schema' feature, which will be part of the upcoming MySQL 5.5 release. Covering in details every part of the performance schema would require much, much more than a simple article. The pace of this teaser is voluntarily fast, to have a quick overview of the new landscape, and help users already familiar with MySQL to understand by examples what the performance schema provides.
Answering questions asked on the site. I have a table which stores track titles in a VARCHAR(200) field. The field is indexed, but searching for titles beginning with a letter Z is noticeably slower than for those beginning with A, and the closer the initial letter is to Z, the slower is the query.
I have a table users and there is a field invited_by_id showing user id of the person who invited this user. I need to make a MySQL query returning rows with all the fields from users plus a invites_count field showing how many people were invited by each user.
INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.
In the previous post I described the first steps of a Python library for controlling the replication of large installations. The intention of the library is to provide a uniform interface to such installations and that will allow procedures for handling various situations to be written in a uniform language.
Configuring the InnoDB Plugin (1.0.6) is just as easy in the MySQL 5.1.43 release. There are a few subtle changes in the new release.
I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.
Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).
Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.
MySQL Cluster can be used as a general purpose transactional storage engine, but if you convert all your InnoDB tables to it and connect your application straight to it you may not see the performance you were hoping for.
When MySQL Cluster data nodes start they need to go through a process of determining roles in the cluster, copying the data back into RAM and synchronising everything up. This can take longer than expected and the process is not always very verbose. So in this post I will outline a simplified version of what MySQL Cluster is doing in each start phase so that you can see why it can take time.
If there is one thing that confuses people about tables in MySQL Cluster (including me at times) it is BLOB/TEXT columns. When NDB was originally created it was not designed to handle BLOB data, so the handling of BLOB data was difficult to implement and is sometimes not exactly what users expect.
One of the most common errors we come across whilst supporting MySQL Cluster is an error commonly referred to as 'GCP stop'. These errors will occur most frequently in cluster setups which have high activity and more often than not use disk data. So lets look into what these are, why they happen and how to prevent them.
After a discussion with my colleague Stephane Varoqui we decided to see how Gearman and the NDBAPI could be used together.
MySQL Cluster is well known as an in-memory database which can be restrictive (a server typically has a RAM limit) so it may surprise some to learn that you can also store MySQL Cluster data on disk. Disk Data Tables have been in MySQL Cluster for quite a while, the first GA release with them included was 6.2. They do have caveats which I will describe here, many of which already have plans to be improved in future versions.
Wednesday, March 03, 2010
Designed for Java developers, the MySQL Cluster Connector for Java implements an easy-to-use and high performance native Java interface and OpenJPA plug-in that maps Java classes to tables stored in the high availability, real-time MySQL Cluster database. Tune into Part 2 of this 2-part webinar series where Sun's senior engineering staff will discuss how to integrate the MySQL Cluster Connector for Java into existing applications.
Friday March 5, 10:00am CET
In this presentation we will explore the benefits of leveraging MySQL Cluster on Windows. We will also cover step by step, how to get started with installing, configuring, and running MySQL Cluster on Windows. Finally, a review of MySQL Cluster's architecture will be covered. If you are interested in learning how to leverage MySQL Cluster on Windows, this webinar is for you.
Tuesday March 9, 10:00am PST
Looking for immediate performance and scalability gains for your MySQL application? Then join us for Part 2 of our "Boosting Performance" webinar series where MySQL Professional Services experts will walk you through best practices for achieving performance and scalability improvements using MySQL 5.1 and the new InnoDB Plugin. We will cover which plugin features to enable, and review specific use cases, tuning, and benchmarking best practices to help you maximize results.
Thursday, March 11, 15:00 CET
In this presentation we will explore the benefits of migrating from Microsoft SQL Server to MySQL. We will also cover migration best practices, as well as, an overview of migration tools, scripts, datatype mapping and functional equivalents. If you are interested in learning how to migrate from SQL Server to MySQL, this webinar is for you.
Tuesday, March 16, 10:00am PST
In this presentation we will build of the previous presentation and look at more advanced MySQL Replication techniques. We will cover master-master configurations, semi-synchronous replication, fail-over and resynchronization. If you are interested in learning about advanced MySQL Replication on Windows, this webinar is for you.
Wednesday, March 17, 10:00am PST
MySQL expert and trainer, Sarah Sproehnle, will discuss the major MySQL design decisions that can affect the performance of your application and with it, your customers' experience and satisfaction. Sarah will show you how to achieve top performance with MySQL Embedded Server, covering the following topics:
- Choose the right storage engine(s)
- Identify critical performance-related server settings
- Understand and properly use indexes
- Using EXPLAIN to verify query execution plans
- How to identify performance problems
Thursday, March 18, 2010, 15:00 CET
In this presentation we will demonstrate how to install and configure MySQL to run securely on Windows. We will explain how MySQL's internal security model, authentication and privileges model works. We will also look at how to secure network access to MySQL and look how to thwart common attacks on MySQL. If you are interested in learning how to run MySQL securely on Windows, this webinar is for you.