Events/2009/Proposed Sessions

MySQL is a popular open source relational database system (RDBMS) and widely used as the storage backend for dynamic web sites and applications. In many areas it has become a mission-critical component of the software stack, where downtime is not an option. This talk will give an overview about different solutions and technologies available to make a MySQL Server system highly available. The session will start with a brief introduction to High Availability (HA), the terminology and general concepts involved. Afterwards, several MySQL HA solutions will be introduced and explained, e.g. replication (and possible topologies), Linux-Heartbeat, block device replication (DRBD), Shared storage/SAN, MySQL Cluster, MySQL Proxy, Sun Open HA Cluster and Red Hat Cluster Suite.

Der Ingres Design Advisor ist der erste Schritt zum autonomen Datenbank Tuning in Ingres. Der integrierte Performance Monitor erlaubt die Überwachung und Analyse des Systems und gibt Empfehlungen für das physische Datenbank Design.

Mit dem Ingres Design Advisor wird das DBMS Ingres um die Möglichkeit erweitert, den Status und die Performance des Systems in Echtzeit und über einen langen Zeitraum zu überwachen und diese Daten automatisiert zur Leistungsverbesserung zu nutzen. Der Advisor untersucht den Workload eines Systems, macht Vorschläge zur Index-Erstellung und zeigt grafisch aktuelle und kommende Engpässe der DBMS Konfiguration. Dieser Vortrag geht auf die Unterschiede und Gemeinsamkeiten zu existierenden Lösungen anderer Anbieter ein und erklärt, warum autonomes Datenbank Tuning ein so wichtiges Thema für moderne Datenbank Management Systeme ist.

Miteinander verglichen werden sowohl (objekt)relationale wie auch objektorientierte und dateibasierte Dateibanken. Die Betrachtung erfolgt aus dem Gesichtspunkt eines Anwenders (Entscheider), der für ein Projekt eine neue Datenbank auswählt und dabei Kriterien wie Anwendungszweck, Integrationsfähigkeit, Mehrbenutzerbetrieb, Speichern von Objekten oder binären Daten, Replikation und Partitionierung, Transaktionssicherheit oder auch die Lizenz und mögliche Kosten zu überprüfen hat.

This workshop will go through all the details of the modified mysqltuner, and take suggestions for further improvements.

Testing single database instances is quite easy. Testing replicated, or other distributed systems across several boxes is a pain. Users of MySQL Sandbox know that they can install one single or complex instance in one box, but they haven't been able, so far, to use a replication system across several hosts. Enters Gearman, a system that deals with jobs on distributed hosts, making the task easy and approachable by most anyone. In this workshop, we will see how to set three separate boxes with Gearman and Sandbox, and then use these servers to install several kinds of replicated systems, with extreme ease.

The Spider storage engine, a plugin for MySQL 5.1 and later, is an extension of partitioning. Using this engine, the user can deal transparently with multiple backends in the server layer. This means that the data is accessible from any application without code changes. This lecture will briefly introduce MySQL partitioning, and then shows how to create and use the Spider engine, with some practical examples. This talk covers the latest version of the Spider engine, which includes a condition pushdown feature that increases performance significantly

This topic will focus on things such as:

• Do not trust anything that comes from outside the Firewall
• Do use of stored procedures and views to isolate your data from the Application
• Isolate your data from the application user
• Make sure you have proper Credentials Management
• Do not send data in plain text (ever!)
• Do not store passwords anywhere
• Make sure your Application is Auditable
• Make sure your Application is Recoverable

It will also go over basics of PCI compliance as well as general programming errors and typical mistakes that is often made, compromising security.

By use of examples this session provides explanation of what the various tools in maatkit does, and how to best utilize them for optimal win. I will also, in this session, give usage information, drawbacks as well as tips and tricks such as how to be able to turn around the knowledge you get from the tools into production.

I'll not cover all tools, but I will go with the most popular ones and also throw in some of the less used tools, such as mk-find and mk-slave-find, to further broaden the knowledge of the crowd when it comes to Maatkit.

Do you already have a cluster but need to add more data nodes? Don't want to take the cluster offline to do this? Then come and listen and ask questions, I will explain how you can accomplish this without downtime. Need replication? No problems. Two channels? No problem.

This session will cover:

• Installation of NDB
• Configuration of NDB
• Terms used and topography of how a cluster is designed
• Best use practices, security, backup and restore
• Scalability
• New features for version 7
• Limitations and drawbacks
• Strengths
• Possible use scenarios, as well as performance numbers compared to InnoDB from not only a availability standpoint, but also a performance standpoint, what can one expect from a cluster versus the current setup?
• Replication, cluster to cluster, standalone to cluster, cluster to standalone, several replication channels.

