My RSS » Items by Internet Super Hero

Feeds

100883 items (271 unread) in 22 feeds

CNN

• CNN.com - U.S.
• CNN.com - Technology
• CNN.com - Science & Space

MSNBC (15 unread)

• MSNBC.com: U.S. News (10 unread)
• MSNBC.com: Business
• MSNBC.com: Technology & Science (5 unread)

PHP (62 unread)

• Planet PHP (1 unread)
• Nettuts+ (61 unread)

Deals (157 unread)

• Techbargains.com (43 unread)
• dealnews - 20 most recent deals. (71 unread)
• xpBargains.com (18 unread)
• GottaDeal.com - Why Pay Retail? (25 unread)

Tech

• broadbandreports.com
• BetaNews.Com

Web Development (35 unread)

• Dev Shed - The Open Source Web Development Site
• Dev Articles - Programming Help and Tutorials for all development technologies (35 unread)
• developer.com

CNN Money

• CNNMoney.com
• CNNMoney.com - Economy News
• CNNMoney.com - Personal Finance

Frugal Blogs (2 unread)

• Frugal Dad
• The Simple Dollar (2 unread)

Items by Internet Super Hero

Planet PHP

• 

  Slides: MySQL 5.6 Global Transaction Identifier and PECL/mysqlnd_ms for failover

  Posted: March 13th, 2012, 7:00am MDT by Internet Super Hero

  MySQL 5.6 Global Transaction Identifier - Use case: Failover
  View more presentations on PHP and MySQL

  The long lasting MySQL replication failover issue is cured. MySQL 5.6 makes master failover easy, PECL/mysqlnd_ms assists with the client/connection failover. Compared to the past this is a significant step towards improving MySQL replication cluster availability, eleminating the need to use 3rd party tools in many cases. The slides illustrate the basic idea, as blogged about before.

  There is not much to say about the feature as such. Slave to master promotion works without hassles, finally. Regardless if you do failover because of an error of the current master or switchover because you want to change the master server, its easy now. Congratulations to the replication team!

  Limitations of the current server implementation

  The global transaction identifier implementation in MySQL 5.6 has a couple of limitations, though. Its not hard to guess that mixing transactional and non-transactional updates in one transaction can cause problems. Its pretty much the first pitfall I ran into when trying to setup a MySQL 5.6.5-m8 (not 5.6.4-m8…) slave using a mysqldump generated SQL dump. MySQL bailed at me and stopped me from failing.

  Let a master run a transaction which first updates an InnoDB table. followed by an update of a MyISAM table, followed by another update: t(UInnoDB, UMyISAM, UX). Let the binary log settings be so that this transaction is written as one to the binary log (binlog_format=statement, binlog_direct_non_transactional_updates=0). It is then copied “as is” to the relay log of a slave. Assume that the slave runs with different binary log settings so that t(UInnoDB, UMyISAM, UX) is split up to t(UMyISAM), t(UInnoDB[, …])and logged as distinct transactions in the slaves binary log.

  Worst case: conflicting binary log settings
  Master	Slave
  GTID=M:1	t(UInnoDB, UMyISAM, UX)	GTID=M:1	t(UMyISAM)
  	GTID=M:1	t(UInnoDB[, …])

  Because slaves must preserve global transaction identifiers they got from their master, the two resulting transactions are given the same identifier. The transaction identifier in the slaves binary log is no longer unique, it now refers to two transactions not just one (issue #1). Any slave that would read from the binary log of the above slave may loose the InnoDB transaction because it may refuse to execute a transaction using an id that has been executed already (issue #2).

  The workaround? Don’t mix InnoDB and MyISAM updates in one transaction. To me it does not sound that much of an issue in 2012. Please note, I’m describing my experience with MySQL 5.6.5-m8, which is a development version.

  The load balancer update

  MySQL Replication takes a primary copy approach to replication. A primary/master handles all the updates. Read-only replicas/slaves replicate from the primary. The primary is a single point of failure.

  Writes	Primary/master
  Reads	Slave	"/>

  Truncated by Planet PHP, read more at the original (another 4322 bytes)

• 

  PECL/mysqlnd_ms: MySQL 5.6.4-m8+ global transaction identifier feature supported

  Posted: March 6th, 2012, 6:05am MST by Internet Super Hero

  MySQL Replication is sometimes critizied for being asynchronous and having slaves that lag behind. True! However, sometimes slaves can be used safely and reliably for read-your-writes. Its easy for PHP MySQL users. All the magic is in the driver. As of yesterday, the development version of PECL/mysqlnd_ms 1.3.0-alpha supports not ...
• 

  PECL/mysqlnd_ms: faster slave reads

  Posted: January 31st, 2012, 11:59am MST by Internet Super Hero

  Why read stale data from an asynchronous MySQL replica (slave)? Fetch it from a local cache instead! Good for the clusters overall load, good for your applications performance. And, possible with PECL/mysqlnd_ms 1.3, the replication and load balancing plugin for PHP MySQL users.

  The idea is simple

  Any application using asynchronous MySQL replication must be capable of handling stale results read from a slave (replica) that is lagging behind the master. The quality of service that the application needs from the database cluster is low. If an application explicitly states the minimum service quality it needs, the underlying systems can adopt to it. That’s a cool thing, because the underlying systems don’t need to do more work than necessary. In the case of a PHP MySQL user, the first underlying system is the database driver library, which is mysqlnd.

  Tell mysqlnd that you can deal with data that is as old as five seconds. Then, mysqlnd can search a matching slave lagging no more than five seconds or even replace the slave access with a TTL-based cache access. Cache? Sure, PECL/mysqlnd_qc, various storage backends including main memory, user-defined, APC and Memcache … have a look at the quickstart.

  It exists…

  The first step is done. The development trees of PECL/mysqlnd_qc 1.1 and PECL/mysqlnd_ms 1.3 have been de-stabilized. A first, crude approach to make the query cache and replication plugin work transparently together exists.

  
./configure --enable-mysqlnd-ms --enable-mysqlnd-ms-cache-support


  PECL/mysqlnd_ms 1.2 introduced the mysqlnd_ms_set_qos() function for setting the service level required from the cluster.

   mysqlnd_ms_set_qos($connection,
    MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
    MYSQLND_MS_OPTION_CACHE,
    5);
  

  If you set the service level as shown above, PECL/mysqlnd_ms does never read from a slave that reports itself to be lagging more than 5 seconds behind the master. If no matching slave is found, PECL/mysqlnd_ms picks the master. Additionally, PECL/mysqlnd_ms tries to cache all slave queries for up to 5 seconds. The cache logic is so, that you never get data older than 5 seconds.

  The initial cache logic

  First, PECL/mysqlnd_ms asks PECL/mysqlnd_qc if the query is already in the cache. If so, PECL/mysqlnd_ms stops searching slaves, continues working and tries to fetch the results from the cache in the following. If fetching from cache fails, which should happen rarely, it reads the result from a master.

  In case the query is not cached yet, PECL/mysqlnd_ms searches for all slaves that lag no more than 5 seconds. The cache TTL is reduced by the highest lag found. If, for example, there are two slaves, lagging 2 and 3 seconds behind, the cache TTL is set to 5 - max(2, 3) = 5 - 3 = 2 seconds.

  	MYSQLND_MS_OPTION_CACHE	Slave lag	TTL
  Slave 1	5	2	3
  Slave 2	5	3	2

  Then, slave selection continues, for example, load balancing is done. At the end of the chain eventually one slave has been selected. The query is run on the slave and put into the cache for 2 seconds.

  How the plugins work together

  PECL/mysqlnd_ms does control the cache, PECL/mysqlnd_qc, through SQL hints. PECL/mysqlnd_ms sets exactly the same SQL hints that are also available to the user, as described in the PECL/mysqlnd_qc manual.

  /*qc=on*//*qc_ttl=2*/SELECT id FROM test
  

  … and tomorrow: a sneak preview of the new built-in pattern based caching for PECL/mysqlnd_qc 1.1.

  Happy hacking!

  @Ulf_Wendel 

• 

  PHP mysqlnd query cache plugin quickstart is online!

  Posted: January 13th, 2012, 1:41pm MST by Internet Super Hero

  New in the PHP manual: a quickstart for the mysqlnd query cache plugin. PECL/mysqlnd_qc, the mysqlnd query cache plugin, is transparent and ease to use. But, how? Some pointers have been given in assorted presentations, here on my blog and in some, few examples from the manual. Fixed. You ...
• 

  PECL/mysqlnd_*: CCC - cloud, cluster, caching!

  Posted: January 11th, 2012, 12:00pm MST by Internet Super Hero

  We are giving PECL/mysqlnd_qc a second chance. PECL/mysqlnd_qc is a query cache plugin for mysqlnd. It can cache any query issued by any PHP MySQL extension using storage handler for process memory, APC, Memcache and SQLlite. Its default invalidation strategy is Time to Live (TTL). Using a more sophisticated invalidation strategy is possible. Of course, its transparent to use and inherits all the other advantages of a driver based approach.

  Award-winning technology: Oxid loves the query cache
  View more presentations on PHP and MySQL

  Cloud and Cluster

  Albeit quite powerful the plugin came late and is faced with stiff competition from application-based solutions. With everybody talking about cloud and database clusters, there is a new use case for client-side caching.

  No all-in-one cluster solution for all purposes exsits. MySQL users, for example, can choose between MySQL Replication, MySQL Cluster and an emerging number of third party solutions, which proof MySQL to be alive and rocking. The CAP theorem makes me assume that MySQL users will continue to have multiple choices.

  Whatever cluster solution will become the dominating force, applications should be enabled to see a cluster as a service. Accordingly, PECL/mysqlnd_ms 1.2 has introduced an API to set the required service level. In version 1.2 the service level option focusses on consistency:

  • Eventual consistency - stale data allows, e.g. when reading from a MySQL replication slave
  • Session consistency - read your writes
  • Strong consistency - every client sees all writes

  Whatever type of cluster is used, PECL/mysqlnd_ms will try to deliver the requested consistency by selecting appropriate nodes.

  Caching

  If the application hints the database driver that eventual consistency is sufficient but data returned shall not be more than five seconds behind, the database driver can replace a slow, eventually remote database access with a fast, local cache access. That’s the second chance PECL/mysqlnd_qc will be given.

  PHP application
  Service level: eventual consistency, max 5 seconds old
  Any PHP MySQL extension (mysqli, PDO_MySQL, mysql)
  mysqlnd library
  PECL/mysqlnd_ms	–>	PECL/mysqlnd_qc
  		|	or	|
  		Process memory, Memcache, …		|
  				MySQL

  Documentation updates

  Before we begin with it, I have given the PECL/mysqlnd_qc a second chance as well. You can expect to find an improved Quickstart and Examples section at [docs.php.net] soon. Have a look during the next days, its a cool plugin thing even without the cloud buzzword bingo…

  Happy New Year and happy hacking!

  @Ulf_Wendel

  PHP 5.4 and APC notes

  PECL/mysqlnd_qc 1.0.1 is not compatible with PHP 5.4. It has not been updated to the latest mysqlnd API as found in PHP 5.4. Andrey and I have prepared a patch and hope to push a 1.0.2 release soon. Unfortunately, the (private) C API of APC has also changed in the past year. Thus, the cache cannot use a recent APC version for storing cache entries. Of course, you can still use the latest version of APC to boost your PHP scripts even if the scripts use the query cache plugin.

• 

  Load balancing for PHP and MySQL

  Posted: December 23rd, 2011, 3:04am MST by Internet Super Hero

  A single MySQL server is a single point of failure. A single MySQL server can only be scaled vertically by increasing hardware size, which has its limits. That’s two good reasons to migrate from a single MySQL server to a cluster of MySQL servers. However, in cloudy white christmas times, few appreciate the extra work that using a cluster causes. For example, MySQL connections must be load balanced. Please, find a comparison of different load balancing architectures in the short presentation. Choose the one that’s best for you - maybe it is PECL mysqlnd_ms 1.2, the mysqlnd replication and load balancing plugin…

  Load Balancing for PHP and MySQL
  View more presentations on mysqlnd

  Seasons greetings!

  @Ulf_Wendel

• 

  PECL/mysqlnd_ms global transaction ID injection status

  Posted: November 29th, 2011, 11:25am MST by Internet Super Hero

  Baby PECL/mysqlnd_ms 1.2.0-alpha, the PHP MySQL replication and load balancing plugin for mysqlnd, has done its first steps into the direction of global transaction id injection: injection happens, IDs can be queried. A simulated global transaction ids can be described as a transaction counter in a table on ...