Ulf Wendel

Wie gelingt es wenigen Personen über Jahre hinweg immer wieder Interesse für ihre Vortrage zu wecken? Carola "Sammy" Koehntopp bietet während der FrOSCon 2011 eine PHPopstars-Veranstaltung an. Sie erklärt die Grundmuster hinter erfolgreichen Vorträgen, die gerne besucht werden und bittet einige PHPopstars ihr Können in Kurzvorträgen zu beweisen und sich dem Jury- und Publikumsurteil zu stellen. Ihr Ehemann, Kristian Koehntopp, gewinnt das Casting.

Die Veranstaltung wird so gut angenommen, daß Carola das Format erneut anbietet in Rahmen der PHP Unconference Hamburg 2011. Ein denkwürdiger Ort, der in vergangenen Jahren dominante Superstars sah, denen es gelang, auffallend viel Aufmerksamkeit auf sich zu ziehen.

Sugar on top

Foto: FrankS

Kris durfte in Hamburg nicht noch einmal mitmachen. Es bestand Wiederholungsgefahr. Ich war stolz wie Oskar, als ich gefragt wurde, ob ich seinen Platz einnehmen möchte. Nicht als Ehemann natürlich, sondern als Affe auf der Castingbühne. Ich nahm die Herausforderung an, machte mich zum Oberaffen, gab mir selbst Zucker und trat in die Fußstapfen meines Lehrmeisters. Ich gewann das Casting dank "sugar on top".

Seht selbst. Etwas Fantasie ist beim Lesen gefragt, denn eines Popstars gleich, setzte ich auf eine Show und die lässt sich kaum in Worten wiedergeben.

After five years I am still in love with the PHP Unconference Hamburg (Germany), which took place last weekend. On Saturday morning I was as thrilled as if I was going to the first date with a person I had just fallen in love to. Facts: 200 tickets sold in 20 minutes! 350 tickets sold in total, 280 attending. One can’t compare this pure madness with any german PHP (un)conference I have presented at during the past 11 years. The continued success of this local german event makes me break my own tradition of german language only reports. What is so special about this event? What makes it my #1 annual PHP event in Germany?

Unbelievable, unbeatable unconference

Secret number one: the popstars. All the german PHP popstars go there since five years! This year, for example, Pierre Joye (PHP core developer, Mr. Windows), Sebastian Bergmann (PHP expert, book author), Johannes Schlueter (PHP 5.3 release manager), Kristian Koehntopp (general purpose guru at booking.com), Jan Lehnardt (CouchDB), Hartmut Holzgraefe (PHP contributor, MySQL guru) came to Hamburg, to name only a few of the internationally known. Plus, a great number of internationally lesser known experts that can easily compete with the superstars.

Secret number two: is it an unconference. At the beginning of every unconference day, all attendees gather around the "bathtub". People propose sessions of any kind – discussion, presentations, workshops – and start voting. This year, there have been about one hundred proposals to choose between. Because the audience does the selection, only relevant and hot topics win. There is no expert jury making more or less educated guesses on what could be of interest. No sponsor slots.

Secret number three: the price. The ticket price of 30 Euro is affordable to all. Prices are kept extremly low by help of many sponsors (Travian Games, Bigpoint, Mayflower, Cloud Control, Microsoft, Amazon Web Services, Mindworks, TEQneers, ICANS, apprupt, Bytemark Hosting, Estivo). The Department of Informatics, Hamburg University, provides the perfect venue with the "bathtub" in the middle and six surrounding lecture rooms. Food is self-service but yummy and, if it comes to important details, such as the coffee, it is stellar. A wide variety of Open Source (biological, fair-trade) coffee was offered! That was real coffee not hot water as served in many conference centers.

Photo: Jonathan Maron

The mood

Secret number four: the mood. As a developer or team leader you go there to feel home. You are welcomed with a smile, you do not need to dress up, internet connectivity works flawless, good air in all rooms, all the beamers work, you drive the agenda, the evening event is at a beer brewery. And, if you know who to follow not only on Twitter but in real life, you can party around the clock. Hamburg never sleeps especially not the pubs and clubs in St. Pauli.

Album on Flickr with 124 reasons to go to the unconference

The megastars, the organizers

The biggest secret: the organizers. Take a detailed checklist on how to prepare an unconference and give it to a team of volunteers. Most teams will fail after short time. Only few will teams will be able to organize the event. And, even less teams will be able to repeat their success for five years: 200 tickets in 20 minutes… It needs way more than a checklist to form such a team, to glue them together and to be a long term member.

Invisible to attendees most of the time the megastars ensured – somehow – that people like me call their unconference Germany’s #1 annual PHP event. The megastars made sure that the above listed popstars have a stage to present themselves. This years megastars are:

