Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 61 to 90 of 205 Next 30 Older Entries

Displaying posts with tag: PlanetPHP (english) (reset)

PECL/mysqlnd_ms: transaction aware load balancing, sort of
Employee +0 Vote Up -0Vote Down

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.

  [Read more...]
PHP replication and load balancing plugin: master_on_write, documentation
Employee +2 Vote Up -0Vote Down

Hours ago, I have committed the first documentation draft for PECL/mysqlnd_ms. The mirrors should show it on Saturday. PECL/mysqlnd_ms is a replication and load balancing plugin for the mysqlnd library. Its latest feature, master_on_write, helps to work around the issue of replication lag. If master_on_write=1, the plugin stops load balancing statements after the first non read-only statement has been run by a PHP script. Instead of running read-only statements on the slaves, the plugin runs all statements on the master. This is done to ensure that the client will always read the latest data, even the data just written and not yet replicated to the slaves. Thank you,

  [Read more...]
PHP replication and load balancing plugin: master_on_write, documentation
Employee +0 Vote Up -0Vote Down

Hours ago, I have committed the first documentation draft for PECL/mysqlnd_ms. The mirrors should show it on Saturday. PECL/mysqlnd_ms is a replication and load balancing plugin for the mysqlnd library. Its latest feature, master_on_write, helps to work around the issue of replication lag. If master_on_write=1, the plugin stops load balancing statements after the first non read-only statement has been run by a PHP script. Instead of running read-only statements on the slaves, the plugin runs all statements on the master. This is done to ensure that the client will always read the latest data, even the data just written and not yet

  [Read more...]
PECL/mysqlnd_ms: let web requests be your M/S load balancer
Employee +5 Vote Up -0Vote Down

Introducing the best load balancing policy for PECL/mysqlnd_ms: "random_once". A (PHP) web request is short-lived. A web server can handle multiple requests per second. There are many web servers running an application. For each web request choose a random slave to use for the rest of the request. Don’t do load balancing, don’t switch connections during a web request . Result: even load distribution, less connection overhead and less connections overall, cache loves you, less hassle switching stateful slave connections…

Load balancing policies featured by PECL/mysqlnd_ms

