Tuesday Jan 28, 2014

Prepare Now for the MySQL Connect 2014 Call for Papers

As announced last month, MySQL Connect will this year be aligned with Oracle OpenWorld and run from Monday September 29 to Thursday October 2, 2014, in San Francisco.

The MySQL Connect and Oracle OpenWorld Calls for Papers will also open at the same time, and you will most likely be able to submit your sessions between March 4 and April 8 (dates subject to change).

While you will have 5 weeks or so to actually submit your talk proposals, you can already prepare your submissions NOW in order to be ready to submit then, and here is the key information you need to do so. In order to submit a session, you will need to provide:

  • Title
  • Conference session type: This can be either a conference session (1 hour), a Birds-of-a-Feather session (1 hour), or a tutorial (2,5 hours). You are obviously welcome to submit several sessions.
  • Abstract: 750 characters maximum including spaces
  • Speaker’s name(s), e-mail(s), phone(s) and bio(s)
  • Track - MySQL Connect 2014 will have 5 tracks:
    • Performance & Scalability
    • High Availability and Replication
    • Cloud & Big Data
    • Database Administration and DevOps
    • Architecture & Application Development


Get going now and you will have ample time to prepare your MySQL Connect presentations submissions! Let us know if you have any question.

We look forward to a fantastic MySQL Connect 2014!

Tuesday Jan 14, 2014

Atos Gère le Clearing des Transactions des Marchés Euronext en Europe Continentale avec MySQL Enterprise Edition

Acteur international des services informatiques avec un chiffre d’affaires annuel de 8,8 milliards d’euros et 77,100 collaborateurs dans 52 pays, Atos fournit à ses clients du monde entier des services informatiques dans 3 domaines, Conseil & Services Technologiques, Intégration de Systèmes et Infogérance & BPO, ainsi que Services Transactionnels de Haute Technologie avec Worldline.

En 2009, Atos s’est vu confier le projet de réduire drastiquement les coûts du moteur de compensation des marchés Cash (actions et obligations) et Produits Dérivés d’Euronext en Zone euro: l’application UCS (Universal Clearing System). Dans le domaine financier, la compensation, ou le "clearing", s'exerce par l'agrégat de toutes les positions d'achats et de ventes par type de produit/actif détenu par chaque teneur de compte, et se matérialise par un solde net dû ou à recevoir ainsi que par des flux nets de titres à livrer ou à recevoir.


 Défis

  • Migrer d’un environnement propriétaire basé sur des systèmes HP NonStop-SQL/MP vers un environnement ouvert et des technologies open source permettant à la fois une réduction drastique du coût total de possession (TCO) et une indépendance technologique.
  • Garantir la robustesse et la haute disponibilité de cette application de marchés financiers hautement critique.
  • Accroître les performances et la montée en charge d’UCS pour répondre à une augmentation constante du volume de transactions.

Solution
  • Adoption d’une stratégie Open Source et complète ré-architecture de l’application basée sur un stack incluant Linux, MySQL Enterprise Edition, Apache/Tomcat, langage C et Java. La nouvelle version d’UCS s’articule autour de plusieurs serveurs ou clusters de serveurs blade sous Linux, dont certains sont virtualisés, et d’une baie SAN. InnoDB est le moteur de stockage MySQL principalement utilisé par l’application qui exploite également dans une moindre mesure le moteur Memory.
  • Mise en place d’une architecture construite autour de processus métier agissant comme sous-composants multi-instanciables pour favoriser une rapide montée en charge. L’architecture précédente pouvait en effet créer des goulots d’étranglement du fait d’une intégration en silo de l’ensemble des processus.
  • Redondance géographique assurée grâce à une réplication synchrone entre 2 centres de données distants de plus de 40 kilomètres.
  • L’application UCS gère :
    • Plus d’un million de transactions par jour, et a atteint un pic à plus de 2 millions de transactions journalières durant l’été 2011.
    • 8000 fichiers envoyés par jour aux clients
    • 700 processus batch chaque nuit