• Florian Blasel
• Ekkehard Dörre
• Jonathan Maron
• Stefan J. Morgenroth
• Hinrich Sager
• Benny Schilling
• Markus Wolff

Congratulations and thank you, dear megastars!

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print the protocol transaction flag after statement execution.

$link = new mysqli("localhost", "root", "root", "test"); 
$link->autocommit(false);
$link->query("DROP TABLE IF EXISTS test"); 
$link->query("CREATE TABLE test(id INT) ENGINE=InnoDB"); 
$link->query("SET @myvar=1"); 
$link->query("SELECT @myvar"); 
$link- >query("SELECT * FROM test"); 
$link->query("INSERT INTO test(id) VALUES (1)"); 
$link->query("ROLLBACK"); 
var_dump($link->query("SELECT * FROM test")->fetch_all());

in transaction 0 - SET AUTOCOMMIT=0
in transaction 0 - DROP TABLE IF EXISTS test
in transaction 0 - CREATE TABLE test(id INT) ENGINE=InnoDB
in transaction 0 - SET @myvar=1
in transaction 0 - SELECT @myvar
in transaction 1 - SELECT * FROM test
in transaction 1 - INSERT INTO test(id) VALUES (1)
in transaction 0 - ROLLBACK
in transaction 1 - SELECT * FROM test
array(0) {
}

The test script turns off autocommit using the $link->autocommit() API call. The mysqlnd library makes the SQL statement SET AUTOCOMMIT=0 from it. The server does not set SERVER_IN_TRANS flag in his reply to the client. The script continues and creates a table. The server still does not announce that we are in a transaction, because some statements cause an implicit commit. It takes until after the execution of SELECT * FROM test before the server sets the protocol flag to announce a running transaction.

If the plugin would base its transaction aware load balancing on the protocol flag, it would send SET AUTOCOMMIT=0, DROP TABLE IF EXISTS test, CREATE TABLE test(id INT) ENGINE=InnoDB, SET @myvar=1 to the master, run SELECT @myvar and SELECT * FROM test on a slave before the plugin learns that the previous statement was part of a transaction and the master gets used for INSERT INTO test(id) VALUES (1).

The plugin works better

With todays plugin implementation, everything after $link->autocommit(false) will be considered part of a transaction. Everything will be run on the master until $link->autocommit(true) is run.

The plugin has limits

You can fool the plugin executing SQL to start a transaction such as BEGIN, START TRANSACTION or SET AUTOCOMMIT=0. However, I still prefer that limitation over the protocol flag.

The protocol flag is not suited for transaction aware load balancing. For other applications it may shine. For example, its fantastic for Global Transaction ID injection.

SQL injection is wonderful! MySQL Proxy can do it, mysqlnd plugins – even written in PHP (not Lua or C) – can do it. Global Transaction IDs are wonderful. A mashup of the PHP replication plugin and global transaction ID injection, makes your replication cluster fail-over much smoother and opens up an opportunity for an API to support consistent reads from slaves "immediately" after a write. Less hassle identifying and promoting a new master for fail-over, even better read load balancing – my last proposal for the future of the PHP replication plugin.

What?

Think of a global transaction ID as a unique identifier for a change set in a database cluster. Replicas in the cluster use the global transaction ID to track changes. Because global transaction IDs are unique cluster-wide, you can easily compare the replication progress among the replicas, in particular, if the global transaction ID contains a sequence number.

Continue Reading →

While Andrey is busy implementing partitioned replication infrastructure code for the PHP replication and load balancing plugin (PECL/mysqlnd_ms), I continued my search for ideas to steal. Mr. Robert Hodges, I’ve robbed the idea of a service level and caching.. If an application is able to function with stale data read from a MySQL replication slave, it can also deal with stale data from a local cache. The replication plugin (PECL/mysqlnd_ms) could, in certain cases, populate the query cache plugin (PECL/mysqlnd_qc) for you and read replies from it.

In the blog posting "Implementing Relaxed Consistency Database Clusters with Tungsten SQL Router" Robert explains from a theoretical standpoint why his product allows application developers to set quality of service. The service level defines if eventual consistency is allowed or not. If so, the system is not required to return current data to all clients. Stale data may be served.

If using MySQL replication for read scale out, dealing with stale data is a standard task. Slaves may lag behind the master and have not the latest updates. Applications must be able to function with stale data. Given that the service level allows stale data, one can replace one stale data source with another. One can replace a MySQL slave possibly lagging behind with a local (TTL) cache.

