pecl:mysqlnd_ms

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
pecl:mysqlnd_ms [2011/11/28 11:58] – [Development steps (release planning)] uwpecl:mysqlnd_ms [2017/09/22 13:28] (current) – external edit 127.0.0.1
Line 116: Line 116:
   * [open] Documentation update   * [open] Documentation update
  
-=== 1.2 - Global Transaction ID injection ===+=== 1.2 - Global Transaction ID injection and quality-of-service concept  ===
  
 +== Coding/Features ==
  
 +  * [done] Introduce quality-of-service (QoS) filter. Service levels provided by QoS filter:
 +    * [done] eventual consistency, optional option slave lag
 +    * [done] session consistency, optional option GTID
 +    * [done] strong consistency
  
-=== 1.3 - partitioned data === +  * [done] Allow setting connection quality at run time 
- +  * [done] Client-side global transaction ID injection (GTID) 
-== Coding/Features == +  * [done] Support fetching of most recent GTID
-  +
-  * [done] develop new JSON-based plugin config syntax +
-    * [ongoing] support definition of replication setups including schemata based partitioning +
-  * [open] make load balancer schemata aware +
-    * for each statement detect database/table +
-    * follow schemata based partitioning rules  +
-    * basic idea: http://blog.ulf-wendel.de/?p=310 +
-  * [done] improve trx_stickiness using MYSQL*/MYSQLND* in_transaction flag +
-    * cancelled: http://blog.ulf-wendel.de/?p=313 - No protocol flag for transaction aware load balancing +
-  * [open] bail on unknown config settings. For example, bail if someone uses round_robin instead of roundrobin +
  
 == QA ==  == QA == 
  
-  * [open] >= 90% code coverage through tests +  * [done] 85-90% code coverage through tests
-  * [open] write report on parsing overhead, goal: <5% in worst case micro benchmarks+
  
 == Documentation, Blogs, Articles etc. == == Documentation, Blogs, Articles etc. ==
  
-  * [done] impact of connection limits such as allow_persistent, max_persistent, max_links+  * [done] Documentation update 
 +  * [done] Load balancing for PHP MySQL presentation   
 +  * [done] Assorted blog postings on GTID and QoS
  
 +=== 1.3 - Query caching through quality-of-service concept  ===
  
-==== Raw Bin ideas (RFCs) ====+== Coding/Features ==
  
-If you got any feature proposal or, if you have any replication related client-side task which you want to see solved, please, describe and add it here. Use the existing entries as a pattern.+  * [done] Introduce QoS filter for caching 
 +  * [done] Update GTID related QoS logic for MySQL 5.6
  
-=== Idea === +== QA ==
-== Problem to solve / Idea == +
-== Feature description ==+
  
-=== Eventual consistent: automatic chaining of PECL/mysqlnd_qc query cache === +  * [done] 85-90% code coverage through tests
-== Problem to solve / Idea ==+
  
-MySQL replication is asnychronous. Slaves are //eventual consistent// because there is a time lag between an update on the master and the ability of all clients to read that update from the slaves. Slaves may serve stale data.+== Documentation, Blogs, Articles etc. ===
  
-MySQL replication used for read scale-out requires applications to be able to cope with stale data. If stale data is acceptable, a replication system may replace a MySQL slave read access with an access to a local, eventually stale, cache. **The cache access will lower network latency resulting in faster reply to the query. Furthermore, it will reduce the load on the MySQL replication slaves, thus requiring less machines for a given read workload.**+  * [done] Documentation update 
 +  * [done] Presentations on GTID and MySQL 5.6 
 +  * [done] Assorted blog postings
  
-For certain queries, PECL/mysqlnd_ms shall automatically populate a local cache and fetch results from the cache. PECL/mysqlnd_ms shall use PECL/mysqlnd_qc, if possible, in a transparent way.+=== 1.4 - Tweaking based on user feedback  ===
  
-(MySQL semi-synchronous replication does not change the matter as it requires only one slave to have received but not yet applied the update.)+== Coding/Features ==
  