Plusieurs benchmarks ont été établis permettant de s’assurer que l’application pouvait gérer 6 millions de transactions journalières. Vincent Cornet, DBA Support Manager chez Atos se souvient "Durant l’un de nos benchmarks, nous avons fait face à un crash. Nous nous sommes tournés vers l’équipe de support MySQL d’Oracle et avons obtenu un correctif en moins de 48 heures, correctif intégré ensuite dans la version officielle de MySQL. Nous avons été ravis d’une telle réactivité."
  • Mise en production de l’application assurant le clearing des marchés Cash après une réécriture complète durant un projet de 15 mois et 12000 jours/h résultant en :
    • Une architecture technique et logicielle intégralement renouvelée
    • De nouvelles salles informatiques
    • De nouvelles baies SAN et nouveaux serveurs blade banalisés
    • De nouveaux outils d’exploitation et de supervision, incluant MySQL Enterprise Monitor et MySQL Workbench
    • Un succès, et un client extrêmement satisfait
  • Suite à cette réussite, refonte similaire du système pour le clearing des produits dérivés. Travail d’équipe rassemblant plus de 40 personnes des différents départements impliqués, et une "task force" en place 24h/24 pendant 6 mois.
  • L’objectif de réduction draconienne du coût total de possession a été atteint, tout en simplifiant la maintenance du système et en augmentant ses performances. La nouvelle application UCS s’avère au final être un succès reconnu et salué par l’ensemble des acteurs. Elle représente non seulement une des meilleures solutions transactionnelles du marché, mais aussi un pari à la fois technologique et humain gagné grâce à une forte coopération des équipes pour conduire le changement en utilisant de nouvelles technologies. Atos a en outre acquis un savoir-faire et une expérience pouvant être valorisée auprès d’autres clients dans de multiples secteurs d’activités.
  • En plus d’une mise à jour vers MySQL 5.6, Atos étudie désormais l’opportunité d’utiliser MySQL Cluster au sein de l’architecture d’UCS ou d’autres applications similaires dans le futur.

"MySQL nous permet de passer des nuits très sereines, ce qui était loin d’être de cas avec l’ancien système. Nous délivrons de meilleurs résultats pour un coût inférieur, et la réaction du support d’Oracle est exemplaire." Vincent Cornet, DBA Support Manager, Atos


Thursday Oct 31, 2013

After 10 Years, MySQL Still the Right Choice for ScienceLogic's "Best Network Monitoring System on the Planet"

ScienceLogic has a pretty fantastic network monitoring appliance.  So good in fact that InfoWorld gave it their "2013 Best Network Monitoring System on the Planet" award.  Inside their "ultraflexible, ultrascalable, carrier-grade" enterprise appliance, ScienceLogic relies on MySQL and has since their start in 2003.  Check out some of the things they've been able to do with MySQL and their reasons for continuing to use MySQL in these highlights from our new MySQL ScienceLogic case study.
  • Science Logic's larger customers use their appliance to monitor and manage  20,000+ devices, each of which generates a steady stream of data and a workload that is 85% write. On a large system, the MySQL database:
    • Averages 8,000 queries every second or about 1 billion queries a day
    • Can reach 175,000 tables and up to 20 million rows in a single table
    • Is 2 terabytes on average and up to 6 terabytes
    • "We told our customers they could add more and more devices. With MySQL, we haven't had any problems. When our customers have problems, we get calls. Not getting calls is a huge benefit."
      Matt Luebke, ScienceLogic Chief Software Architect.


  • ScienceLogic was approached by a number of Big Data / NoSQL vendors, but decided against using a NoSQL-only solution. Said Matt, "There are times when you really need SQL. NoSQL can't show me the top 10 users of CPU, or show me the bottom ten consumer of hard disk. That's why we weren't interested in changing and why we are very interested in MySQL 5.6. It's great that it can do relational and key-value using memcached."
  • The ScienceLogic team is very cautious about putting only very stable technology into their product, and according to Matt, MySQL has been very stable: "We've been using MySQL for 10 years and we have never had any reliability problems. Ever."
  • ScienceLogic now uses SSDs for their write-intensive appliance and that change alone has helped them achieve a 5x performance increase.

Learn more>>




Wednesday Oct 09, 2013

Join us: Upcoming MySQL Tech Tours in EMEA

Would you like to learn about the latest MySQL news and innovations from MySQL Connect/Oracle OpenWorld but couldn't travel there? Are you about to develop a new modern web-based application, wondering what the best database solution is for you? Would you like to learn how to use NoSQL access to MySQL and the new MySQL Database and MySQL Cluster features to build next generation applications?