All that needs to be done is combining PECL/mysqlnd_ms, the replication and load balancing plugin, with PECL/mysqlnd_qc, the query cache plugin. Of course, this should be done on the C level, inside the extensions. Ideally, applications using the combination of the two plugins would not need to bother of populating the cache and deciding when to read from it.

mysqlnd_ms_set_service_level($mysqli, MYSQLND_EVENTUAL_CONSISTENT);
$mysqli->query("SELECT  id FROM test");
$mysqli->query("SELECT id FROM test");

The replication plugin would just know from the service level that queries may be served from the cache. For example, it could automatically decide to cache the SELECT from the example above. Could… this is brainstorming. No promises on features and time lines. I’m fishing for feedback.

Dream on, read on at https://wiki.php.net/pecl/mysqlnd_ms#raw_bin_ideas_rfcs. Feel free to edit the wiki page…

The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

Replicating different databases to different servers

Replicating different databases to different servers is a standard MySQL replication pattern. There’s a magnitude of possible applications. For example, your MySQL master server may serve more than one application, say it serves a public web application and a private reporting application monitoring the web application. The private reporting application does not need to be scaled-out using MySQL replication. It has only few admin users. Also, it is sort of private and shall not be mirrored on other machines. Whereas the main web application has a very high read load and needs to be replicated to some slaves for load distribution.

Continue Reading →

Frühlingsputz: weg mit den alten Zeitungen! Noch mehr Wohnung kann ich mir nicht leisten!

Halt: das sind keine Fotomagazine, keine Geo-Hefte, keine Airbrush-Magazine und auch keine normalen Computerzeitschriften. Was mir in die Hände fällt sind Belegexemplare. Genussvoll blättere ich im moderigen Papier. Gedanken kommen auf. Als ich das schrieb, saß ich mit dem Dell-Notebook, welches ich für den Kongress…

Bei Betrachtung der Liste drängt sich mir der Eindruck einer einseitigen Ernährung auf. Für den Herbst, nachdem mein Beinbruch auskuriert ist, sollte ich mir etwas Neues ausdenken. Bis dahin werde ich mich wohl auf Blogging konzentrieren.

Veröffentlichungen

PHP Spezial (Linux Enterprise), erschienen Herbst 2001
Ja, ich hatte wirklich mehrere Artikel in dem Heft.

• PHP-Community bündelt Kräfte – Der neugegründete PHP Verein stellt sich vor
• Zeit gewinnen mit Templates – Trennung von Layout und Code
• Schnelligkeit durch Faulheit – Applikationen beschleunigen mit dem PEAR Daten/Content Cache
• Websites, Tools und Editoren – Die besten Quellen rund um PHP
• "Wir wollen ein Bindeglied sein", Interview von Sebastian Bergman mit Ulf Wendel, Georg Richter, Jörg Behrens und Thomas Fromm, Mitglieder des neu gegründeten PHP-Vereins (ebenfalls erschienen in Linux Enterprise, September 2001)

PHP im Einsatz (Begleitbuch zur PHP Konferenz 2001), erschienen 2002
Objektorientierte HTMLForms

PHP Magazin, 4.2003
Datenkapsel – Von DAOs, Business Entities, Resultsets und Spreadsheets – Teil 1

PHP Magazin, 5.2003
DAOs, die Zweite – Resultsets als Mittel zur komfortablen Suche – Teil 2

PHP Magazin, 6.2003
Reiseziel Access im Web – Von Spreadsheets, Controllern und Interpretern – Teil 3

PHP Magazin, 1.2004
Zielgerade – Von Betacode und Fernzielen: Data Access Objects – Teil 4

PHP Magazin, 6.2005
MySQL 5.0 und MaxDB 7.6 – Reifeprüfung

mysql.com, 12/2005
MaxDB for SAP Hosting – Technical Whitepaper

SAP Info, 1.2006
MaxDB: Hohe Professionalität, niedrige Betriebskosten

SAP Info, 2.2006
MaxDB: Sicherer Betrieb für Anwender und Entwickler

Entwickler Magazin, 2.2006
MySQL 5.0 – Best Practices: Tipps und Tricks zu MySQL

PHP Magazin, 3.2006
Backen für PHP-MySQL-Anwender unter Windows – Es ist angerichtet

PHP Magazin, 4.2006
Hand in Hand – Wie XA-Transkationen und das Zwei-Phasen-Commit-Protokoll aus MySQL und Oracle Freunde machen

php|architect, December 2007
Going Native With mysqlnd – Learn about the new MySQL driver for PHP

Linux Magazin Technical Review: Alles über: Datenbanken – Praxistipps von Entwurf bis Tuning (Buch), Ausgabe 2008
Fünf Fragen an MySQL: Storage Engines, Unicode, Neuerungen, Zukunftsaussichten, Geodaten