storage engines developed with spindles and rpms in mind. Add here cheap RAM, multi-core CPUs - are you sure your storage engine effectively utilizes all this?

Learn how the PBXT transactional storage engine for MySQL meets these challenges.

The discussion will cover engine's general architecture, specific design solutions, engine's transactional and multi-versioning properties as well as recently added features and improvements such as concurrent index updates and in-memory tables.

Galera replication gives:

• good performance even with write intensive work loads
• high availability (due to synchronous replication)
• transparency for client connections

Galera replication uses certification based replication method, which gives good performance even with write intensive work loads. In the presentation, we will show the concept and architecture of MySQL/Galera clustering solution. Galera integration with DBMS engines happens through wsrep API (write set replication API) and we will describe the state and goals of wsrep and Galera projects.

We have benchmarked MySQL/Galera with several benchmarks and results show remarkable performance gain. The presentation shows results from Sysbench, DBT2 benchmark runs. We have evaluated MySQL/Galera under Drupal and eZ Publish CMS systems and will show results from jmeter benchmark against Drupal cluster.

Es gibt viele Unternehmen, die PostgreSQL einsetzen. Dieser Vortrag stellt einige Beispiele vor und zeigt auf, warum diese Tatsache trotzdem noch nicht weit verbreitet ist. Zusätzlich werden einige der angeblich vorhandenen Schwächen für den Produktionseinsatz analysiert und widerlegt.

This session will help you understand the principles of covering indexes, their benefits and the situations where you cannot use them.

The basic use of an index is to retrieve rows as efficiently as possible. Generally speaking, finding a row with an index involves two steps internally: first, you query the index to get pointers to data and then you use the pointers to retrieve data. Indexes hold values, so why not use directly these values ? As indexes are smaller than data and are more likely to fit in memory, you can expect huge improvements from this optimization.

How can you check that the MySQL server only retrieves values from the index ? Is it possible to rewrite a query so that it uses a covering index ? And are you sure that using a covering index will always help get better performance ? To answer these questions we will look at the information of EXPLAIN. Then we will explain some rewriting techniques (for instance with queries with LIKE or dates) and show some benchmarks with good and bad use of covering indexes.

Another important use of covering indexes is sorting. Basically, even if you use an index to sort rows, which can be very fast, you still have to retrieve data from the rows, which can be expensive. Once again covering indexes can help you much.

Summary of the session:

• Indexing basics : features and goals of indexes
• Index types : hash, b-tree
• Index and data layouts for MyISAM and InnoDB
• Accessing data on disk, on RAM
• Covering indexes : definition, why and when using it
• Situations where you cannot use a covering index
• Case studies