-**PECL/mysqlnd_qc supports Memcache, APC and other storage media.**+  * [done] More fail over options 
 +    * [done] Automatic on-connect fail over, if activated, shall be done in a loop until a connection can be opened. Currently we stop after the first attempt. If automatic fail over is on, we try "next" and in case of a failure of "next" we stop. With the new logic there shall be no stop until all possible "next" have been tried. 
 +    * [done] Remember failed hosts for the duration of a web request (the plugins' typical lifespan)
  
-== Feature description ==+  * [done] Refine QoS session consistency server selection policy 
 +    * [done] Support "wait for GTID". Currently we loop over all servers until we find a matching one. MySQL 5.6 allows SQL users either to fetch the latest GTID or SQL users can ask for a GTID and their request will block until the GTID has been replicated on the server. We should support the latter logic as well.
  
-Applications shall be able to define a "quality of service" (the term is borrowed from Tungsten) for a statement, a connection or globally. The "quality of service" shall be guaranteed by PECL/mysqlnd_ms. The service level defines if //eventual consistency// is allowed:+  * [done] Improve load balancing 
 +    * [done] Allow users to set a ratio/weight for query distribution. There may be machines in a cluster/config which can handle twice as many requests as others. Such machines shall be given a weight of, for example, two to indicate that the load balancer shall direct twice as many requests to them as to a machine with the weight of one. 
 +  
 +== QA ==
  
-  * [RW_]CONSISTENT +  * [done] 85-90% code coverage through tests
-  * [RO_]EVENTUAL_CONSISTENT+
  
-With CONSISTENT every statement shall be run on the master to avoid stale data. EVENTUAL_CONSISTENT allows the use of slaves and thus, stale, non-consistent data may be read.+== Documentation, Blogs, Articles etc. ===
  
-PECL/mysqlnd_ms supports the logic of service levels already as of 1.0.0, but does not call them that way. PECL/mysqlnd_ms features SQL hints (USE_MASTER) to enforce the use of the master (CONSISTENT), the use of the last used server (USE_LAST_USED) for consistent reads from slaves (CONSISTENT) and has an ini-setting (master_on_write) to automatically use only the master after the first write (CONSISTENT). It also features the concept of EVENTUAL_CONSISTENT which is the default operation mode.+  * [done] Update documentation 
 +  * [done] Assorted blog postings
  
-However, PECL/mysqlnd_ms does not call it "quality of service" and has not extra API calls for setting the service level. It may be convenient to introduce an extra API call, which removes the need of having to prefix statements with SQL hint. Prefixing is inconvenient.+=== 1.5 - Tweaking based on user feedback  ===
  
-  /* Force use of master - consistent read although no SQL hint is used */ +Work has not started yet.
-  mysqlnd_ms_set_service_level($link, CONSISTENT); +
-  $link->query("SELECT ..."); +
-  $link->query("SELECT ..."); +
-   +
-  /* Use master or slaves for reads - allow read stale, non-consistent data is acceptable */   +
-  mysqlnd_ms_set_service_level($link, EVENTUAL_CONSISTENT); +
-  $link->query("SELECT ..."); +
-   +
-The service level EVENTUAL_CONSISTENT can be combined with automatic, local caching. It shall be configurable if and for which queries caching shall be used. Configuration can, for example, be done via the PECL/mysqlnd_ms plugin configuration file. It may be also useful to introduce a EVENTUAL_CONSISTENT_CACHE service level to control caching via API call.+
  
-If PECL/mysqlnd_ms runs in caching mode, it shall automatically populate and read from the PECL/mysqlnd_qc query cache. PECL/mysqlnd_qc is TTL based. The TTL could either be set manually via PECL/mysqlnd_ms function call, globally in the PECL/mysqlnd_ms configuration file or automatically derived from SHOW SLAVE STATUS Seconds_Behind_Master. +== Coding/Features ==
  
-Use and integration of PECL/mysqlnd_qc shall be as transparent as possible from an applications point of view. For example, PECL/mysqlnd_ms shall inject all SQL hints required to control caching by PECL/mysqlnd_qc.+  * [open] More fail over options 
 +    * [open] Silent and automatic connection fail over if server returns configured error code 
 +      * NOTE: this may require refactoring of four filters.  
 +    * [open] Add failover timeout and failover (maximum) attempts limits/configuration settings
  
 +  * [open] Support directing statements manually to a group of nodes for more efficient server cache usage
 +    * [open] Remember the most current server and test this one first when searching for a GTID (a synchronous server). Use of cached information is possible for the duration of a read-only request sequence. The cache must be flushed and refreshed for every write.
  
-=== Global Transaction ID === +  * [open] Work with php.net Windows team to provide PECL/mysqlnd_ms windows builds 
-== Problem to solve / Idea ==+    * Ensure the code builds 
 +    * Encourage Windows team to create builds 
 +  
 +== QA ==
  
-In the scope of MySQL master - slave replication global transaction IDs can be understood as a measure to identify a database state. Replicas/slaves can use global transaction ids to identify change sets (from the master) that need to be applied. Clients can use global transaction ids to monitor the replication status of the replicas in the database cluster.+  * [open] 85-90% code coverage through tests 
 +  * [open] Cross-platform testing on all major MySQL build hosts
  
-The MySQL built-in replication does not feature global transaction IDs. MySQL replication uses transaction log files and log file positions to identify change sets that replicas (slaves) need to apply. Log file and log file position are local to a machine. A tuple of (log file, log file position) does not uniquely identify a change set in the whole cluster.+== Documentation, Blogs, Articles etc. ===
  
-If, for example, your setup is A -> B -> C (A is the master, B is a slave reading from A, C is a slave reading from B) and B fails you are faced with the challenge to identify the log file and log file position on A from which C shall continue to read and apply change sets. C cannot continue reading from the same (log file, log position) as it was reading from B. The tuple (log file=2, log position=17) on B can identify a different change set than the tuple (log file=2, log position=17) on A. C might get out of sync, loose change sets or apply them multiple times, if it would blindly continue reading from the same position on A. +  * [open] Update documentation 
 +  * [open] Assorted blog postings
  
-Today, it can become a bit of a time consuming guessing game to identify the next change set that B needs to process in this fail-over example.  
  
-Fail-over and circular-replication is much easier to do if the database cluster maintains a global transaction ID. +==== Raw Bin ideas (RFCs) ====
  
-The plugin shall inject SQL to manually maintain a global transaction ID on the master. +If you got any feature proposal or, if you have any replication related client-side task which you want to see solved, please, describe and add it here. Use the existing entries as a pattern.
  
 +=== Idea ===
 +== Problem to solve / Idea ==
 == Feature description == == Feature description ==
  
-Global transaction IDs can be used for multiple purposes:+=== More fail over configuration options === 
 +== Problem to solve / Idea ==
  
-  * Simplify server fail-over (server-side, not client-side)+Whenever the plugin tries to connect to a node it may fail to do so. Connection attempts can be made when opening a connection or later, when executing a statement (lazy connections). By default the plugin bails out if the connection attempt fails. To make using the plugin as transparent as possible it is possible to optionally enable automatic fail over in case of failed connection attempts.
  
-  * Monitor slave replication status (client-side, plugin relevant) +The fail over logic itself is basic. Smaller improvements will make it much more capable.
-    * Identify "up-to-date" slave for "synchronous" reads +
-    * Identify slaves which are "recent enough" (quality of service) +
-    * Let the load balancer skip slaves which have fallen to far behind to reduce their load+
  
-The plugin shall support using a global transaction ID for:+== Feature description ==
  
-  - server fail-over (e.g. promoting new master after master failure)  
-  - selection of "recent enough" (quality of service) slaves for reading 
  
-Gobal transaction ID generation shall be done by SQL injection. The plugin shall insert SQL at the end of each (write) transaction (see http://jan.kneschke.de/projects/mysql/mysql-proxy-and-a-global-transaction-id/) on the master. The inserted SQL shall generate an ID which helps with the desired use cases.+Automatic fail over is basic. We shall:
  
-The SQL used for globals transaction ID generation can be different for the different purposes. It may be good enough to inject SET @_g_trx_id=UUID_SHORT() on the master before a SQL transaction commits to help with fail-over. Whereas the selection of slaves of a certain state ("up-to-date, "recent enough") may require maintaining a sequence number or a tuple of server-id and sequence number. Cluster topology and replication method (statement based, row based, mixed). + - make it configurable whether fail over node search tries one or all possible alternatives 
 + - link fail over to certain error codes 
 + - remember failed hosts to skip them for the rest of the web request
  
-Thus, the user shall be able to configure:+Up to version 1.3 the automatic fail over stops after trying one alternative. For example, if a connection to A fails we try B. If connecting to B fails we stop and bail out. In that case the user must handle the error although there may be node C and D, ... which could be used. In the future the search for an alternative shall not stop after B but continue until a connection has been established or there are no alternatives. If a connect to A fails, the plugin shall try B, C, D and so forth. Automatic fail over, automatic connection attempts shall not stop after trying B and failing to connect to B. 
  
-  * SQL used for ID generation +Whether this shall become a new default or become configurable is to be decided.
-  * SQL used to retrieve ID +
  
-If no SQL is given to retrieve the ID, the plugin does not feature '2) selection of "recent enough" (quality of service) slaves for reading'. If given, the user can restrict the choice of slaves for reading by setting a minimum global transaction id which has to be already replicated on the slaves. +Furthermore, it shall be possible to link the automatic fail over to certain error codes: if A returns error n, try next. If A returns m, stop the search and bail out. This way we can differentiate between a server that rejects new requests, e.g. because its overloaded, and a server that has failed.
  
-This is useful if, for example, an application does a write and needs to read the modified data "immediately" after the write. Not all slaves my have replicated the write "immediately". Thus, the read can only be performed on the master or some, selected slaves which have replicated the changes already. Using a global transaction id it is possible to identify those slaves.+Upon request the plugin will remember failed nodes for the duration of a web request. If connecting to a node has failed once no further attempts will be made to connect to the node. This may lead to situations where nodes are skipped although they became available again in the meantime. This is ignore because most web requests are short-lived.
  
-  /* write to master */ +=== Support directing statements manually to a group of nodes for more efficient server cache usage ===
-  $link->query("INSERT ..."); +
-  $write_trx_id = mysqlnd_ms_get_last_global_transaction_id(); +
-   +
-  /* Force use of master - consistent read although no SQL hint is used */ +
-  mysqlnd_ms_set_service_level($link, CONSISTENT); +
-  $link->query("SELECT ..."); +
-  $link->query("SELECT ..."); +
-    +
-  /* Read from a slave which has replicated global transaction ID or, if no slave found, use master */ +
-  mysqlnd_ms_set_service_level($link, MIN_GLOBAL_TRX_ID, $write_trx_id); +
-  $link->query("SELECT ..."); +
-  $link->query("SELECT ..."); +
-   +
-  /* Use master or slaves for reads - allow read stale, non-consistent data is acceptable */   +
-  mysqlnd_ms_set_service_level($link, EVENTUAL_CONSISTENT); +
-  $link->query("SELECT ...");+
  
-The global transaction ID feature of the plugin is turned off by default. The feature is seen as an experts topic. 
- 
-=== mysqlnd_ms_change_user() === 
 == Problem to solve / Idea == == Problem to solve / Idea ==
  
-Give users a way to overrule all automatic decisions, including the use of host credentials from the config when broadcasting COM_CHANGE_USER after mysqlnd_change_user(master_user, master_pass, master_db).+In large clusters users can improve performance by optimizing query distribution and server selection using criteria such as cache usage, distance or latency. Application developers shall be allowed to annotate a statement in a way that its executed on a certain group of nodes.  
 + 
 +Server cache usage can be optimized by distributing queries in a way that they hit hot caches. For example, clients may want to run all accesses to table A, B and C on the node group 1 and table accesses to D preferably on group 2. Because we do not support automatic table filtering/partitioning clients shall be allowed to manually route requests to the appropriate groups.  
 + 
 +Some nodes may be co-located to clients whereas others may be remote. This is often done in large systems when storing multiple copies, e.g. on the machine, on the rack, in the same data center, in a different data center. In case of MySQL Replication its unlikely to find such highly optimized setups, however, there may be nodes closer to the client than others. Nodes closer to a client may be given a certain alias or group name and application developers shall be allowed to hint routing to a group of such nodes.
  
 == Feature description == == Feature description ==
  
-  bool mysqlnd_ms_change_user(mixed $connection, mixed $where, $user, $password, $db) +For every node in the configuration users shall be able to set one or more group names. A SQL hint, for example, MS_GROUP=name can be used to hint the load balancer to direct a request to a certain node group.
-   +
-  $where: MYSQLND_MS_ALL_CONNECTIONS, MYSQLND_MS_LAST_USED_CONNECTION +
-   +
-Consider: +
  
-  $where: MYSQLND_MS_ALL_CONNECTIONS, MYSQLND_MS_LAST_USED_CONNECTION, name of server 
  
-=== mysqlnd_ms_get_errors() ===+=== Refine QoS session consistency server selection policy === 
 == Problem to solve / Idea == == Problem to solve / Idea ==
  
-Not all MS errors can be bubbled up through the standard APIs. For example, failing to parse parts of the MS config may result in connection error. Upon connect, the user will get the error code  for the failed connect. However, the failure to parse the config, the cause of the problem, is only shown as a warning.+Users can request session consistency. Session consistency guarantees that a user will only be redirected to nodes that have already replicated his changes. Currently we check the status of all configured nodes before we pick a node for statement execution. Checking the status causes extra load on the nodes.
  
 == Feature description == == Feature description ==
  
-  array mysqlnd_ms_get_errors([mixed $connection]) +Two additional ways for finding candidates help lowering the overhead: 
-   + 
-TDB. +   * Wait for GTID 
-  +   * Cache/persist GTID/synchron-status 
 + 
 +There are two ways to check in MySQL 5.6 if a server has replicated a GTID. One can ask a node whether a GTID has been replicated and either get an immediate response (yes/no) or delay the reply until the node has replicated the GTID (wait for GTID). Currently only the first logic is used. We shall also support "wait for GTID". In that case we pick a candidate and wait until the candidate has replicated the GTID. This will lower the checking overhead as only one node is checked but not all configured nodes. 
 + 
 +Furthermore we can persist GTID/synchron-status information and attempt to reuse it. If we search for a candidate for the first time we query all configured nodes and persist their responses. The responses are cached and used for all subsequent read-only operations until a write is done. After the write we flush the cache and, if a read-only operation follows, we check again all nodes and cache their synchro-status. 
 + 
pecl/mysqlnd_ms.1322481487.txt.gz · Last modified: 2017/09/22 13:28 (external edit)

Page Tools