PHP hates me, 2.2009
Nimmer Ärger mit den Persistenten Verbindungen von MySQL

PHP Magazin, 5.2010
Guerillia Gardening im MySQL-Treibhaus – Mit mysqlnd-Plug-ins den Datenbanktreiber erweitern

PHP hates me, 7.2010
Ulf Wendel und das MySQL-Mofa

Entwickler Magazin, 1.2011
Ich sehe was, was Du nicht siehst – Ein transparentes MySQL-Cache-Plug-in für PHP

Vorträge (Konferenzen)

PHP Konferenz 2000, Köln
PHPDoc

International PHP Conference 2001, Frankfurt
Workshop: After OOH-Forms: auto-layout, XML and wizards

International PHP Conference 2003, Frankfurt
Workshop: Applikationsbasiertes Caching
(Jan Kneschke, Ulf Wendel )
Zeigen, Bearbeiten und Speichern von Data Access Objects (DAOs)
Die OOP-Sprachfeatures von PHP im Vergleich mit anderen Technologien

PHPUG Hamburg, Dezember 2003
DAO, Resultsets und Spreadsheets – was PEAR nicht kann

International PHP Conference 2008, Mainz
Running Asynchronous Queries using ext/mysqli and mysqlnd

International PHP Conference 2010 Spring Edition, Berlin
Das MySQL-Treibhaus erweitern (The PHP mysqlnd plugin talk – plugins an alternative to MySQL Proxy)

Berliner PHP Usergroup, Juni 2010
Die mysqlnd plugin API

PHP Unconference Hamburg, September 2010
Top Ten Irrtümer und Fehler von PDO

FrosCon 2010, PHP Track, St. Augustin
The power of mysqlnd plugins

International PHP Conference 2010, Mainz
Ran an den Klienten – ein Cache-Plugin für mysqlnd (Award-winning technology: Oxid loves the query cache)

PHP Unconference Hamburg, 11/2011
PHPopstar
Gastredner und Sieger des Wettbewerbs “PHPopstar” dessen Ziel es ist Zuschauer zu motivieren Beiträge einzureichen.

Webseminare

mysql.com, April 2006
Performanzoptimierung für MaxDB

mysql.com, July 2009
Der neue MySQL Connector/C++ (The new MySQL Connector/C++)

mysql.com, July 2009
Der neue MySQL Connector/C++ (The new MySQL Connector/C++)

mysql.com, November 2011

Building High Performance and High Traffic PHP Applications with MySQL, part 3: Succeed with Plugins
Co-Moderator für Teil 1 und 2. Entwurf der Inhalte aller Teile.

Weitere Onlinepräsentationen

Juni 2010
Built-in query caching for all PHP MySQL extensions/APIs

Juni 2010
Mysqlnd query cache plugin statistics and tuning

Juli 2010
Mysqlnd query cache plugin: user-defined storage handler

Juli 2010
Mysqlnd query cache plugin benchmark report

August 2011
The mysqlnd replication and load balancing plugin

Oktober 2011
MySQL native driver for PHP (mysqlnd) – Introduction and overview, Edition 2011

Dezember 2011
Load Balancing for PHP and MySQL

Aktualisierungen – http://www.slideshare.net/nixnutz/presentations

Sonstiges

PHP BBQ Tour
Eine einwöchige Rundreise durch sieben Städte in Deutschland mit Grillabend bei sieben deutschen PHP-Anwendergruppen. Die PHP-Anwendergruppen stellten sich vor und lockten neue und alte Gesichter im lockeren Rahmen eines Grillabends.

The PHP replication and load balancing mysqlnd plugin as been released as an alpha through PECL. Alpha is for those who want to try out the 1.0 feature set as early as possible. Alpha is for those who do not fear debugging, if need be. Notes on troubleshooting.

Testing load balancing and read/write split

There are three ways to verify that load balancing and read/write split works as it should:

• within PHP script: checking thread/connection ids
• within PHP script: monitoring statistics
• externally: mysqlnd debug log file

Thread/connection ids to distinguish connections

PECL/mysqlnd_ms changes the semantics of a PHP MySQL connection handle. If using the plugin, a connection handle returned by any of the three PHP MySQL extensions (mysql, mysqli, PDO_MySQL) does no longer map to a physical MySQL connection in a strict 1:1 way. The connection handle represents a pool of connections managed by the plugin. At a time, the connection handle may be mapped to a connection to the master. Later on, it may be mapped to a connection to one of the slaves.

• mysql: int mysql_thread_id ([ resource $link_identifier ] )
• mysqli: int mysqli::mysqli_thread_id ( mysqli $link )
• PDO_MySQL: n/a