How often do you wonder "What privileges does user xyz have globally (I.E. on databases, tables and stored procs)?", "Do all my users have a decent password?" and "When was it changed last?", whilst hoping they weren't cycling through the usual three passwords just because they were lazy to remember a new one, or "Who has access to table 'passwords' in database 'livedb'". Maybe you have occasionally granted full access to all tables in a database just because it's a pain in the back to grant on tables one by one, or wondered about the mess your users and their privileges were in. Did you ever wish you could clone user 'abc'@'localhost' out of 'xyz'@'%.my.domain.com'? then, perhaps, Securich (http://www.securich.com) can come to your rescue. It can easily tackle the above and much more. Securich also enables the long absent roles functionality in MySQL, permits dynamic updating of roles and immediate rollout of the changes to each user.

Securich is very simple to install and update using a bash script which includes error catching and rollback (should there be a problem). Securich is also easy to remove if required, a single command "drop securich" does the trick and doesn't change any of the rights granted to users through it.

Features:

• Roles / groups
• Password history / updating
• User cloning
• Dynamic roles
• Secured user (avoiding password less accounts) Password guidelines The possibility to grant access to all tables in a database but one in two commands *Partial privilege history User blocking / unblocking Total isolation of users if need be|- valign="top"

memcached is a key ingredient in any major Web 2.0 site to offload the database by handling the read-load. Up till now you had to fork memcached if you wanted to use a different backend to store the items (e.g., Tugela cache use BDB). We are currently working on adapting something similar to the MySQL storage engine design, by creating a plugable engine interface in Memcached.

The ideal memcached server is the one with the lowest price per GB of memory. The introduction of Flash memory and SSD in commodity hardware, opens new possibilities. I have been experimenting with a hybrid memcached engine, utilizing RAM and Flash memory. This engine would be ideal where you need fast access time for the frequently most used items (they are stored in RAM), but can accept slightly higher latency for other items (stored in flash). This talk will cover the following topics:

• What the storage interface is
• Why we created it
• What it may be used for
• How this allows us to create a hybrid caching model by utilizing RAM and Flash memory.

In this talk we would like to explain our motivations for designing and building BlackRay, and then elaborate on the architecture of the internals of the data engine. The index structures inside the data engine are designed for low memory consumption, and the ability to quickly index large amounts of data. A total of five index layers, also called index perspectives, is required to fulfill the functions required for our search algorithms. Our smart combination of binary- and permuterm based search offers significant performance benefits over many traditional tree- and trie-based searches. Finally, searching for a token combination within a single table column only result in linear complexity, rather than exponential as in most typical index structures. The option to additionally compress the index further reduce the amount of memory used during operation.

Additionally we will explain the overall data engine setup, including the object-oriented search API (Java, C++, Pyhton, C#), as well as the progress on the PostgreSQL compatible socket interface for JDBC and ODBC. Further topics covered in our brief introduction are Remora, the web-based management interface, and the fail-over and recovery mechanisms of BlackRay, including the options available to maintain a history of your data. (project home)

This talk introduces MongoDB by explaining how it compares to traditional relational databases as well as some other non-relational systems. Following this introduction we will discuss some specific use cases for MongoDB. This will include examples of interacting with MongoDB from several different languages. Questions and discussion will be encouraged throughout the presentation.

The talk will begin with a discussion of non-relational databases, focusing on the gains in scalability and flexibility that non-relational systems can provide over the typical RDBMS. We will then review some of the non-relational systems that have become popular recently, including BigTable, SimpleDB, CouchDB and MongoDB, with a focus on what differentiates these systems.

The second part of the talk will focus specifically on MongoDB. This will include discussion of the architecture and features of MongoDB, as well as the use cases it excels at. We will also walk through a case study using MongoDB from several different programming languages.

Questions and discussion will be encouraged throughout the presentation, but some time will be alloted at the end specifically for Q&A.

On most Web applications you will find the following scenario : a search box, then a list of results that lead to a page with details. As the list may contain up to thousands of results or even more, you will end up optimizing the display by paginating the list. In order to improve the usability of the list of results, you will add links to browse the different pages and probably add the total number of items in the list. This idea sounds good but unfortunately it can consume so many resources that your whole application can be slowed down!

With a demo application, we will outline the reasons of theses performance problems, then we will show technical and non-technical solutions to succeed in making efficient paginated lists. These solutions will be implemented in our demo application to give an idea of the performance gains we can expect.

Summary of the session :

• Different types of paginated displays
• Performance implications
• Non-technical solutions
• Technical solutions
• Benchmarcks before and after optimization

This feature packed database is small enough to be run in many constrained environments, yet powerful enough to efficiently utilize the power of modern multi-CPU machines. Distributed under the Apache License (v2.0), you are free to bundle Derby as part of your application.

This presentation will give a brief overview of Apache Derby, its history and the community around it. Further, it will go into the details of features that have been added in the latest releases and show how they enable powerful ways to use a relation database.

Topics that will be covered:

• Origin, history and community
• Derby modes (embedded, client/server, combined)
• Overview of features and capabilities
• Making legacy data available to Derby
• New features

Danach wird erläutert, warum wir uns in einer sehr späten Beta-Phase eines SAAS-Projektes nach einer Alternative zur Firebird-DB umgesehen haben. Es wird unter anderem auf den Zusammenhang zwischen Multiversion Concurrency Control (MVCC)/Garbage Collection und der Usability einer AJAX-Webanwendung eingegangen. Die Servervarianten Classic, SuperServer und SuperClassic und deren Skalierbarkeit werden miteinander verglichen.

Anschließend schauen wir uns die Kriterien an, die im konkreten Fall zur Entscheidung für die PostgreSQL-Datenbank und gegen MySQL geführt haben.

Es hat das Management gefreut, dass die Anwendung bei dem rechenintensivsten Feature insgesamt – also die Einheit aus Anwendungscode und Datenbank - um den Faktor 9 schneller als vor dem Umstellungs- und Optimierungsprozess ist. Die gewählte Optimierungsstrategie und Schritte bei der Migration werden beschrieben.

Diving into the architecture of the MySQL Proxy, splitting it up into its components and putting them together to implement something new. From working on binary logs, filtering them, merging them to making MySQL 5.1 upgrade easier. All spiced with some scripting and examples.

Suggested discussion topics

• Backup and recovery: what are the usual tools and applications used to perform backups of your database of choice?
• High Availability: how can your database be made resilient to failure? What functionality is built into your database, which external tools do you use/recommend?
• Scalability: How can your database system be tuned to handle an increase in load/volume? How do you tackle performance issues?
• Security: What measurements can you take to improve the security of the database? Which features does your database provide?
• Monitoring: What functionality does the database system provide? What applications or tools do you recommend for checking the availability and health of the system?
• APIs and Connectors: How can your database interact with the wide range of programming and scripting languages? What interfaces do exist and which ones are commonly used?