Events/2010/Proposed Sessions

MySQL/InnoDB. Talk will cover how to use it scenarios, xtradb internals, tips & tricks and live demonstration

Talk covering all important features, use cases and performance/usability gains.

Wir zeigen auf, wie die Geschichte von MySQL bisher verlaufen ist und welche wichtigen Ereignisse die Entwicklung der Firma beeinflusst haben. Dann gehen wir über zur aktuellen Situation berichtet, welches Branches und Forks sich bisher ergeben haben und wohin sich diese entwickeln. Aus diesen Informationen sollten Entscheidungsträger in der Lage sein, Ihre Schlüsse zu ziehen und Entscheidungen bezüglich Ihrer Datenbank-Backends für die nahe und mittlere Zukunft zu treffen.

Freie Lösungen für das überwachen von MySQL und aufzeichnen von Performance-Graphen gibt es einige. Doch überzeugen diese nicht wirklich. Als Alternative bietet sich der MySQL Enterprise Monitor an. Der kostet aber eine Stange Geld (oder besser, der ihn beinhaltende Support-Vertrag). Mit Zabbix bietet sich eine elegante Open-Source Lösung für die Überwachung aller Arten von Geräten, Applikationen und Diensten. In diesem Vortrag zeigen wir auf, wie die Architektur von Zabbix aussieht und wie wir die Datenbank-Überwachung gelöst haben. Am Ende des Vortrags sollten die Zuhörer in der Lage sein, nachdem sie Zabbix installiert haben, die Datenbank-Überwachung zuzuschalten und Ihre eigenen MySQL-Datenbanken zu überwachen. Sofern noch Zeit bleibt, stellen wir noch die Module für XtraDB/InnoDB, PBXT und MySQL Cluster vor. Die Installation von Zabbix ist NICHT Teil des Vortrags.

(99.99) availability for the mysql backend behind a website. It used mysql replication to set up a multi-master environment, MMM to manage it and LVS for mysql backend loadbalancing. This architecture not only provided high availability, but also permitted us to change the schema, upgrade MySQL versions, or even replace hardware without any downtime. This presentation explains how this solution worked. One of the sites where this solution was deployed was a credit card processing interface for an alexa top100 site. The other one is a famous alexa top500 page (ustream.tv) with huge traffic. At first I would like to give a short overview of how these systems built up, mention the traffic and the goals that each system had. The payment site needed to be as reliable as possible with the highest possible uptime. The data consistency was very important, but downtime can't be tolerated below the 4 nines border. I will cover the scenarios we had to keep the data consistent on failure. On the other site at ustream, 10k visitors were online at all times, every single minute. During the peaks, more than 700k visitors were online at the same time. But the greatest challenge was when some unique live event happened, such as Michael Jackson's funeral, when the smallest mistake would make disappointed visitors who would look for another broadcaster immediately. I'll also discuss the following:

• The requirements of each service mentioned in the title
• The limitations of mysql
• The required configuration of LVS
• Issues around MMM
• Monitoring and failover management and how we ensured data consistency
• The requirements on the application side
• What support the DBAs require from the developers

To attend, you should know the mysql replication, routing and tcp basics and some experience with web based applications would be useful also. This talk won't mention installation of software or configuration of services. It will focus on the principles, rules, and main goals of this solution, and finally the workarounds it requires.

MySQL replication is straightforward to set up but also easy to break. If inconsistencies are introduced, the master and its slaves start being desynchronized. And as MySQL does not offer any tool to monitor the situation, most often you will not catch the problem, which can lead to strange behaviors and possibly make your application crash. Maatkit, a set of administration tools for MySQL, has a neat solution with 2 nice scripts: mk-table-checksum, which is a tool to detect inconsistencies between a master and its slaves and mk-table-sync, which can correct automatically these problems detected with mk-table-checksum. This session will show you first how to use both tools depending on your replication setup (master-slaves or master-master) and then how to get the most from the multiple options that are available.

It translates SQL-queries from the dialect T-SQL (used in the database MS SQL Server) to pl/pqSQL (database Postgre SQL?). SELTA@Etersoft works in Windows and in Linux as well.

The MySQL Server distribution includes several command-line tools for administrative purposes.

However, the included tools leave a lot to be desired – many common DBA tasks are more complicated to perform than necessary, others can’t be accomplished at all.

Fortunately, these gaps have nicely been filled by other Open Source projects in the meanwhile, providing a wide range of functionality to ease a MySQL DBA’s life.

In this session, Lenz will give an introduction and overview to the most popular and commonly used tools and tool collections that make performing MySQL administration tasks on Linux and other Unix systems a breeze.

The attendees will learn which tools are suitable to better tackle duties like user administration, setting up and administering replication setups, performing backups and security audits.

Among others, the session will cover the following tools and their most useful components:

• maatkit
• openark-kit
• kontrollkit
• securich
• xtrabackup
• mylvmbackup