Every physical connection to MySQL has a thread/connection id. By checking the thread id, a PHP script can detect if the plugin has decided to switch connections. You can obtain the thread id of a mysql or mysqli connection handle using the functions mysql_thread_id() respectively mysqli_thread_id(). PDO_MySQL offers no corresponding API call. Please, check the thread id immediately after running a query.

• SELECT CONNECTION_ID()

You must not use the query SELECT CONNECTION_ID() to fetch the thread id for checking if the load balancing plugin works! The query itself will be load balanced. It makes the plugin potentially switch connections. It changes the state of the connection handle. Thus, it cannot be used to monitor the state.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306
pick[]=random_once

function run_query($mysqli, $query, $hint = NULL) {

  if (!is_null($hint) )
    $query = sprintf("/*%s*/%s", $hint, $query);
  
  if ($ret = $mysqli->query($query))
    return $ret;

  printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
  return $ret;
}

/* Create load balanced connection handle */
$mysqli = new mysqli("myapp", "username", "password", "database");

/* Find connection thread ids, assumption: SQL hints work */
$threads = array('master' => NULL, 'slaves' => array());
run_query($mysqli, "SELECT 1 FROM DUAL", MYSQLND_MS_MASTER_SWITCH);
$threads['master'] = $mysqli->thread_id();
run_query($mysqli, "SELECT 1 FROM DUAL", MYSQLND_MS_SLAVE_SWITCH);
$threads['slaves'][$mysqli->thread_id()] = $mysqli->thread_id();

/* Verify if plugin works */
run_query($mysqli, "DROP TABLE IF EXISTS test");
if ($mysqli->thread_id != $threads['master'])
  printf("DROP TABLE has not been run on the master!");
run_query($mysqli, "SELECT 1 FROM DUAL");
if (!isset($threads['slaves'][$mysqli->thread_id]))
  printf("SELECT has not been run on a slave!");

The more slaves you have, the more complicated the thread id checking matter becomes. Do yourself a favor: start with one or two slaves. If it works with one or two slaves, why wouldn’t it work with three or four… Watch out for the load balancing strategy (random, random_once (sticky), roundrobin, user) you have configured when collecting thread ids.

Statistics – mysqlnd_ms_get_stats()

Users of PDO_MySQL may want to (ab)use statistics to monitor decisions of the plugin. There are some 15 statistics telling you how many statements have been sent to the master server and the slave servers, why they have been sent there, how many connections have been opened and how often the transaction mode has been used.

mysqlnd_ms.enable=1
mysqlnd_ms.collect_statistics=1

Statistics are returned by mysqlnd_ms_get_stats() if mysqlnd_ms.collect_statistics=1 has been set in the PHP configuration file (php.ini).

The statistics have not been designed specifically to monitor connection switches of the plugin when running many PDO_MySQL scripts in parallel on a web server. Like with the mysqlnd query cache plugin statistics are aggregated per process. If your web server deployment model serves several web requests with the same PHP process, you’ll see that statistics are not reset before the PHP process ends.

Nonetheless, you can use the statistics in some cases. The basic idea is that you check the statistics before and after running a query.

$pdo = new PDO( 'mysql:host=myapp;dbname=test', 'username', 'password');

$stats_before = mysqlnd_ms_get_statistics();
$stmt = $pdo->prepare("SELECT 1 FROM DUAL");
$stats_after = mysqlnd_ms_get_statistics();

foreach ($stats_before as $stat => $value) {
  if ($stats_after[$stat] != $value)
    printf("%s - before: %d, after %d\n", $stat, $value, $stats_after[$stat]);
}

The last resort: mysqlnd debug log

If nothing else helps, try to isolate the issue, install a debug version of PHP and check the mysqlnd debug log. The debug log is only available when using a debug version of PHP. Turning on debug can be a severe performance hit, depending on the amount of data written to the log file. Debug log writing is activated with the PHP configuration directive mysqlnd.debug in the PHP configuration file (php.ini).

mysqlnd.debug="d:t:x:O,/tmp/mysqlnd.trace"

The debug log is a function trace log. It shows all mysqlnd and mysqlnd_ms internal function calls. Many of the function calls take a mysqlnd connection handle as a parameter. Relevant function often either show the memory address of the connection handle or its thread id. This way you can track exactly what mysqlnd has done and which server has been used to execute a query.