Join one of our upcoming "Performance, Scalability and High Availability with MySQL" Tech Tours! You will hear directly from the source about new MySQL innovations from Oracle, including how the latest MySQL 5.7 DMR yet again doubles the performance of MySQL, and how the new MySQL Fabric provides you with a framework for both High-Availability and sharding of MySQL Databases. Our technical experts will show you how to take advantage of the new features and enhancements in the best MySQL product releases ever, and share tips and tricks to confer high performance, scalability, availability and flexibility to your applications.

You will also learn how Oracle Linux and Oracle VM deliver more value for your enterprise deployments.

Register Now for the following Tech Tours:

Munich, October 15

Cape Town, November 5

Barcelona, November 7

Milan, November 15

London, November 19

Copenhagen, November 21

We look forward to seeing you there!



Thursday Sep 26, 2013

Tracing MySQL Protocol from Client Side

Protocol tracing infrastructure

Since introduction of PERFORMANCE_SCHEMA in MySQL Server, it is possible to trace what happens inside the server when it executes client queries. The client-side protocol tracing framework is a similar mechanism in the MySQL Client Library. It allows tracing communication of the client with the server. The infrastructure has been included in MySQL 5.7.2 Development Milestone Release.

Unlike PERFORMANCE_SCHEMA, focus of the client-side tracing framework is on tracing the protocol flow and client-server I/O rather than the performance of the client library implementation. The client-side tracing framework recognizes trace events which happen during normal conversation between the client and the server. Events such as sending packets to the server, receiving server replies, establishing and terminating a connection are recognized. These events can be passed to a trace plugin if one is loaded into the library. Trace plugin defines callback functions which are called by the protocol tracing framework when trace events happen.

Protocol tracing infrastructure is enabled by default in the client library. However, it does nothing until a trace plugin is loaded. It is possible to build client library with a test trace plugin included in it and use it to see the protocol tracing framework in action (see below). In what follows it is also described how to build and use own protocol trace plugins.

Note that there can be only one trace plugin loaded into the library. An attempt to load another trace plugin results in an error. This means that to use custom trace plugins one has to use client library without the test trace plugin built into it, as is the case when it is distributed by MySQL.

Test trace plugin

When building client library from sources it can be configured to include a built-in test trace plugin using WITH_TEST_TRACE_PLUGIN cmake option. If this was the case, then the test trace plugin is enabled in the library without any further configuration. It receives trace events and follows the protocol flow checking that everything goes as expected.

By default test trace plugin keeps quiet and ignores unexpected turns in the protocol flow. But user can tell it to produce diagnostic output on standard error stream by setting environment variable MYSQL_TEST_TRACE_DEBUG to something else than 0. An example output produced by the plugin might look as follows:


test_trace: Test trace plugin initialized
test_trace: Starting tracing in stage CONNECTING
test_trace: stage: CONNECTING, event: CONNECTING
test_trace: stage: CONNECTING, event: ERROR
test_trace: error 2038: Can't open shared memory; client could not create requet event (0)
test_trace: stage: CONNECTING, event: CONNECTED
test_trace: stage: WAIT_FOR_INIT_PACKET, event: READ_PACKET
test_trace: stage: WAIT_FOR_INIT_PACKET, event: PACKET_RECEIVED
test_trace: packet received: 74 bytes
  0A 35 2E 35 2E 32 37 00  51 00 00 00 28 3F 43 73   .5.5.27.Q...(?Cs
  3C 6C 2E 3F 00 FF F7 21  02 00 0F 80 15 00 00 00   <l.?...!........
test_trace: 081: stage: WAIT_FOR_INIT_PACKET, event: INIT_PACKET_RECEIVED
test_trace: 081: stage: AUTHENTICATE, event: AUTH_PLUGIN
test_trace: 081: Using authentication plugin: mysql_native_password
test_trace: 081: stage: AUTHENTICATE, event: SEND_AUTH_RESPONSE
test_trace: 081: packet sent: 60 bytes
  85 A2 3F 00 00 00 00 01  08 00 00 00 00 00 00 00   ..?.............
  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00   ................
test_trace: 081: stage: AUTHENTICATE, event: READ_PACKET
test_trace: 081: stage: AUTHENTICATE, event: PACKET_RECEIVED
test_trace: 081: packet received: 7 bytes
  00 00 00 02 00 00 00                               .......
test_trace: 081: stage: AUTHENTICATE, event: AUTHENTICATED
test_trace: 081: stage: READY_FOR_COMMAND, event: SEND_COMMAND
test_trace: 081: QUERY: select @@version_comment limit 1
test_trace: 081: stage: READY_FOR_COMMAND, event: PACKET_SENT
test_trace: 081: stage: WAIT_FOR_RESULT, event: READ_PACKET
test_trace: 081: stage: WAIT_FOR_RESULT, event: PACKET_RECEIVED
test_trace: 081: packet received: 1 bytes
  01                                                 .
test_trace: 081: Expecting result set with 1 columns
test_trace: 081: stage: WAIT_FOR_FIELD_DEF, event: READ_PACKET
test_trace: 081: stage: WAIT_FOR_FIELD_DEF, event: PACKET_RECEIVED
test_trace: 081: packet received: 39 bytes
  03 64 65 66 00 00 00 11  40 40 76 65 72 73 69 6F   .def....@@versio
  6E 5F 63 6F 6D 6D 65 6E  74 00 0C 08 00 13 00 00   n_comment.......
test_trace: 081: Received next field definition
test_trace: 081: stage: WAIT_FOR_FIELD_DEF, event: READ_PACKET
test_trace: 081: stage: WAIT_FOR_FIELD_DEF, event: PACKET_RECEIVED
test_trace: 081: packet received: 5 bytes
  FE 00 00 02 00                                     .....
test_trace: 081: No more fields
test_trace: 081: Reading result set rows
test_trace: 081: stage: WAIT_FOR_ROW, event: READ_PACKET
test_trace: 081: stage: WAIT_FOR_ROW, event: PACKET_RECEIVED
test_trace: 081: packet received: 20 bytes
  13 53 6F 75 72 63 65 20  64 69 73 74 72 69 62 75   .Source distribu
  74 69 6F 6E                                        tion
test_trace: 081: Received next row
test_trace: 081: stage: WAIT_FOR_ROW, event: READ_PACKET
test_trace: 081: stage: WAIT_FOR_ROW, event: PACKET_RECEIVED
test_trace: 081: packet received: 5 bytes
  FE 00 00 02 00                                     .....
test_trace: 081: End of data  

We can see the various trace events reported to the plugin and the data such as packet contents provided with each event. Note that the protocol tracing infrastructure maintains information about the current stage of the protocol. This is helpful for trace plugin to correctly interpret trace events. For example, a packet received from server (PACKET_RECEIVED event) has different meaning and structure depending on whether it arrives during authentication phase (WAIT_FOR_INIT_PACKET stage), just after query has been sent (WAIT_FOR_RESULT stage), when server sends result set metadata (WAIT_FOR_FIELD_DEF stage) or when it sends rows of a result set (WAIT_FOR_ROW stage).

As can be seen the output is quite verbose thus MYSQL_TEST_TRACE_DEBUG should be enabled only for debugging purposes. One can also instruct the test trace plugin to crash client application if it detects problems in the protocol flow. This is done by setting environment variable MYSQL_TEST_TRACE_CRASH to something else than 0.

How to write a trace plugin

Protocol trace plugins, as any other client-side plugins, are loaded into the client library from a shared module. Client library must know the location where plugin modules are stored (MYSQL_PLUGIN_DIR option) and the plugin must be explicitly loaded into the library using mysql_load_plugin() C API call. It is also possible to name the plugin module using environment variable LIBMYSQL_PLUGINS and then it will be automatically loaded during client library initialization. See documentation of plugin functions in C API for the details.

A plugin module must define plugin descriptor using mysql_declare_client_plugin() macro. This macro and other relevant types are defined by <mysql.h> and <mysql/plugin_trace.h> headers which should be included prior to defining a trace plugin.

mysql_declare_client_plugin(TRACE)
  "my_trace_plugin",            /* plugin name */
  "Author Name",                /* author */
  "protocol trace plugin",      /* description */
  {1,0,0},                      /* version = 1.0.0 */
  "GPL",                        /* license type */
  NULL,                         /* for internal use */
  plugin_init,                  /* plugin initialization function */
  plugin_deinit,                /* plugin shutdown function */
  plugin_options,               /* option-handling function */
  tracing_start,                /* start tracing new connection */
  tracing_stop,                 /* stop tracing connection */
  trace_event                   /* handle trace event */
mysql_end_client_plugin;

Callback functions plugin_init(), plugin_deinit() and plugin_options() are the same as for any other type of client-side plugin and are described in plugin data structures documentation.

Callbacks tracing_start(), tracing_stop() and trace_event() are specific to trace plugins and are documented in <mysql> header. Function tracing_start() is called when a new connection is established with mysql_real_connect() C API call. This happens at a very early stage, before any packets are exchanged with the server. Function might return a pointer to plugin specific trace data which will be kept in the MYSQL connection handle and passed to other tracing callbacks. Note that this function is different from the global plugin initialization function plugin_init() which is called only once, after loading the plugin. Function tracing_start() is called for each new connection made by the client.

Function tracing_stop() is called when tracing of the connection has ended. This can happen due to normal connection termination as defined by the protocol, because of connection error detected in the client library or because tracing of this connection has been explicitly terminated by trace_event() callback (see below). This function should de-allocate any resources allocated in tracing_start().

Function trace_event() is called by the protocol tracing infrastructure when various trace events happen. The current protocol stage and additional arguments are passed with each trace event so that this function can for example examine contents of the packets being sent or received. Function trace_event() can tell the protocol-tracing infrastructure to stop tracing the current connection by returning non-zero value.

Here is a list of trace events that are reported to a protocol trace plugin:


Connection events
CONNECTING Client is connecting to the server.
CONNECTED Client is connecting to the server.
DISCONNECTED Connection with server was terminated.
SSL events
SEND_SSL_REQUEST Client is sending SSL connection request.
SSL_CONNECT Client is initiating SSL handshake.
SSL_CONNECTED SSL connection has been established.
Authentication events
INIT_PACKET_RECEIVED Client received initial handshake packet.
AUTH_PLUGIN Client selects an authentication plugin to be used in the following authentication exchange.
SEND_AUTH_RESPONSE Client sends response to the authentication challenge.
SEND_AUTH_DATA Client sends extra authentication data packet.
AUTHENTICATED Server has accepted connection.
Command phase events
SEND_COMMAND Client is sending a command to the server.
SEND_FILE Client is sending local file contents to the server.
General events
READ_PACKET Client starts waiting for a packet from server.
PACKET_RECEIVED A packet from server has been received.
PACKET_SENT After successful sending of a packet to the server.
ERROR Client detected an error.

Test trace plugin implementation

A good place to look for an example of a trace plugin implementation is file test_trace_plugin.cc in libmysql/ subdirectory of MySQL Server source tree. It defines the test trace plugin described above.

Note that test trace plugin is a bit special as it is not loaded dynamically into client library, but it is built into it. This is why the plugin is not declared with the mysql_declare_client_plugin() macro but rather the plugin descriptor structure is defined explicitly. It is then added to the mysql_client_builtins[] array defined in sql-common/client.c.

Looking at the code in test_trace_plugin.cc one can see there how plugin's specific trace data structure st_trace_data is allocated in trace_start() callback. Among other things it contains a next_stage member which specifies the expected stage of the protocol when next trace event is reported. This expected stage is updated in check_event_XXX() functions called from the main trace_event() callback. In trace_event() function there is a check that the protocol stage reported by trace infrastructure is as expected:

if (data && data->next_stage != stage)
{
  LOG(("wrong stage, expected: %s",
       protocol_stage_name(data->next_stage)));
  if (opt_crash)
    DBUG_ASSERT(0);
}

One can also see how trace_event() function analyses arguments passed with each trace event and shows them in the log.

Full discussion of the implementation of test trace plugin is out of the scope of this article. But hopefully the source code and comments there can give an idea how one can write a trace plugin and what can be achieved with the protocol-tracing infrastructure.

MySQL Connect Keynotes and News

The second edition of MySQL Connect took place just a few days ago, Sept 21-23, and was a great event. Thank you very much to all participants including speakers, sponsors, exhibitors and attendees! We hope you had a fantastic time and learned a lot.

The keynotes are now available for you to watch on demand (both highlights and full length), including:

The State of the Dolphin
Edward Screven, Chief Corporate Architect, Oracle
Tomas Ulin, Vice President of MySQL Engineering, Oracle

MySQL at Facebook
Harrison Fisk, Manager, Data Performance, Facebook