As of today, PECL/mysqlnd_ms, our PHP mysqlnd toy plugin for replication load balancing, features four built-in load balancing policies:

  • random: for each
  •   [Read more...]
    PECL/mysqlnd_ms: let web requests be your M/S load balancer
    Employee +0 Vote Up -0Vote Down

    Introducing the best load balancing policy for PECL/mysqlnd_ms: "random_once". A (PHP) web request is short-lived. A web server can handle multiple requests per second. There are many web servers running an application. For each web request choose a random slave to use for the rest of the request. Don’t do load balancing, don’t switch connections during a web request . Result: even load distribution, less connection overhead and less connections overall, cache loves you, less hassle switching stateful slave connections…

    Load balancing policies featured by PECL/mysqlnd_ms

    As of today, PECL/mysqlnd_ms, our PHP mysqlnd toy plugin for replication load balancing, features four built-in

      [Read more...]
    What if PECL/mysqlnd_ms does not implement failover?
    Employee +2 Vote Up -0Vote Down

    Implicit and automatic connection failover done by the driver is broken by design, correct? Should PECL/mysqlnd_ms implement automatic failover? Today Johannes showed me an IRC snippet about TAF (Transparent Application Failover). Maybe, I fail miserably but I don’t see a need for automatic failover in a master slave replication loadbalancing plugin. Let the application do it. In the end, this is also what TAF does - via a callback.

    Say you run a query. The master slave load balancing plugin recognizes that it is a read query, a SELECT. The plugin picks a server for query execution, for example, the first of the configured slaves. The plugin sends the query to the slave but the slave is not

      [Read more...]
    What if PECL/mysqlnd_ms does not implement failover?
    Employee +0 Vote Up -0Vote Down

    Implicit and automatic connection failover done by the driver is broken by design, correct? Should PECL/mysqlnd_ms implement automatic failover? Today Johannes showed me an IRC snippet about TAF (Transparent Application Failover). Maybe, I fail miserably but I don’t see a need for automatic failover in a master slave replication loadbalancing plugin. Let the application do it. In the end, this is also what TAF does - via a callback.

    Say you run a query. The master slave load balancing plugin recognizes that it is a read query, a SELECT. The plugin picks a server for query execution, for example, the first of the configured slaves. The plugin sends

      [Read more...]
    PHP: Application calling plugin - on communication
    Employee +4 Vote Up -0Vote Down

    Imagine PECL/mysqlnd_ms could keep certain reads on certain slaves in a MySQL master slave setup. You would have a good chance to profit from hot database caches. You would not only distribute read load, scale out horizonally and so forth due to using MySQL replication. You would also optimize your reads for hot caches.

    PECL/mysqlnd_ms is our master slave replication load balancing proof-of-concept mysqlnd plugin playground (see also previous blog posts). It allows you to define a callback which is responsible for picking the server that shall run a query. That userspace callback could be doing the read redirection optimization for hot caches.

    The eye-catcher: hot cache optimization

    (pecl_mysqlnd_ms_config.ini)

    [myapp]

      [Read more...]
    PHP: Application calling plugin - on communication
    Employee +0 Vote Up -0Vote Down

    Imagine PECL/mysqlnd_ms could keep certain reads on certain slaves in a MySQL master slave setup. You would have a good chance to profit from hot database caches. You would not only distribute read load, scale out horizonally and so forth due to using MySQL replication. You would also optimize your reads for hot caches.

    PECL/mysqlnd_ms is our master slave replication load balancing proof-of-concept mysqlnd plugin playground (see also previous blog posts). It allows you to define a callback which is responsible for picking the server that shall run a query. That userspace callback could be doing the read redirection optimization for hot caches.

    The eye-catcher: hot cache optimization

      [Read more...]
    PHP: Master slave load balancing in mysqlnd - how transparent?
    Employee +4 Vote Up -0Vote Down

    How transparent does master slave load balancing support for mysqlnd have to be? Transparency, when driven to the extremes, has three limiting factory: power users not needing it, increase of messages send to MySQL, time intensive SQL monitoring and parsing. The PECL/mysqlnd_ms proof-of-concept mysqlnd plugin is almost transparent to make it easy to use. Almost because there are some design limits. Please let us know how you rate the severity of these limits. For example, can we ignore transactions? Your feedback on the previous blog post has been most helpful - thanks!

    Give me four paragraphs, one minute, for the introduction and background.

      [Read more...]
    PHP: Master slave load balancing in mysqlnd - how transparent?
    Employee +0 Vote Up -0Vote Down

    How transparent does master slave load balancing support for mysqlnd have to be? Transparency, when driven to the extremes, has three limiting factory: power users not needing it, increase of messages send to MySQL, time intensive SQL monitoring and parsing. The PECL/mysqlnd_ms proof-of-concept mysqlnd plugin is almost transparent to make it easy to use. Almost because there are some design limits. Please let us know how you rate the severity of these limits. For example, can we ignore transactions? Your feedback on the previous blog post has been most helpful - thanks!

    Give me four

      [Read more...]
    PHP: … want transparent master slave load balancing in mysqlnd?
    Employee +9 Vote Up -0Vote Down

    Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

    Start small…

    Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed

      [Read more...]
    PHP: … want transparent master slave load balancing in mysqlnd?
    Employee +0 Vote Up -0Vote Down

    Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

    Start small…

    Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL

      [Read more...]
    PHP: Transparent load balancing and sharding with mysqlnd
    Employee +4 Vote Up -0Vote Down

    Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

    The goal

    Round-robin connection load balancing

    Client   Server connect(‘Host A’) mysqlnd plugin
    30+ lines PHP ->
      [Read more...]
    PHP: Transparent load balancing and sharding with mysqlnd
    Employee +0 Vote Up -0Vote Down

    Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

    The goal

    Round-robin connection load balancing

    Client   Server connect(‘Host A’[Read more...]
    PHP: the 38 characters of the mysqlnd profiler
    Employee +2 Vote Up -0Vote Down

    Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

    The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

    • extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
    • dump
      [Read more...]
    PHP: the 38 characters of the mysqlnd profiler
    Employee +0 Vote Up -0Vote Down

    Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

    The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

    • extra statistics for critical sections in the plugin using the mysqlnd
      [Read more...]
    PHP: 62 characters to see all MySQL queries
    Employee +1 Vote Up -0Vote Down

    Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

    • Get PHP 5.3.4-dev
    • Get PECL/mysqlnd_qc
    • Add 19 characters to your configure line: --enable-mysqlnd-qc
    • Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
    • Start your query analysis

    
    array(329) {
      [0]=>
      array(8) {
        ["query"]=>
        string(27) "SET @@session.sql_mode = """
        ["origin"]=>
        string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352): mysql_query('SET @@session.s...',

      [Read more...]
    PHP: 62 characters to see all MySQL queries
    Employee +0 Vote Up -0Vote Down

    Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

    • Get PHP 5.3.4-dev
    • Get PECL/mysqlnd_qc
    • Add 19 characters to your configure line: --enable-mysqlnd-qc
    • Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
    • Start your query analysis

    
    array(329) {
      [0]=>
      array(8) {
        ["query"]=>
        string(27) "SET @@session.sql_mode = """
        ["origin"]=>
        string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352):

      [Read more...]
    The mysqlnd query cache meets Oxid at the IPC 2010
    Employee +2 Vote Up -0Vote Down

    How amusing: an ancient write-up on the first PHP Kongress in 2000 edition written by one who later became a mentor of the autor of phpOpenTracker. How boring: a silver guy talking at the 10th aniversary of the conference about some 60% performace benefit for Oxid eShop, an example of a modern award-winning software, if using the mysqlnd query cache plugin (PECL/mysqlnd_qc). How confusing: the same silver guy calls his own benchmarks irrelevant and faulty.

    Award-winning technology: Oxid loves the query cache

    View more presentations on mysqlnd and its

      [Read more...]
    The mysqlnd query cache meets Oxid at the IPC 2010
    Employee +0 Vote Up -0Vote Down

    How amusing: an ancient write-up on the first PHP Kongress in 2000 edition written by one who later became a mentor of the autor of phpOpenTracker. How boring: a silver guy talking at the 10th aniversary of the conference about some 60% performace benefit for Oxid eShop, an example of a modern award-winning software, if using the mysqlnd query cache plugin (PECL/mysqlnd_qc). How confusing: the same silver guy calls his own benchmarks irrelevant and faulty.

    Award-winning technology: Oxid loves the query cache

    View more presentations on

      [Read more...]
    PHP @ FrOSCon: the power of mysqlnd plugins
    Employee +1 Vote Up -0Vote Down

    Slowly the power of mysqlnd plugins becomes visible. Mysqlnd plugins challenge MySQL Proxy and are often a noteworthy, if not superior, alternative alternative to MySQL Proxy for PHP users. Plugins can do almost anything MySQL Proxy can do - but on the client. Please find details in the slides. The presentation has been given today at the PHP track at FrOSCon.

    The power of mysqlnd plugins

    View more presentations on mysqlnd.

    The biggest news is certainly the work from David



      [Read more...]
    PHP @ FrOSCon: the power of mysqlnd plugins
    Employee +0 Vote Up -0Vote Down

    Slowly the power of mysqlnd plugins becomes visible. Mysqlnd plugins challenge MySQL Proxy and are often a noteworthy, if not superior, alternative alternative to MySQL Proxy for PHP users. Plugins can do almost anything MySQL Proxy can do - but on the client. Please find details in the slides. The presentation has been given today at the PHP track at FrOSCon.

    The power of mysqlnd plugins

    View more presentations on mysqlnd.

    The biggest news is certainly the work from



      [Read more...]
    PHP: Client side caching for all MySQL extensions
    Employee +7 Vote Up -0Vote Down

    The first public mysqlnd plugin adds client side query result caching to all MySQL extensions of PHP (ext/mysql, ext/mysqli, PDO_MySQL). The cache is written in C. It does not change any of the PHP MySQL APIs and works with any PHP application using MySQL. Query results are stored on the client. Cached data can be stored in main memory, APC, Memcache, SQLite (theoretically - via SQLite wrapper -

      [Read more...]
    PHP: Client side caching for all MySQL extensions
    Employee +0 Vote Up -0Vote Down

    The first public mysqlnd plugin adds client side query result caching to all MySQL extensions of PHP (ext/mysql, ext/mysqli, PDO_MySQL). The cache is written in C. It does not change any of the PHP MySQL APIs and works with any PHP application using MySQL. Query results are stored on the client. Cached data can be stored in main memory, APC, Memcache, SQLite (theoretically - via SQLite wrapper -

      [Read more...]
    PHP BBQs 2010 - Karlsruhe today, Frankfurt 27th
    Employee +0 Vote Up -0Vote Down

    Pretty much exactly a year ago many german PHP meetups have held BBQs to celebrate the summer, to invite guests to join their meetups in a very relaxed atmosphere and to enjoy themselves. During the PHP BBQ week 2009 more than 140 people got connected and had fun . And, of course, I loved visiting all the meetups. I am not touring through the user groups this year. However, and that is fantastic news, some user groups have organized BBQs again. Today, June 22th - 19:00, you can enjoy a BBQ in Karlsruhe organized by the PHP user group Karlsruhe and sponsored by urlaubswerk.de! Check their homepage for the latest details.

    KIT Campus Süd – Center für Innovation & Entrepreneurship (CIE)
    CIE Cube

      [Read more...]
    PHP BBQs 2010 - Karlsruhe today, Frankfurt 27th
    Employee +0 Vote Up -0Vote Down

    Pretty much exactly a year ago many german PHP meetups have held BBQs to celebrate the summer, to invite guests to join their meetups in a very relaxed atmosphere and to enjoy themselves. During the PHP BBQ week 2009 more than 140 people got connected and had fun . And, of course, I loved visiting all the meetups. I am not touring through the user groups this year. However, and that is fantastic news, some user groups have organized BBQs again. Today, June 22th - 19:00, you can enjoy a BBQ in Karlsruhe organized by the PHP user group Karlsruhe and sponsored by urlaubswerk.de! Check their homepage for the latest details.

    KIT Campus Süd – Center für Innovation &

      [Read more...]
    mysqlnd plugins: alternative to MySQL Proxy ?!
    Employee +5 Vote Up -0Vote Down

    The mysqlnd plugin API is a well hidden gem of mysqlnd. Mysqlnd plugins operate on a layer between PHP applications and the MySQL server. This is comparable to MySQL Proxy. MySQL Proxy operates on a layer between any MySQL client application, for example, a PHP application and, the MySQL server. Plugins can take over classical MySQL Proxy tasks such as Load Balancing, Monitoring and Performance optimizations. But due to the different architecture and location mysqlnd plugins do not share some common MySQL Proxy annoyances: no single point of failure, no dedicated proxy server to deploy, no new programming language to learn (Lua).

    Slides from the IPC Spring conference

    This blog posting and today’s presentation at the IPC Spring

      [Read more...]
    mysqlnd plugins: alternative to MySQL Proxy ?!
    Employee +0 Vote Up -0Vote Down

    The mysqlnd plugin API is a well hidden gem of mysqlnd. Mysqlnd plugins operate on a layer between PHP applications and the MySQL server. This is comparable to MySQL Proxy. MySQL Proxy operates on a layer between any MySQL client application, for example, a PHP application and, the MySQL server. Plugins can take over classical MySQL Proxy tasks such as Load Balancing, Monitoring and Performance optimizations. But due to the different architecture and location mysqlnd plugins do not share some common MySQL Proxy annoyances: no single point of failure, no dedicated proxy server to deploy, no new programming language to learn (Lua).

    Slides from the IPC Spring conference

    This blog posting and today’s presentation at the

      [Read more...]
    PHP/C: Does MYSQL[I]_OPT_CONNECT_TIMEOUT work?
    Employee +1 Vote Up -0Vote Down

    C and PHP MySQL clients can set a connection timeout before a connection is established to MySQL. The MySQL C API manual states about MYSQL_OPT_CONNECT_TIMEOUT , which is equal to PHPs MYSQLI_OPT_CONNECT_TIMEOUT:

    MYSQL_OPT_CONNECT_TIMEOUT

    Connect timeout in seconds.


    From: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html

    That is half of the story. The actual behaviour depends on the library you use, the operating system and the transport protocol (TCP/IP, Unix domain sockets, Windows named pipes, Shared Memory).

    C users can choose between the MySQL Client Library, which ships with the MySQL server, and the





      [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 90 of 205 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.