Planet MySQL

Planet MySQL - http://www.planetmysql.org/
  1. How to mess up your data using ONE command in MySQL/Galera.

    Or how wsrep_on can bring you to have a cluster with usless data. This is a WARNING article, and it comes out after I have being working on define internal blueprint on how to perform DDL operation using RSU safely. The fun, if fun we want to call it, comes as usual by the fact that I am a curious guy and I often do things my way and not always following the official instructions. Anyhow, lets us go straight to the point and describe what can happen on ANY MySQL/Galera installation. The environment The test environment, MySQL/Galera (Percona PXC 5.6.20 version). The cluster was based on three nodes local no geographic distribution, no other replication in place then Galera. Haproxy on one application node, simple application writing in this table: Table: tbtest1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE: CREATE TABLE 'tbtest1' ( 'auAPP1nc' bigint(11) NOT NULL AUTO_INCREMENT, 'a' int(11) NOT NULL, 'uuid' char(36) COLLATE utf8_bin NOT NULL, 'b' varchar(100) COLLATE utf8_bin NOT NULL, 'c' char(200) COLLATE utf8_bin NOT NULL, 'counter' bigint(20) DEFAULT NULL, 'time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 'partitionid' int(11) NOT NULL DEFAULT '0', 'date' date NOT NULL, 'strrecordtype' char(3) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY ('auAPP1nc','partitionid'), KEY 'IDX_a' ('a'), KEY 'IDX_uuid' ('uuid') ) ENGINE=InnoDB AUTO_INCREMENT=482 DEFAULT CHARSET=utf8 COLLATE=utf8_bin     Small app 1 2 3 4 5 6 7 8 9 10 11 12 13 14 #! /bin/bash -- i=1 while : do echo "$i " mysql -ustress -pxxx -h192.168.0.35 -P 3307 -e "SET @HH=@@HOSTNAME; insert into test.tbtest1 (a,uuid,b,c,strrecordtype,date,partitionid)  values($i,UUID(),@HH,'a','APP1'|'APP2',now(),RAND()*100)"; i=$((i + 1)) if [ $i -eq 100 ] then break fi sleep 0.5; done       Server Information 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 (root@localhost:pm) [(none)]>\s SHOW global STATUS LIKE 'wsrep_provider_version'; -------------- /home/mysql/templates/PCX-56/bin/mysql Ver 14.14 Distrib 5.6.20-68.0, FOR Linux (i686) USING EditLine wrapper Connection id: 90 Current DATABASE: Current user: root@localhost SSL: NOT IN USE Current pager: stdout USING OUTFILE: '' USING delimiter: ; Server version: 5.6.20-68.0-25.7-log Percona XtraDB Cluster BINARY (GPL) 5.6.20-25.7, Revision 886, wsrep_25.7.r4126 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /home/mysql/instances/galera1-56/mysql.sock Uptime: 2 min 38 sec Threads: 3 Questions: 282 Slow queries: 0 Opens: 78 FLUSH TABLES: 3 Open TABLES: 8 Queries per second avg: 1.784 -------------- +------------------------+---------------+ | Variable_name | Value | +------------------------+---------------+ | wsrep_provider_version | 3.7(r7f44a18) | +------------------------+---------------+ 1 row IN SET (0.01 sec)       Facts In MySQL/Galera there is variable that allow us to say to the server to do not replicate. This variable is wsrep_on and when we set it as OFF the server will not replicate any statement to the other node. This is quite useful when in the need to perform actions on an single node, like when you need to perform DDL on RSU mode. But this flexibility can bite you quite badly. I had done a simple small change to the widely use command:   SET wsrep_on=OFF;   I just add GLOBAL: SET GLOBAL wsrep_on=OFF;     To be honest I was expecting to have the command rejected, but no it was accept and this is what happened: I had run the small loop (see above) on two application servers, one pointing to HAProxy and writing APP1 in the field strrecordtype, the other pointing directly to the node where I will issue the command with wsrep_on inserting APP2. The results: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 (root@localhost:pm) [test]>select @@HOSTNAME;select count(*) AS APP2_COUNTER FROM tbtest1 WHERE strrecordtype='APP2'; select count(*) AS APP1_COUNTER FROM tbtest1 WHERE strrecordtype='APP1'; +---------------+ | @@HOSTNAME | +---------------+ | tusacentral03 | +---------------+ 1 row IN SET (0.00 sec) +-------------+ |APP2_COUNTER | +-------------+ | 99 | +-------------+ 1 row IN SET (0.00 sec) +-------------+ |APP1_COUNTER | +-------------+ | 99 | +-------------+ 1 row IN SET (0.00 sec) (root@localhost:pm) [test]> (root@localhost:pm) [test]>SET GLOBAL wsrep_on=OFF; <------------- It should not be GLOBAL (root@localhost:pm) [test]>select @@HOSTNAME;select count(*) AS APP2_COUNTER FROM tbtest1 WHERE strrecordtype='APP2'; select count(*) AS APP1_COUNTER FROM tbtest1 WHERE strrecordtype='APP1'; +---------------+ | @@HOSTNAME | +---------------+ | tusacentral01 | +---------------+ 1 row IN SET (0.00 sec) +-------------+ |APP2_COUNTER | +-------------+ | 0 | +-------------+ 1 row IN SET (0.00 sec) +-------------+ |APP1_COUNTER | +-------------+ | 66 | <-------------------- 1/3 lost because HAProxy think that the node is ok... +-------------+ 1 row IN SET (0.00 sec)     As you can see in the tusacentral03 (which is the one where I issue SET GLOBAL wsrep_ON=OFF), I have ALL the records inserted in the local node and ALL the records coming from the others node. But on the node tusacentral01, I had NO records related to APP2, but more relevant I had lost 1/3 of my total inserts. Why? Well this is quite clear, and unfortunately is by design. If I issue wsrep_ON=OFF with GLOBAL the server will apply the setting to ALL sessions, meaning all session on that will STOP to replicate. In the source code the section relevant to this is quite clear: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 #wsrep_mysqld.cc #line 1395 int wsrep_to_isolation_begin(THD *thd, char *db_, char *table_, const TABLE_LIST* table_list) {   /*   No isolation for applier or replaying threads.   */ if (thd->wsrep_exec_mode == REPL_RECV) return 0;   int ret= 0; mysql_mutex_lock(&thd->LOCK_wsrep_thd);   if (thd->wsrep_conflict_state == MUST_ABORT) { WSREP_INFO("thread: %lu, %s has been aborted due to multi-master conflict", thd->thread_id, thd->query()); mysql_mutex_unlock(&thd->LOCK_wsrep_thd); return WSREP_TRX_FAIL; } mysql_mutex_unlock(&thd->LOCK_wsrep_thd);   DBUG_ASSERT(thd->wsrep_exec_mode == LOCAL_STATE); DBUG_ASSERT(thd->wsrep_trx_meta.gtid.seqno == WSREP_SEQNO_UNDEFINED);   if (thd->global_read_lock.can_acquire_protection()) { WSREP_DEBUG("Aborting APP1: Global Read-Lock (FTWRL) in place: %s %lu", thd->query(), thd->thread_id); return -1; }   if (wsrep_debug && thd->mdl_context.has_locks()) { WSREP_DEBUG("thread holds MDL locks at TI begin: %s %lu", thd->query(), thd->thread_id); }   /*   It makes sense to set auto_increment_* to defaults in APP1 operations.   Must be done before wsrep_APP1_begin() since Query_log_event encapsulating   APP1 statement and auto inc variables for wsrep replication is constructed   there. Variables are reset back in THD::reset_for_next_command() before   processing of next command.   */ if (wsrep_auto_increment_control) { thd->variables.auto_increment_offset = 1; thd->variables.auto_increment_increment = 1; }   if (thd->variables.wsrep_on && thd->wsrep_exec_mode==LOCAL_STATE) <------- Here we have a check for wsrep_on { switch (wsrep_OSU_method_options) { case WSREP_OSU_APP1: ret = wsrep_APP1_begin(thd, db_, table_, table_list); break; case WSREP_OSU_APP2: ret = wsrep_APP2_begin(thd, db_, table_); break; } if (!ret) { thd->wsrep_exec_mode= TOTAL_ORDER; } } return ret; } enum wsrep_exec_mode { LOCAL_STATE, REPL_RECV, TOTAL_ORDER, LOCAL_COMMIT };       So what happen is that the server check if the thd object has that variable ON and has LOCAL_STATE, if so it replicates, if not it does nothing. But as said while this makes sense in the SESSION scope, it does not in the GLOBAL.   Not only, setting wsrep_on to OFF in global scope does NOT trigger any further action from MySQL/Galera, like for instance the possible FACT that the node could be desynchronize from the remaining cluster. The interesting effect of this is that HAProxy has NO WAY to know that the node had stop to replicate, and as such the server can receive the requests but those will not replicate to the other node causing data diversion.   You can say, that a DBA SHOULD know what he is doing, and as such he/her should be MANUALLY desync the node and then issue the command. My point instead is that I don't see ANY good reason to have wsrep_on as global variable; instead I see this as a very dangerous and conceptually wrong "feature".   Browsing the Codership manual, I noticed that the wsrep_on variable comes with the "L" flag, meaning that the variable is NOT suppose to be GLOBAL. But it is... I also had dig in the code and: 1 2 3 4 5 6 7 8 9 10 11 12 wsrep_var.cc #line58   bool wsrep_on_update (sys_var *self, THD* thd, enum_var_type var_type) { if (var_type == OPT_GLOBAL) { // FIXME: this variable probably should be changed only per session thd->variables.wsrep_on = global_system_variables.wsrep_on; } return false; }     That is interesting isn't it? Wondering when this comment was inserted and why it was ignored.   Anyhow the source of all problems is here in the wsrep_on variable definition: 1 2 3 4 5 6 7 static Sys_var_mybool Sys_wsrep_on ( "wsrep_on", "To enable wsrep replication ", SESSION_VAR(wsrep_on), <----------------------- This allow global CMD_LINE(OPT_ARG), DEFAULT(TRUE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(wsrep_on_update));     The variable was defined as SESSION_VAR instead of SESSION_ONLY, and as such used also in global scope.   As already state, this is from my point of view a conceptual error not a bug, but something that should not exists at all, because in a cluster where I have data certify/replicate/synchronize there should NOT be any option for a DBA/user to bypass at GLOBAL level the data validation/replication process.   To note, and to make things worse, after I had done the test I can easily set wsrep_on back, and my node will continue to act as part of the cluster as if all the nodes are equal, while they are not. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 (root@localhost:pm) [test]>select @@HOSTNAME;select count(*) AS RSU_COUNTER FROM tbtest1  WHERE strrecordtype='RSU'; select count(*) AS TOI_COUNTER FROM tbtest1 WHERE strrecordtype='TOI'; +---------------+ | @@HOSTNAME | +---------------+ | tusacentral03 | +---------------+ 1 row IN SET (0.00 sec)   +-------------+ | RSU_COUNTER | +-------------+ | 181 | +-------------+ 1 row IN SET (0.00 sec)   +-------------+ | TOI_COUNTER | +-------------+ | 177 | +-------------+ 1 row IN SET (0.00 sec)   +---------------+ | @@HOSTNAME | +---------------+ | tusacentral01 | +---------------+ 1 row IN SET (0.00 sec)   +-------------+ | RSU_COUNTER | +-------------+ | 77 | +-------------+ 1 row IN SET (0.00 sec)   +-------------+ | TOI_COUNTER | +-------------+ | 139 | +-------------+     As you can see the cluster continue to insert data using HAProxy and all the node, but it has a data set that is inconsistent. Conclusions Never use SET GLOBAL with wsrep_on IF you are so crazy to do so, be sure no one is writing on the node. I am sure this is a mistake in the logic and as such this variable should be change from the source, in the code defining the variable SESSION_ONLY and not SESSION_VAROr wsrep_on can damage you quite badly.
  2. Full table scans and MySQL performance

    High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up. Select_scan from SHOW GLOBAL STATUS indicates how many full table scans were done since last MySQL restart. Scanning the entire table is a resource intensive operation. It also forces MySQL to store unnecessary data in the buffer pool, wasting memory and IO resources. Full scan of a tiny table would be quite fast so missing indexes could stay invisible until the load rises or the dataset grows up. This could also be the case for developers who work with too small data sets on their dev boxes. To prevent performance issues all newly added features need to be tested against production-size tables, so that the required indexes could be created on production servers before the code release. Full table scans can be caused by several reasons. First, a missing index forces MySQL to walk though the entire table and filter rows one-by-one to match the condition in WHERE clause or sort them instead of using index. For example: SELECT * FROM staff WHERE first_name = 'John'; SELECT * FROM staff ORDER BY first_name LIMIT 10; Lack of the index on first_name column would force MySQL to read the entire table even when only a few rows need to be sent back to the client. Second, sometimes MySQL is unable to utilize existing indexes and has to perform full table scan. Below are a few common examples. Using function or math as a constraint: SELECT * FROM table WHERE myfunc(a) = 5 SELECT * FROM sessions WHERE session_updated + INTERVAL 30 MINUTE < NOW(); Not equal and NOT IN constraints: SELECT * FROM table WHERE a 1 SELECT * FROM table WHERE a NOT IN (1,2,3) Bitwise operations on numeric columns: SELECT * FROM table WHERE ( status & 4 ) = 0 Postfix LIKE clause: SELECT * FROM table WHERE c LIKE ‘%omething’ All these kinds of queries need to be rewritten where possible, so that MySQL could use index lookups. Another performance trick is to move such queries out of MySQL, but that's another story. Third, some queries would cause a full table scan at all times. My favorite example is ORDER BY RAND(). MySQL has to calculate a random value for every single row in the dataset and then perform sorting. This is a guaranteed performance killer. Fourth, another special case is InnoDB doing a full table scan for SELECT COUNT(*) FROM table. This is one of the rare cases where MyISAM is faster because it keeps number or rows in the table metadata. InnoDB can't do that because of it's transactional nature, so it has to perform a full table scan to calculate the rows count on the fly. Please note that some admin statements like SHOW STATUS can increase Select_scan as well. Getting back to counters, another one called 'Select_full_join' shows an even worse case, when MySQL has to perform a full table scan against a joined table, which is even slower. Good news is, it is so slow that people usually notice these cases right away. How to get these numbers from MySQL? There are several ways: 1. Run SHOW GLOBAL STATUS LIKE 'Select_scan'; in any MySQL client and you'll get a raw value since MySQL start. 2. mysqladmin -r -i 10 extended-status | grep "Select_scan" will show the same as above and then continue to display live increments every 10 seconds. 3. Download poor man's health check. This tool will show you both historical and current per second averages for Select_scan, Select_full_join, and other useful statistics, as well as most resource consuming queries from the slow query log. Please see the report sample for details. 4. When MySQL server gets occasionally overloaded you can employ pt-stalk utility from Percona Toolkit to gather key metrics exactly when the issue appears. 5. Take a look on Select_scan graph in Cacti/Graphite/etc. Graph will show you how it changes over the time which is extremely useful in investigations, especially if you compare it with 'Com_select' statistics.
  3. MySQL benchmarking: Know your baseline variance!

    Often enough I find MySQL benchmark results where the difference between results is 1% or even less and some conclusions are drawn. Now it is not that 1% is not important – especially when you’re developing the product you should care about those 1% improvements or regressions because they tend to add up. However with such a small difference it is very important to understand whenever this is for real or it is just the natural variance for your baseline test.Take a look at this graph: Click the image for a larger view) This is the result for a simple in-memory, read-only “select by primary key” SysBench benchmark on dedicated physical hardware that is otherwise idle, simple 1 socket system. I tried to stabilize it as much as possible, for example disabling CPU frequency scaling. But still I saw some 3% difference between “good runs” and bad runs.What is the difference between those runs? Simply mysqld restarts.Does this mean you can’t measure a smaller difference? You can by setting the appropriate test plan. Often having several runs makes sense, in others you need to make sure the system warms up before taking measurements or having benchmark runs that are long enough. Whatever method you use it is a good idea to apply your test methodology by conducting several runs of your baseline run to ensure the results are stable enough for your purpose. For example If I decided to do five 30-minute runs and average the results, if they all run within 0.1% I will consider 0.3% differences as meaningful.Another practical trick that often helps me to separate real differences from some side effects is mixing the tests. Say if I have configurations I’m testing A and B instead of doing AAA BBB I would do ABABAB which helps with the case when there is some regression that can accumulate over time, such as with Flash.You should also note that in modern systems there is almost always something happening in the background that can change performance – the SSD is doing garbage collection, MySQL (or Kernel) is flushing dirty pages, the CPU can even simply cool off and as a result being able to support Turbo-boost operations a little longer. So when you are stat running your benchmarks make sure you keep the idle time between runs the same – scripting benchmarks and iterating scenarios helps here.Happy MySQL benchmarking!The post MySQL benchmarking: Know your baseline variance! appeared first on MySQL Performance Blog.
  4. Nasty MySQL Replication Bugs that Affect Upgrade to 5.6

    There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master. The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of thousands of RBR events. The... The post Nasty MySQL Replication Bugs that Affect Upgrade to 5.6 appeared first on ovais.tariq.
  5. Write Yourself a Query Rewrite Plugin: Part 1

    With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin. When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I will cover in a separate post — is the efficiency, especially the lack of overhead for those queries that are actually rewritten. Typical cases where you may want to write a pre-parse plugin are: When you want to remove certain specific clauses from queries. For example, perhaps you want to remove all ENGINE clauses from DDL statements. When you want to replace all queries against table_foo with table_foo2. When you want to add a hint, for example a USE INDEX hint, to all queries against tbl3. Any general case where you might want to do a broad REGEXP style find and replace. Example Plugin There will be an example pre-parse plugin shipped with MySQL. It is not very useful, but you can take it as a starting point for developing your own plugins. What it does is rewrite all queries to lowercase. You would typically issue the following commands to install it and then to demonstrate its usage and effects:mysql> install plugin rewrite_example soname 'rewrite_example.so'; Query OK, 0 rows affected (0,15 sec) mysql> SELECT "HELLO World"; +-------------+ | hello world | +-------------+ | hello world | +-------------+ 1 row in set, 1 warning (0,01 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------+ | Note | 1105 | Query 'SELECT "HELLO World"' rewritten to 'select "hello world"' by plugin: rewrite_example. | | Note | 1105 | Query 'show warnings' rewritten to 'show warnings' by plugin: rewrite_example. | +-------+------+----------------------------------------------------------------------------------------------+ 2 rows in set, 1 warning (0,00 sec) As you can see, it really rewrites all queries, including the show warnings query that I just issued. I did that to show you that the server knows that the query was rewritten and lets you know by raising a note. The Basics The plugin is conceptually very simple: it’s a single C function. The input is a const char *, the output another const char *. If your plugin returns a new query string, that’s the new query and the server will forget about the old one. Only an SQL note remains as a testimony of the original query. You really only need three files to create a plugin: Your source code file A plug.in file A CMakeLists.txt file Most of what goes into writing general plugins is already covered well in the manual, and http://dev.mysql.com/doc/refman/5.7/en/writing-plugins.html is a good starting point. What is new is the specific pre-parse query rewrite plugin type. You need to declare your specific plugin of this new type as shown below:static st_mysql_rewrite_pre_parse rewrite_example_descriptor= { MYSQL_REWRITE_PRE_PARSE_INTERFACE_VERSION, /* interface version */ rewrite_lower, /* rewrite raw query function */ free_rewritten_query, /* free allocated query */ }; This should be fairly straightforward. The first argument is the version number of the interface that the plugin is using, the second is the function doing the rewrite, and the third is called by plugin framework after the rewrite is complete in order to free up any resources you might have been using—for example, in the rewrite_example plugin’s rewrite_lower function we allocate a new char[] that we return a pointer to, so we need to clean that up afterwards in the free_rewritten_query function. As always, THANK YOU for using MySQL. Happy rewriting!