Learn the basics, the troubleshooting, the advanced tips and tricks.

MySQL is the perfect companion for most startups. The usual scenario is a database server coupled with a web server. When traffic grows, additional web servers are easily deployed through a load balancer. Th database server can easily serve more than one web server, but it becomes the bottleneck of the whole system once it reaches its limits. To make the database scale easily, MySQL provides a built-in replication feature, which is easy to deploy, although it requires some DBA care. This session explains how to set up a replication, and how to design and tune the system for maximum performance.

Learn the basics of the command line power. Using standard tools and a bit of creativity, you can put together results that few GUIs can achieve. And, most important, you get the result immediately. No need to wait till the developers implement the features you need. With the command line, you are the master and the owner of your system. There are many GUI applications to deal with databases and with MySQL in particular. But for die hard command line enthusiasts, who grew up with Linux, the shell prompt is still prince. You would be surprised at the vast amount of operations that you can do from the command line. Running commands on several databases at once, filtering users, getting detailed metadata, transferring data from the operating system to a database and the other way around, creating scripts from database data, creating SQL commands from database data. That, and more, you can learn by attending this session. Learn the art of the command line as a shortcut to quick and efficient administration, and as help for demanding developers.

This technology is GA for almost two years, still surprisingly few users know about it, even though it can help to advance their applications in the most drastic way.

The talk will look at MySQL plugins from the end user point of view. What are they, what can they do, how can you use them, what plugins are available - these are the topics of this talk. The talk will look at MySQL plugins from the end user point of view.

• What are "MySQL Plugins" ? How they work ?
• How to use them ?
• What do they allow to do ?
• What plugins exist now ?
• What's cooking ?

These are just few questions that will be answered during the talk.

The session covers topics:

• InnoDB page format
• Internal system tables SYS_INDEXES and SYS_TABLES
• InnoDB Primary and Secondary keys
• Typical failure scenarios
• Dealing with corrupted InnoDB tablespace
• Recovery from a raw partition

The session is illustrated with examples using Percona in-house InnoDB Recovery Tool.

• High Availability (no lost transactions)
• Good performance (write scalability)
• Transparency (very close to native InnoDB behavior)

Galera replication is based on new "certification based" replication method. This optimistic replication model together with efficient group communication system and usage of MySQL row base replication events makes the MySQL/Galera cluster of very high performance.

The presentation will give an overview of Galera replication model and shows the performance gain by a number of benchmarks results.

MySQL/Galera cluster GA release (version 0.7) was published in December 2009. We have worked with Galera cluster installations and evaluations during spring time 2010, and are currently finalizing release 0.8, which will bring many performance optimizations. The 0.8 release makes also cluster management a lot easier.

The tool is currently being used in the MySQL and Drizzle projects, and will be introduced in MariaDB as well. Given its expressive power and practical effectiveness, it is hoped that other MySQL-derived or NoSQL projects will also adopt it. The Random Query Generator takes a SQL grammar file and uses it to generate queries that are then executed against one or more database servers. Various facilities can be used to validate the outcome of each query and the general operation of the server, including comparison testing between different products, result set validation via Perl code, and automatic detection of common situations such as crashes, deadlocks and replication failures.

The tool is GA-quality and production-ready and has been used extensively in MySQL over the last two years to test various aspects of the server and its storage engines, especially the Optimizer as well as transactional consistency and locking correctness.

The use of a SQL grammar instead of individual, hard-coded SQL queries allows the RQG to cover more ground for the same amount of effort as compared to MTR (mysqltest). Further, the RQG comes with a set of ready-made tests, covering the Optimizer, DDL, replication, transactional consistency and recovery.

The talk will also describe some of the more general issues encountered when testing database systems and will present specific solutions and advice to both developers and testers.

Every problem will be examined in parts: description; code; explanation what is not clear and why MySQL behaves in such a way; solution. Examples are made from real cases and were groupped by particular topics: DDL, DML, Types, Privileges, Charsets, Upgrade, Client and some others. They were slightly rewritten to be clear for understanding and to hide private data.

Maintenance tasks can take a lot more time than expected with large applications, leading to downtime or degraded service. For instance, adding an index with ALTER TABLE seems very easy, but if the table has 50GB of data, the command can run for hours or days and will lock the whole table. In this talk I will show you tips and useful tools that will help you to execute your maintenance jobs efficiently while minimizing the impact on your application.

I will cover the following topics:

• Changing your schema (columns and/or indexes): ALTER TABLE and alternatives
• Defragmenting your data
• Purging and archiving old data with and without partitioning
• Resyncing a replication slave

This talk will focus on the new features currently developed, and gives an outlook on the roadmap until the end of 2010:

• SQL parser built with Lemon and Quex
• Dynamic loading of user defined index functions
• Sharding and Partitioning
• BlackRay as a MySQL Storage engine
• Building a query cache with BlackRay