>mysqlnd_connect
| info : host=myapp user=root db=test port=3306 flags=131072
| >mysqlnd_ms::connect
| | >mysqlnd_ms_ini_section_exists
| | | info : section=[myapp] len=[5]
| | | info : ret=1
| | <mysqlnd_ms_ini_section_exists
| | >mysqlnd_ms_conn_free_plugin_data
| | | >_mysqlnd_plugin_get_plugin_connection_data
| | | | info : plugin_id=5
| | | <_mysqlnd_plugin_get_plugin_connection_data
| | <mysqlnd_ms_conn_free_plugin_data
| | >_mysqlnd_plugin_get_plugin_connection_data
| | | info : plugin_id=5
| | <_mysqlnd_plugin_get_plugin_connection_data
| | >_mysqlnd_pestrdup
| | | info : file=mysqlnd_ms.c    line= 450
| | | info : ptr=0x7fc7a50f29e0
| | <_mysqlnd_pestrdup
| | >_mysqlnd_pestrdup
| | | info : file=mysqlnd_ms.c    line= 454
| | | info : ptr=0x7fc7a50e3f48
| | <_mysqlnd_pestrdup
| | >mysqlnd_ms_ini_string
| | | info : name=master
| | | info : the list has 1 entries
| | | >_mysqlnd_pestrdup
| | | | info : file=mysqlnd_ms_ini.c line= 271
| | | | info : ptr=0x10baeb0
| | | <_mysqlnd_pestrdup
| | | info : ret=localhost:/tmp/mysql.sock
| | <mysqlnd_ms_ini_string
| | >mysqlnd_ms_ini_string
| | | info : name=lazy_connections
| | | info : ret=0
| | <mysqlnd_ms_ini_string
| | info : overwriting socket : /tmp/mysql.sock
| | >mysqlnd_conn::connect
| | | info : host=localhost user=root db=test port=3306 flags=131072 persistent=1 state=0
| | | >_mysqlnd_plugin_get_plugin_connection_data
| | | | info : plugin_id=5
| | | <_mysqlnd_plugin_get_plugin_connection_data
| | | >mysqlnd_ms::set_server_option
| | | <mysqlnd_ms::set_server_option
| | | info : socket=/tmp/mysql.sock
| | | info : transport=unix:///tmp/mysql.sock
| | | >mysqlnd_protocol::get_greet_packet
| | | <mysqlnd_protocol::get_greet_packet
| | | >mysqlnd_net::connect
| | | | info : hashed_details=0xf5b930
| | | | info : calling php_stream_xport_create
| | | | info : setting 31536000 as PHP_STREAM_OPTION_READ_TIMEOUT
| | | | >mysqlnd_net::set_client_option
| | | | | info : option=203
| | | | | info : MYSQLND_OPT_NET_READ_BUFFER_SIZE
| | | | | info : new_length=32768
| | | | <mysqlnd_net::set_client_option
| | | <mysqlnd_net::connect
| | | info : stream=0xf5bcf0
| | | >php_mysqlnd_greet_read
| | | | info : buf=0x7fffc587c990 size=2048
| | | | >mysqlnd_read_header_name
| | | | | info : compressed=0 conn_id=0
| | | | | >mysqlnd_net::receive
| | | | | <mysqlnd_net::receive
| | | | | >mysqlnd_net::network_read
| | | | | | info : count=4
| | | | | <mysqlnd_net::network_read
| | | | | info : HEADER: prot_packet_no=0 size= 78
| | | | <mysqlnd_read_header_name
| | | | >mysqlnd_net::receive
| | | | <mysqlnd_net::receive
| | | | >mysqlnd_net::network_read
| | | | | info : count=78
| | | | <mysqlnd_net::network_read
| | | | info : proto=10 server=5.5.11-log thread_id=6
| | | | info : server_capabilities=2148530175 charset_no=8 server_status=2 auth_protocol=mysql_native_password scramble_length=21
| | | <php_mysqlnd_greet_read

[...]
>mysqlnd_ms::query
| >_mysqlnd_plugin_get_plugin_connection_data
| | info : plugin_id=5
| <_mysqlnd_plugin_get_plugin_connection_data
| >mysqlnd_ms_pick_server
| | >_mysqlnd_plugin_get_plugin_connection_data
| | | info : plugin_id=5
| | <_mysqlnd_plugin_get_plugin_connection_data
| | >mysqlnd_ms_choose_connection_random_once
| | | >mysqlnd_ms_query_is_select
| | | | >mysqlnd_tok_create_scanner
| | | | <mysqlnd_tok_create_scanner
| | | | >mysqlnd_tok_get_token
| | | | | info : token=329
| | | | | info : strval=ms=master
| | | | <mysqlnd_tok_get_token
| | | | info : forced master
| | | | >mysqlnd_tok_get_token
| | | | | info : token=709
| | | | <mysqlnd_tok_get_token
| | | | >mysqlnd_tok_free_scanner
| | | | <mysqlnd_tok_free_scanner
| | | <mysqlnd_ms_query_is_select
| | | info : USE_MASTER rnd_idx=0
| | | info : Using master connection
| | <mysqlnd_ms_choose_connection_random_once
| <mysqlnd_ms_pick_server
| info : Connection 0xf5ab00
| >mysqlnd_conn::query
| | info : conn=6 query=/*ms=master*/SET @myrole='master'
| | >mysqlnd_conn::simple_command
| | | info : command=QUERY ok_packet=13 silent=0
| | | >mysqlnd_protocol::get_command_packet
| | | <mysqlnd_protocol::get_command_packet
| | | >php_mysqlnd_cmd_write
| | | | >mysqlnd_net::send
| | | | | info : conn=6 count=34 compression=0
| | | | | info : no compression
| | | | | >mysqlnd_net::network_write
[...]

The mysqlnd debug log is the ultimate tool – beside a debugger – to learn how mysqlnd_ms works internally. However, it is targeting developers and not users.

Reporting bugs

If you find that PECL/mysqlnd_ms is not working as it should or you have a feature request, please tell us. It should not be hard to find ways to contact me, Andrey or Johannes. Bugs can be reported using the php.net/PECL bug tracker. Needless to say, it would be fantastic if you followed the hints from above on debugging when filing a bug report to give as much qualified input as possible.

Happy Easter, happy hacking!

Replication is as old as life on earth. No life without replication. MySQL replication is as old as MySQL, almost. No MySQL without replication. The PECL/mysqlnd_ms 1.0.0 alpha release is brand new. My dream, no PHP without the mysqlnd library replication and load balancing plugin, which works with all the PHP MySQL extensions (mysql, mysqli, PDO_MySQL)!

PECL/mysqlnd_ms (download alpha release) is a transparent plugin for the mysqlnd library. It inspects statements executed by any of the three PHP MySQL extensions, if they are compiled to use mysqlnd. Read-only statements are load balanced and send to one of the configured MySQL slave servers. All other statements are executed on the configured MySQL master server. Connection pooling and connection switches are done automatically. The plugin does not change the user API. Depending on the usage scenario very little, if any, application changes may be needed.

All automatic decisions of the plugin can be monitored and overruled, if need be.

Usage example

Basic usage of the plugin is straight-forward. The plugin deploys its own configuration file. Among others, the configuration file instructs the plugin which MySQL master server and MySQL slave servers to use. The configuration file is divided into sections. Each section has a name. For creating a load balanced connection open a MySQL handle and use the section name as a host name.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306

/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");

Any non-prepared statement run on any of the three MySQL handles opened in the previous example will be load balanced. Every statement which begins with SELECT is considered read-only and sent to a slave. All other statements are run on the master server. SQL hints can be used to force using a slave, the master or the last used server. A user defined callback can be installed to replace the built-in read write split mechanism.

/* Statements will be run on the master */
if (!$mysqli->query("DROP TABLE IF EXISTS test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("CREATE TABLE test(id INT)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
if (!$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}

/* read-only: statement will be run on a slave */
if (!($res = $mysqli->query("SELECT id FROM test")) {
 printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
 $row = $res->fetch_assoc(); 
 $res->close();
 printf("Slave returns id = '%s'\n", $row['id'];
}
$mysqli->close();

Three load balancing policies are available: random, random once (sticky), round robin. Additionally a user defined callback can be installed to pick the server for running a statement.

Key Features at a glance

• Transparent and therefore easy to use
  • supports all PHP MySQL extensions
  • no API changes
  • very little, if any, application changes required, dependent on the usage scenario required
• Featured read-write split strategies
  • automatic detection of SELECT, supports SQL hints to overrule automatism
  • user-defined
• Featured load balancing strategies
  • round robin: choose different slave in round robin fashion for every slave request
  • random: choose random slave for every slave request
  • random once (sticky): choose random slave once to run all slave requests for the duration of a web request
  • user-defined. The application can register callbacks with mysqlnd_ms

Current status and Limitations

The plugin is released as alpha. The release contains all features planned for 1.0. It shows the final API. The release does pass all tests. There are no known bugs. However, as an alpha release it cannot be recommended for mission critical use.

The 1.0 alpha does not support multi-statements. Also, it does not support native prepared statements. Please, do not confuse the PDO prepared statement emulation used by PDO_MySQL by default with native prepared statements. By default, PDO_MySQL works with the plugin. Further limitations are documented in the manual.

Happy Easter, happy hacking!

Feedback and comments are welcome! Happy Easter, happy hacking. Enjoy replication.

Download, Documentation, Blog postings

• PECL mysqlnd_ms package and download
• Documentation (soon to appear on the mirrors)
• Related blog postings (sorted by date, latest first)
  • PECL/mysqlnd_ms: transaction aware load balancing, sort of
  • PHP replication and load balancing plugin: master_on_write, documentation
  • PECL/mysqlnd_ms: let web requests be your M/S load balancer
  • A missed load balancing opportunity?
  • What if PECL/mysqlnd_ms does not implement failover?
  • PHP: Application calling plugin – on communication
  • PHP: Master slave load balancing in mysqlnd – how transparent?
  • PHP: … want transparent master slave load balancing in mysqlnd?

PECL/mysqlnd_ms is now transaction aware, sometimes. A new experimental configuration option trx_stickiness=master disables load balancing if autocommit mode is turned off via API. This makes the mysqlnd library plugin transaction safe. Unfortunately it requires PHP 5.3.99 and, it can be fooled by using SQL to control autocommit. It requires some discipline to use.

Past and today: SQL hints for transactions

By default the PHP replication and load balancing plugin (mysqlnd_ms) is not transaction safe, because it is not transaction aware. The plugin does not know when a transaction starts and when it ends. Thus, it may decide to load balance statements and switch connections in the middle of a transaction. So far, SQL hints have been the only way to prevent connection switches. SQL hints can be used to overrule each and every automatic decision of the plugin.

$mysqli->autocommit(FALSE);
$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (1)", MYSQLND_MS_MASTER_SWITCH));
$mysqli->query(sprintf("/*%s*/INSERT INTO test(id) VALUES (2)", MYSQLND_MS_LAST_USED_SWITCH));
$mysqli->commit();
$mysqli->autocommit(TRUE);

The SQL hints in the example are used to run all of the statements on the master. If the first INSERT would use MYSQLND_MS_SLAVE_SWITCH instead, all statements would be run on one of the slaves. Whatever server you pick for the first statement, is also used for the second INSERT because of the SQL hint MYSQLND_MS_LAST_USED_SWITCH and the COMMIT. The statements are run as a unit of work on the same server.

Future: trx_stickiness=master

With PHP 5.3.99 and the new plugin configuration directive trx_stickiness=master you do not need the SQL hints any more, sometimes. The example below without the SQL hint does exactly the same as the first example from above: it tries to commit a transaction consisting of two INSERT statements on the master.

[myapp]
master[]=localhost:/tmp/mysql.sock
slave[]=192.168.2.27:3306
trx_stickiness=master

$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO test(id) VALUES (1)");
$mysqli->query("INSERT INTO test(id) VALUES (2)");
$mysqli->commit();
$mysqli->autocommit(TRUE);

The mysqlnd library allows plugins to subclass the mysqlnd internal function trx_autocommit() as of PHP 5.3.99. PECL/mysqlnd_ms does overwrite it to monitor the state of the autocommit setting. If trx_stickiness=master and autocommit has been turned off via a user API call, such as $mysqli->autocommit(FALSE), the plugin knows that you want to run transactions. It stops load balancing and switching connections until autocommit is turned on again. In the meantime it executes all statements on the master. Please note that the plugin does not monitor SQL statements which change the autocommit mode, such as SET AUTOCOMMIT=0. The plugins autocommit monitoring is based on the mysqlnd library call trx_autocommit().

Who calls mysqlnd’s trx_autocommit()?

Please, consider trx_stickiness as an experimental feature. It got implemented today and some basic testing has been done. However, applications that need to rely on transactions must not go with "some basic testing". For example, the applications need to know exactly which PHP MySQL API call is using the mysqlnd library call trx_autocommit().

To be on the safe side, you should run your own test. Whenever you are unsure if a user API call invokes trx_autocommit(), which is a requirement for trx_stickiness=master to work properly, wrap the call in two calls of mysqlnd_ms_get_stats(). Let the user call deactivate autocommit. Compare the statistics trx_autocommit_off and trx_autocommit_on before and after the call. If both are unchanged, the user API call must have used a SQL statement to bypass the mysqlnd library. In that case you must not use trx_stickiness=master.

$stats = mysqlnd_ms_get_stats();
printf("trx_autocommit_on= %d\n", $stats['trx_autocommit_on']);
printf("trx_autocommit_off = %d\n", $stats['trx_autocommit_off']);
$mysqli->autocommit(FALSE);
$stats = mysqlnd_ms_get_stats();
printf("trx_autocommit_on= %d\n", $stats['trx_autocommit_on']);
printf("trx_autocommit_off = %d\n", $stats['trx_autocommit_off']);

I’ve added a few more details to the documentation draft. Though, it may take some hours until it appears on the server.

That’s it folks!

My feature whishlist for 1.0.0 is empty. Next is testing and releasing an alpha version.

Feature requests are most welcome. However, I guess some of you want the convenience of a downloadable alpha release to try it out, to find what’s missing.