Planet MySQL

MariaDB 10.1.1: Defragmenting unused space on InnoDB tablespace

Introduction

When you e.g. delete rows, these rows are just marked as deleted not really physically deleted from indexes and free space introduced is not returned to operating system for later reuse. Purge thread will physically delete index keys and rows, but still free space introduced is not returned to operating system and this operation can lead holes on page. If you have variable length rows, this could lead to situation where this free space can’t be used for new rows (if these rows are larger than old ones). User may use OPTIMIZE TABLE or ALTER TABLE <table> ENGINE=InnoDB to reconstruct the table.

Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things:

  • Makes the table’s data and indexes contiguous inside ibdata1
  • Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1
New defragmentation

In MariaDB 10.1 we have merged Facebooks defragmentation code prepared for MariaDB by Matt, Seong Uck Lee from Kakao. Only major difference to Facebooks code and Matt’s patch is the fact that in MariaDB we decided not to introduce new literals to SQL and no changes to server code. Instead we use already existing OPTIMIZE TABLE and all code changes are inside InnoDB/XtraDB storage engines. To enable this new feature you need to add following to my.cnf (this requirement is to keep the original behavior of OPTIMIZE TABLE for those users that need it).

[mysqld] innodb-defragment=1

This new defragmentation feature works inplace, thus no new tables are created and there is no need to copy data from old table to new table. Instead this feature loads n pages and tries to move records so that pages would be full of records and frees pages that are fully empty after the operation.

New configuration variables
  • innodb_defragment: Enable/disable InnoDB defragmentation. When set to FALSE, all existing defragmentation will be paused. And new defragmentation command will fail. Paused defragmentation commands will resume when this variable is set to TRUE. Default value FALSE.
  • innodb_defragment_n_pages: Number of pages considered at once when merging multiple pages to defragment. Range of 2–32 and default is 7.
  • innodb_defragment_stats_accuracy: How many defragment stats changes there are before the stats are written to persistent storage. Set to 0 meaning disable defragment stats tracking. Default 0.
  • innodb_defragment_fill_factor_n_recs:  How many records of space defragmentation should leave on the page. This variable, together with innodb_defragment_fill_factor, is introduced so defragmentation won’t pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective. Range of 1–100 and default 20.
  • innodb_defragment_fill_factor: A number between [0.7, 1] that tells defragmentation how full it should fill a page. Default is 0.9. Number below 0.7 won’t make much sense. This variable, together with innodb_defragment_fill_factor_n_recs, is introduced so defragmentation won’t pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective.
  • innodb_defragment_frequency: Do not defragment a single index more than this number of time per second.This controls the number of time defragmentation thread can request X_LOCK on an index. Defragmentation thread will check whether 1/defragment_frequency (s) has passed since it worked on this index last time, and put the index back to the queue if not enough time has passed. The actual frequency can only be lower than this given number.
New status variables
  • Innodb_defragment_compression_failures: Number of defragment re-compression failures
  • Innodb_defragment_failures: Number of defragment failures.
  • Innodb_defragment_count: Number of defragment operations.
Example

set @@global.innodb_file_per_table = 1; set @@global.innodb_defragment_n_pages = 32; set @@global.innodb_defragment_fill_factor = 0.95; CREATE TABLE tb_defragment ( pk1 bigint(20) NOT NULL, pk2 bigint(20) NOT NULL, fd4 text, fd5 varchar(50) DEFAULT NULL, PRIMARY KEY (pk1), KEY ix1 (pk2) ) ENGINE=InnoDB; delimiter //; create procedure innodb_insert_proc (repeat_count int) begin declare current_num int; set current_num = 0; while current_num &lt; repeat_count do INSERT INTO tb_defragment VALUES (current_num, 1, REPEAT('Abcdefg', 20), REPEAT('12345',5)); INSERT INTO tb_defragment VALUES (current_num+1, 2, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+2, 3, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+3, 4, REPEAT('HIJKLM', 20), REPEAT('67890',5)); set current_num = current_num + 4; end while; end// delimiter ;// commit; set autocommit=0; call innodb_insert_proc(50000); commit; set autocommit=1;

After CREATE TABLE and INSERT operations we can see following from INFORMATION_SCHEMA:

select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 313 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 72 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 0 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB' and table_name like '%tb_defragment%'; table_name data_free_MB table_rows tb_defragment 4.00000000 50051 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 25873 4739939 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 50071 1051775

Now if we delete 3/4 of the records that will leave holes in pages and then we optimize table to execute defragmentation:

delete from tb_defragment where pk2 between 2 and 4; optimize table tb_defragment; Table Op Msg_type Msg_text test.tb_defragment optimize status OK show status like '%innodb_def%'; Variable_name Value Innodb_defragment_compression_failures 0 Innodb_defragment_failures 1 Innodb_defragment_count 4

After this we can see that some pages are freed and some pages merged:

select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 0 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 2 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB'; table_name data_free_MB table_rows innodb_index_stats 0.00000000 8 innodb_table_stats 0.00000000 0 tb_defragment 4.00000000 12431 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 690 102145 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 5295 111263

Links

WebScaleSQL Git repository https://github.com/webscalesql/webscalesql-5.6

Facebook Percona Live presentation: https://www.google.fi/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCQQFjAB&url=https%3A%2F%2Fwww.percona.com%2Flive%2Fmysql-conference-2014%2Fsites%2Fdefault%2Ffiles%2Fslides%2Fdefragmentation.pdf&ei=UgNKVNnZMcHhywP7qwI&usg=AFQjCNGREUpen21jCcy0bchUa6Ro83ol_A&sig2=MDZU2Ue9sX1kB9OusvdiFA


PlanetMySQL Voting: Vote UP / Vote DOWN

Dynamic Columns Tutorial – Part 2: Searching and Updating

Fri, 2014-10-24 09:21maxmether

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items' FROM items WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white'; +----+----------------------+ | id | White Items | +----+----------------------+ | 3 | Samsung Galaxy S5 | | 4 | Samsung Galaxy Pro 3 | +----+----------------------+

Notice that we had to specify the data type within the COLUMN_GET(), the AS CHAR in this example. That lets MariaDB know what to expect and how to cast the values it finds. In this case, we instructed MySQL to cast the values as characters.

Let's try another dynamic column function, but one that's designed primarily to be used in the WHERE--although it may be used elsewhere. Let's look at the COLUMN_EXISTS() function which merely checks if an attribute exists with a given name. For example we can look for all items that have an OS:

SELECT name FROM items WHERE COLUMN_EXISTS(attributes, 'OS'); +---------------------------+ | name | +---------------------------+ | Samsung Galaxy S5 | | Dell Latitude E6510 | | Lenovo ThinkPad Carbon X1 | | Samsung Galaxy Pro 3 | +---------------------------+ 4 rows in set (0.00 sec)

More interestingly this function can also be used to find items that do not have a specific attribute. For example let's look at all items that don't have a defined type (if there are such items:

SELECT id, name AS 'Typeless Items' FROM items WHERE NOT COLUMN_EXISTS(attributes, 'type'); +----+-----------------+ | id | Typeless Items | +----+-----------------+ | 1 | MariaDB t-shirt | | 2 | MariaDB t-shirt | +----+-----------------+ 2 rows in set (0.00 sec)

As you can see, that worked fine. We found two items that do not have a defined value for the type attribute in the attributes column.

Updating Dynamic Columns

So far we've used dynamic functions as part of SELECT statements to extract data or as part of INSERT statements when inserting the rows. You can also change the existing rows in a table, this can be done by using the Dynamic Column functions in UPDATE statements. If you want to add another name/value pair to a row that already contains a dynamic column, you can use the COLUMN_ADD() function.

For example, in the previous example, we determined that there is no type for the MariaDB t-shirt items. I forgot that when inserting the first rows and I didn't realise that all items would eventually have a type. But that's not a big issue, I can now add a type to these items. Let's add a type of t-shirt. Here's how that is done with an UPDATE statement:

UPDATE items SET attributes = COLUMN_ADD(attributes,'type','t-shirt') WHERE NOT COLUMN_EXISTS(attributes, 'type');

That's simple. Here's how those items look now:

SELECT name AS 'Item', COLUMN_LIST(attributes) AS 'Dynamic Column Names' FROM items WHERE name LIKE 'MariaDB%'; +-----------------+------------------------+ | Items | Dynamic Column Names | +-----------------+------------------------+ | MariaDB t-shirt | `size`,`type`,`colour` | | MariaDB t-shirt | `size`,`type`,`colour` | +-----------------+------------------------+

Perfect, job well done. Just to verify we can run the NOT COLUMN_EXISTS WHERE clause again:

SELECT id, name AS 'Typeless Items' FROM items WHERE NOT COLUMN_EXISTS(attributes, 'type'); Empty set (0.00 sec)

That seemed to have worked fine. Ok, now what if we want to remove an attribute from a row in the table? We can use the COLUMN_DELETE() function with the UPDATE statement like this:

UPDATE items SET attributes = COLUMN_DELETE(attributes,'type') WHERE name LIKE 'MariaDB%'; SELECT name AS 'Item', COLUMN_LIST(attributes) AS 'Dynamic Column Names' FROM items WHERE name LIKE 'MariaDB%'; +-----------------+----------------------+ | Item | Dynamic Column Names | +-----------------+----------------------+ | MariaDB t-shirt | `size`,`colour` | | MariaDB t-shirt | `size`,`colour` | +-----------------+----------------------+

That removed the type attribute again from the two rows of t-shirts.

Ok, but what if you want to change a specific attribute, can you do that? The answer is yes. MariaDB does not allow you to “overload” the attribute names, so you cannot have the same attribute twice in the same row. This means that when you use the COLUMN_ADD() function in an UPDATE statement to add an attribute that already exists on a specific row you are basically overwriting the old one. In that sense COLUMN_ADD() works more like the REPLACE statement than an INSERT statement.

 

That is all for this time, next blog will focus on search efficiency and other more advanced features of Dynamic Columns.

 

Tags: DeveloperHowtoMariaDB EnterpriseNoSQL About the Author Max Mether

As a co-founder Max now manages the field services and training departments at MariaDB and helps advance the MariaDB and MySQL eco-systems around the world. Max is a frequent speaker at LinuxCon, meetups and other conferences around the globe.


PlanetMySQL Voting: Vote UP / Vote DOWN

Abdel-Mawla Gharieb: Galera Cluster and XA Transactions

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":

SQL> SHOW PROCESSLIST; 27 user host:33214 foodmart Query 14440 sleeping XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x SQL> SHOW ENGINE INNODB STATUS; TRANSACTIONS ============ ---TRANSACTION 2DE71D, ACTIVE 14459 sec 9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115 MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart sleeping XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x41544f4d ONE PHASE

XA means eXtended Architecture and "XA COMMIT" statement is one of the distributed transactions (XA Transactions) commands which are clearly NOT supported in Galera Cluster and one of its limitations because of possible rollback on commit.

The following command can be used to check if XA Transactions are being used by your application or not:

SQL> SHOW GLOBAL STATUS LIKE 'Com_xa%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Com_xa_commit | 2828094 | | Com_xa_end | 2828221 | | Com_xa_prepare | 0 | | Com_xa_recover | 2205697 | | Com_xa_rollback | 42 | | Com_xa_start | 2828305 | +-------------------+---------+ 6 rows in set (0.00 sec)

There are only two possible solutions for this problem:

  • Get rid of all XA transactions in the application to get the Galera Cluster work.
  • Use another HA solution (Active/passive, Master/Slave, ... etc) but not Galera Cluster.
Conclusion
  • XA transactions can not be supported in Galera Cluster and that is already stated clearly in the Galera Cluster Limitations.
  • Before Moving to Galera Cluster, it is preferred to go through all its limitations and check whether your application can cope with them or not.

PlanetMySQL Voting: Vote UP / Vote DOWN

FromDual.en: MySQL Environment MyEnv 1.1.2 has been released

Taxonomy upgrade extras: myenvoperationMySQL Operationsmulti instanceconsolidationBackupcatalog

FromDual has the pleasure to announce the release of the new version 1.1.2 of its popular MySQL, Galera, MariaDB and Percona Server multi-instance environment MyEnv.

You can download MyEnv from here.

In the inconceivable case that you find a bug in MyEnv please report it to our Bugtracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.1 to 1.1.2 # cd ${HOME}/product # tar xf /download/myenv-1.1.2.tar.gz # rm -f myenv # ln -s myenv-1.1.2 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Changes in MyEnv 1.1.2 MyEnv
  • The MySQL Backup Manager was removed from MyEnv and put into its own package which can be downloaded from FromDual Backup/Recovery Manager.
  • OEM Agent is checked now based on process list and not oemagentctl status any more. Makes it much faster.
  • Alias cdc for NDB Cluster directory removed. NDB Cluster is not supported any more.
  • Deprecate alias v and replace by V.
  • Error unlink(/var/lock/subsys/mysql): Permission denied is caught more nicely now.
  • Unknown version in up guessing is improved.
  • MD5 checksum made portable for Darwin OS (Mac OSX).
MyEnv Installer
  • innodb_flush_log_at_trx_commit default in template changed.
  • Version guessing improved for installer.
  • Better download support for Percona Server and MariaDB added.
  • mkdir bug fixed.
  • Version check for RedHat made better.
  • Check for lsb_release and SELinux/AppArmor check added for faster finding problems during MySQL installation.
  • Template my.cnf from website is used for creating an intance.
  • Option hideschema is automatically added to the myenv.conf file now.
  • Check and warning implemented if non mysql user is used.
  • Error is caught when wrong user is used.
  • mysql_install_db output made more verbose in case of errors for debugging.
  • Default option changes from Add to Save after instance was changed.
  • Missing users HOME directory is caught now.
  • Question done? can be answered with y now.
  • Comment about waiting during instance installation added.
MyEnv Utilities
  • Table offline/online scripts integrated into MyEnv utilities.
  • alter_engine.pl does hide views from Primary Key check now.
MySQL Backup Manager
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL 5.6 Full Text Search Throwdown: Webinar Q&A

Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?

There are several strategies for updating a Solr index. In my examples, I showed only a “full import” which is what you would do to create an index by reading all the source data.

You can also perform a “delta import” periodically, to add a subset of the source data to an existing index, for example to add data that has changed since the last time you updated the Solr index. See the documentation for Using delta-import command and also Using query attribute for both full and delta import.

The delta import would typically be something you would invoke from a cron job, perhaps every hour. But that means that a Solr search might not find data that has changed in MySQL more recently than the last delta import. Depending on the application, a delay of up to 60 minutes might be acceptable, or else maybe you have strict requirements that all data must be in sync instantly.

You could also update the Solr index one document at a time using its Java API or web service API. This would require you to write code in your application. Every time you INSERT or UPDATE or DELETE a document in MySQL that you want to be kept in sync with the Solr index, you would write more code to do a similar operation in the Solr index. That way every single text change would be searchable nearly immediately.

Q: Did you test Elasticsearch? (several people asked about this)

I did not test Elasticsearch, but according to their technology overview: “Elasticsearch uses Lucene under the covers.” So I expect that this part of Elasticsearch performs similarly to what I saw from Apache Solr, which also uses Lucene internally.

Q: One question I could not understand, how to maintain Sphinx index in sync with data? Can be it in real time?

The Sphinx Search index does not automatically refresh as your MySQL data changes. You would have to write application code to invoke the indexing process. There’s a page in the Sphinx Search documentation about Live Index Updates, that gives an overview of the two methods, and links to further reading.

This is definitely the most inconvenient aspect of Sphinx Search. Queries are very fast, but it’s expensive to do incremental updates to an index. So it’s ideal for indexing an archive of text that doesn’t change very frequently, but not as easy to use it for indexing rapidly-changing content.

Q: I have over 800,000 PDF documents to index (in several languages), any recommendations?

I said during the webinar that I recalled there exists tools to extract searchable text from a PDF file. I found one such project called Apache PDFBox includes this capability, and they have a page describing a helper class for doing PDF parsing and extraction combined with Lucene indexing. I haven’t used it myself, so I can’t comment on its performance for indexing 800,000 PDF documents, but it seems like you could write a Java program to iterate over your collection of PDF’s, and index them using this class.

Q: What is your suggestion to use Sphinx Search for single column searches?

You can use any SQL query in the sphinx.conf to define the source data to index. You can select one column, multiple columns, or even multiple columns from joined tables. The result from any SQL query you write can be used as the data source.

Q: Which modules did you use with Sphinx Search? Did you use its built-in stemmers and metaphone package, etc.?

I installed the default modules. I don’t know if there is a significant performance difference from using optional packages.

Q: What about quality of results from each solution? I remember reading an article on percona.com several months ago comparing MyISAM fulltext vs InnoDB fulltext, and there were concerns about the results from InnoDB. Did you do any testing on this?

Indeed, here’s a link to the excellent blog post by my colleague Ernie Souhrada in which he found some surprises in the results from InnoDB FTS: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

I was just doing some comparison for performance in the current MySQL 5.7 milestone. I didn’t compare the query results this time.

Q: Is there any full text search in Percona Server with XtraDB?

Percona Server is based on the upstream MySQL Community Edition of the respective version number. So Percona Server has the builtin FULLTEXT index types for MyISAM and InnoDB, and we have not changed this part of the code. Percona Server does not bundle Sphinx Search, but it’s not too difficult to install Sphinx Search as a complementary technology, just as you would install other packages that are commonly used parts of an application infrastructure, for example Memcached or HA-proxy.

Q: Is MySQL going to improve the built-in InnoDB FTS in the near future?

They are continuing to add features that improve FTS, for example:

  • You can now write your own plugins for fulltext parsing (that is, parsing the input data to identify “words” to index; you may have your own idea about how to split text into words).
  • Both B-tree and full-text types now uses bulk-loading to make it faster and more efficient to build the index.

I’m not aware of any work to improve the performance of fulltext queries significantly.

Q: What is the performance comparison between MyISAM and InnoDB for inline index updating?

I didn’t test performance of incremental index updates this time. I only populated my tables from the StackOverflow data using LOAD XML, and then I created fulltext indexes on the populated tables. But I generally favor moving all important data to InnoDB, and not using MyISAM tables. It’s hard to imagine that the performance of index updates would be so much better that would convince me to use MyISAM. It’s more likely that the accuracy of search results would be a good reason to use MyISAM. Even then, I’d keep the original data in InnoDB and use MyISAM only as a copy of the data, to create a disposable fulltext index.

Thanks again for attending my webinar! For more great content, please join Percona and the MySQL community at our conference events. The next one is Percona Live London 2014 on November 3-4. We also look forward to the Open Stack Live 2015 in Santa Clara, California April 13-14, in the same venue with Percona Live MySQL Conference and Expo 2015, April 13-16.

Also watch more webinars from Percona in the future!

The post MySQL 5.6 Full Text Search Throwdown: Webinar Q&A appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Resources for Database Clusters: 9 DevOps Tips, ClusterControl 1.2.8 Release, HAProxy Webinar Replay &amp; More

October 23, 2014 By Severalnines Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters

 

Here is a summary of resources & tools that we’ve made available to you in the past weeks. If you have any questions on these, feel free to contact us!

 

New Technical Webinar

If you are in DevOps, you will know that deploying and managing databases has its challenges! Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live!

In this new webinar, we will walk you through 9 key tips to consider before specifically taking Galera Cluster into production. So if you are in devops, then this webinar is for you ;-)

Read more details and register here!

 

Technical Webinar - Replay

If you missed this webinar with our guest speaker from HAProxyTechnologies (or would just like to view it again), it’s online on demand.

read more


PlanetMySQL Voting: Vote UP / Vote DOWN

Refactoring replication topology with Pseudo GTID

This post describes in detail the method of using Pseudo GTID to achieve unplanned replication topology changes, i.e. connecting two arbitrary slaves, or recovering from a master failure even as all its slaves are hanging in different positions.

Please read Pseudo GTID and Pseudo GTID, RBR as introduction.

Consider the following case: the master dies unexpectedly, and its three slaves are all hanging, not necessarily at same binary log file/position (network broke down while some slaves managed to salvage more entries into their relay logs than others)

(Did you notice the "Candidate for master" message? To be discussed shortly)

GTID

With GTID each transaction (and entry in the binary log) is associated with a unique mark -- the Global Transaction ID. Just pick the slave with the most advanced GTID to be the next master, and just CHANGE MASTER TO MASTER_HOST='chosen_slave' on the other slaves, and everything magically works. A slave knows which GTID it has already processed, and can look that entry on its master's binary logs, resuming replication on the one that follows.

How does that work? The master's binary logs are searched for that GTID entry. I'm not sure how brute-force this is, since I'm aware of a subtlety which requires brute-force scan of all binary logs; I don't actually know if it's always like that.

Pseudo GTID

We can mimick that above, but our solution can't be as fine grained. With the injection of Pseudo GTID we mark the binary log for unique entries. But instead of having a unique identifier for every entry, we have a unique identifier for every second, 10 seconds, or what have you, with otherwise normal, non-unique entries in between our Pseudo GTID entries.

Recognizing which slave is more up to date

Given two slaves, which is more up to date?

  • If both replicate(d) from same master, a SHOW SLAVE STATUS comparison answers (safe method: wait till SQL thread catches up with broken IO thread, compare relay_master_log_file, exec_master_log_pos on both machines). This is the method by which the above "Candidate for master" message is produced.
  • If one is/was descendent of the other, then obviously it is less advanced or equals its ancestor.
  • Otherwise we're unsure - still solvable via bi-directional trial & error, as explained later on.

For now, let's assume we know which slave is more up to date (has received and executed more relay logs). Let's call it S1, whereas the less up-to-date will be S2. This will make our discussion simpler.

Prerequisites
  • We require a Pseudo GTID in place: a periodic injection of a known-to-be-unique query, and which we know how to intercept
  • We require log_slave_updates on all slaves. We will need to parse the binary logs on slaves. I have little trust in the availability of relay logs: these are flushed, rotated and auto-erased all too quickly. The proposed solution does not require any daemon running on the MySQL servers themselves. There will be nothing to back up the relay logs, so I can't trust these to exist. Binary logs, on the other hand, have expiry period in days, and so I can trust them to exist for a duration of a few minutes.
  • Normal replication. Not multi threaded. Not multi-source.
The process of rematching slaves

S1 is more up to date, hence we want to make S2 a slave of S1. We expect the statements/entries found in S2's binary logs to exist in S1, in the same order, but somewhere back in the past, padded by additional entries (zero or more) that are not found in S2. Steps are:

  • Find latest Pseudo-GTID entry in S2's logs. This can be done by iterating S2's binary logs newest to oldest. The first (time DESC) binary log where such entry is found is to be searched for the last entry (latest). Keep record of the binlog file2:pos2 coordinates.
  • Take note of the exact entry made in the above. This is the unique value.
  • Search said unique value in S1's binary logs. Since it is unique, your method of search is arbirtary, you just need to find it. Brute-force wise you start looking at newest binary log moving back in time. Not found? Unlikely, since this means the lag diff between S1 and S2 is as long as the binlog expiry. We will be handling with failures and with immediate actions; we can expect slave lags in the seconds or in the minutes - we don't even consider the possibility where the entry is not found.
  • Take note of the coordinates file1:pos1 in S1 where we found the unique value.
  • We now iterate S2's binary logs starting with the Pseudo GTID file2:pos2. We expect to find each entry in S1's binary logs, successively, starting file1:pos1. We verify the entries in both servers are identical. Exceptions above could be:
    • Meta-entries (start-of-log, end-of-log, shutdown), in which case we skip to the next entry (this is done in both S1 and S2)
    • Local statements executed directly on either S1 or S2, such as ANALYZE TABLE or whatever, which make no impact on data -- we may skip these
    • Local, evil statements executed directly on the slaves,which make for data impact (INSERT, DELETE, ...). We choose to fail the operation in such case
  • After all entries in S2 (matched by entries in S1) are iterated, our S1 "cursor" now looks at the first statement that never made it to S2. This is file_win:pos_win, into which we will point S2.
  • Or, we might find that upon iterating all entries in S2 we have exactly reached the end of binlog entries for S1: this means both S1 and S2 are actually in identical state. We point S2 into S1's next-binlog-position.
    • This is in fact no different than the previous case, but of particular interest.
  • Or, we might run out of entries in S1. No, we can't, because our assumption was that S1 is more advanced than (or equally advanced as) S2. But this answers the question: "what if didn't know in advance who's more advanced?" (no pun intended). In such case we conclude S2 is actually more advanced than S1 and we can try the other way around.

That last bullet is of importance: if you have two slaves whose "family connection" is complex, you can still match one below the other; you may try one way and fail, then try the other way around and succeed.

Comparison of the events following the Pseudo-GTID is a good way of sanity checking (some meta-stuff should be ignored, like transaction IDs, table IDs, these can vary across servers), and builds up confidence in the correctness of the operation.

The codebase is actually complete and pushed; I'll release a BETA version or orchestrator next week, that supports Pseudo GTID. Let me tell you, doing this kind of crazy stuff with visual feedback (of course command line is available) is very very cool.

 

 


PlanetMySQL Voting: Vote UP / Vote DOWN

Improvements to STRICT MODE in MySQL

As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.

STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.

In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:

 1. STRICT mode got simpler

It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only took effect if STRICT mode was also enabled, otherwise they produced warnings.

In MySQL 5.6 we deprecated these three modes and in 5.7.4 we have made their functionality part of STRICT mode itself. Basically the effects of enabling these three modes have been folded into STRICT mode. However, these modes are still retained due to upgrade issues and for backward compatibility. These modes will be completely removed in 5.8. Setting these modes will have no effect from 5.7.4 onwards. More details can be found here: WL#7467. Also you can check the documentation for this task here.

2. STRICT mode got better

It was also observed that the implementation of STRICT mode was not very good, and we got many related bug reports. For example, see Bug #42910 (triggers override strict sql_mode).

The behavior of Stored Routines with respect to STRICT mode was not consistent either. Sometimes, they were allowed to perform actions which are otherwise prohibited in STRICT mode.

The problem was that there was no central place in the code where the statements and errors affected by STRICT mode were handled. The code had the abort_on_warning flag which was switched off and on at various places across the codebase, making the implementation hard to understand and error prone.

In MySQL 5.7.4, we have re-implemented STRICT mode. The usage of the abort_on_warning flag is completely removed. Now, for each statement affected by STRICT mode, we push an error handler which is active during execution of the statement, and pop it when the statement execution is finished. There is also now a central place in the code where all errors that are generated during statement execution, and which are affected by STRICT mode, are handled. For additional information, you can look here: WL#6891.

Following is the list of errors affected by STRICT mode:

  • ER_TRUNCATED_WRONG_VALUE –  “Truncated incorrect value”
  • ER_WRONG_VALUE_FOR_TYPE –  “Incorrect value for function”
  • ER_WARN_DATA_OUT_OF_RANGE –  “Out of range value”
  • ER_TRUNCATED_WRONG_VALUE_FOR_FIELD –  “Incorrect value for column at row”
  • WARN_DATA_TRUNCATED –  “Data truncated for column”
  • ER_DATA_TOO_LONG –  “Data too long for column”
  • ER_BAD_NULL_ERROR –  “Column cannot be null”
  • ER_DIVISION_BY_ZERO –  “Division by 0″
  • ER_NO_DEFAULT_FOR_FIELD –  “Field doesn’t have a default value”
  • ER_NO_DEFAULT_FOR_VIEW_FIELD –  “Field of view underlying table doesn’t have a default value”
  • ER_CUT_VALUE_GROUP_CONCAT –  “Row was cut by GROUP_CONCAT()”
  • ER_DATETIME_FUNCTION_OVERFLOW –  “Datetime function field overflow”
  • ER_WARN_NULL_TO_NOTNULL –  “Column set to default value: NULL supplied to NOT NULL column”
  • ER_WARN_TOO_FEW_RECORDS –  “Row doesn’t contain data for all columns”
  • ER_TOO_LONG_KEY –  “Specified key was too long”
  • ER_WRONG_ARGUMENTS –  “Incorrect arguments”
  • ER_INVALID_ARGUMENT_FOR_LOGARITHM –  “Invalid argument for logarithm”

STRICT mode applies to the following types of DML statements:

  • INSERT
  • UPDATE
  • DELETE
  • LOAD DATA
  • ALTER TABLE
  • INSERT… SELECT
  • CREATE TABLE
  • CREATE INDEX
  • CREATE TABLE… SELECT
  • SELECT sleep()
 3. STRICT mode is the default

We have decided to add STRICT_TRANS_TABLES to the list of default sql modes in MySQL 5.7.5. (WL#7764).

Previously, if the user tried to insert 15 characters into a CHAR(10) column, then by default (STRICT Mode OFF) it would insert the first 10 characters, present the user with a warning, and then throw away the remaining five characters. Now the default behaviour (STRICT Mode ON) will be that the above statement will be rejected with an error.

Lots of changes have been made to existing test cases so that they are run with the new default SQL MODE. Around 500 mtr testcases were updated as part of this work.

WL#7467, WL#6891, WL#7764 were designed and implemented by Raghav Kapoor.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL sys 1.3.0 released – The Facepalm Release

With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.

So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.

This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).

This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:

mysql> SELECT * FROM innodb_lock_waits\G *************************** 1. row *************************** waiting_trx_id: 805505 waiting_thread: 78 waiting_query: UPDATE t1 SET val = 'c2' WHERE id = 3 waiting_lock_id: 805505:132:3:28 waiting_lock_mode: X waiting_lock_type: RECORD waiting_lock_table: `db1`.`t1` waiting_lock_index: PRIMARY blocking_trx_id: 805504 blocking_thread: 77 blocking_query: UPDATE t1 SET val = CONCAT('c1', SLEEP(10)) WHERE id = 3 blocking_lock_id: 805504:132:3:28 blocking_lock_mode: X blocking_lock_type: RECORD blocking_lock_table: `db1`.`t1` blocking_lock_index: PRIMARY

Sorry for the breakage, but enjoy the new view!


PlanetMySQL Voting: Vote UP / Vote DOWN

Monitoring progress and temporal memory usage of Online DDL in InnoDB

Introduction

Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.

  1. InnoDB::ha_prepare_inplace_alter_table(..)
  2. InnoDB::ha_inplace_alter_table(..)
  3. InnoDB::ha_commit_inplace_alter_table(..)
  4. mysql_rename_table(..)

InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement will copy old table’s rows to a new altered table. After this MariaDB will take exclusive lock for target table and applies row log buffer to the new altered table.

This introduces a new unpredictable failure case row log buffer overflow. MariaDB server will rollback ALTER statement if row log buffer overflows. Thus, there is following problems:

  • If row log buffer size is too small the ALTER statement is rolled back and you have wasted precious time and resources.
  • If row log buffer is too big, you have wasted precious main-memory that could be used e.g. for buffer pool.
  • Currently, there is no way to see how much row log buffer is used and how much there is free space.
  • Currently, there is not even estimate how much work has been done and how much there is till to be done.
  • Currently, merge sort phase could also take a long time and there is no progress information.
Improvements

There is two improvements in MariaDB 10.1: new status variables and progress information for online DDL.

New status variables and progress info

MariaDB Corporation would like to thank Matt, Seong Uck Lee from Kakao for contributing a patch that has now merged to MariaDB 10.1.

First of all there is three new global status variables.

  • Innodb_onlineddl_rowlog_rows: Shows how many rows is stored in the row log buffer.
  • Innodb_onlineddl_rowlog_pct_used: Shows row log buffer usage in 5-digit integer  (10000 means 100.00% ).
  • Innodb_onlineddl_pct_progress: Shows the progress of in-place alter table. It might be not so accurate because in-place alter is highly dependent on disk and buffer pool status.

Lets consider as an example where we have InnoDB table containing 150000 rows and we try to add a new column.

CREATE TABLE tb_onlineddl1 ( pk1 int(11) NOT NULL, pk2 bigint(20) NOT NULL, fd1 bigint(20) DEFAULT NULL, fd2 bigint(20) DEFAULT NULL, fd3 datetime DEFAULT NULL, fd4 text, fd5 varchar(50) DEFAULT NULL, fd6 bigint(20) DEFAULT NULL, fd7 bigint(20) DEFAULT NULL, PRIMARY KEY (pk1, pk2), UNIQUE KEY ux1 (pk2, pk1), KEY ix1 (fd6, fd7) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE tb_onlineddl1 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;

Concurrently, if we add new row, update some rows and delete few rows

insert into tb_onlineddl1 values (200000, 200000, 1,1, NULL,'insert', 'insert', 1,1); update tb_onlineddl1 set fd5='update' where pk1 between 2000 and 3000; delete from tb_onlineddl1 where pk1 between 6000 and 7000; show status like 'Innodb_onlineddl%'; Variable_name Value Innodb_onlineddl_rowlog_rows 2003 Innodb_onlineddl_rowlog_pct_used 26 Innodb_onlineddl_pct_progress 5677

This means that at the time of status statement there were 2003 rows on row log, 23\% of memory allocated for row log is used, and online alter table has completed 56.77\% of it’s work.

There is also additional output at error log, as example:

141022 15:49:59 [Note] InnoDB: Online DDL : Start 141022 15:49:59 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files 141022 15:50:01 [Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files 141022 15:50:01 [Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), estimated cost : 18.0263 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs 141022 15:50:01 [Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs 141022 15:50:02 [Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs 141022 15:50:02 [Note] InnoDB: Online DDL : End of merge-sorting index PRIMARY (1 / 3) 141022 15:50:02 [Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), estimated cost : 27.0395 141022 15:50:11 [Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3) 141022 15:50:11 [Note] InnoDB: Online DDL : Completed 141022 15:50:11 [Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), estimated cost : 5.7895 141022 15:50:11 [Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs 141022 15:50:11 [Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs 141022 15:50:11 [Note] InnoDB: Online DDL : End of merge-sorting index ux1 (2 / 3) 141022 15:50:11 [Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), estimated cost : 8.6842 141022 15:50:17 [Note] InnoDB: Online DDL : End of building index ux1 (2 / 3) 141022 15:50:17 [Note] InnoDB: Online DDL : Completed 141022 15:50:17 [Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), estimated cost : 6.1842 141022 15:50:17 [Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs 141022 15:50:17 [Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs 141022 15:50:17 [Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs 141022 15:50:17 [Note] InnoDB: Online DDL : End of merge-sorting index ix1 (3 / 3) 141022 15:50:17 [Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), estimated cost : 9.2763 141022 15:50:23 [Note] InnoDB: Online DDL : End of building index ix1 (3 / 3) 141022 15:50:23 [Note] InnoDB: Online DDL : Completed

Merge sort progress

Additionally, show processlist statement will output estimate of index merge sort progress, e.g.

MariaDB [test]&gt; show processlist; +----+------+-----------+------+---------+------+----------------+--------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+----------------+--------------------------------------------------------------------------+----------+ | 3 | root | localhost | test | Sleep | 23 | | NULL | 0.000 | | 4 | root | localhost | test | Query | 0 | init | show processlist | 0.000 | | 5 | root | localhost | test | Query | 28 | altering table | ALTER TABLE tb_onlineddl1 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE | 0.356 | +----+------+-----------+------+---------+------+----------------+--------------------------------------------------------------------------+----------+ 3 rows in set (0.00 sec)

Links

http://seonguck.blogspot.kr/2014/09/what-is-problem-of-mysql-online-ddl.html
http://kakao-dbe.blogspot.kr/2014/09/mysql-status-variables-for-innodb.html


PlanetMySQL Voting: Vote UP / Vote DOWN

Pseudo GTID, Row Based Replication

This post continues Pseudo GTID, in a series of posts describing an alternative to using MySQL GTIDs.

The solution offered in the last post does not work too well for row based replication. The binary log entries for the INSERT statement look like this:

# at 1020 # at 1074 #141020 12:36:21 server id 1  end_log_pos 1074  Table_map: `test`.`pseudo_gtid` mapped to number 33 #141020 12:36:21 server id 1  end_log_pos 1196  Update_rows: table id 33 flags: STMT_END_F BINLOG ' lddEVBMBAAAANgAAADIEAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE lddEVBgBAAAAegAAAKwEAAAAACEAAAAAAAEAA///+AEAAACL10RUJDg2ZmRhMDk1LTU4M2MtMTFl NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAlddEVCQ4Y2YzOWMyYy01ODNjLTExZTQtOTc2Mi0zYzk3 MGVhMzFlYTg= '/*!*/;

Where's our unique value? Encoded within something that cannot be trusted to be unique. Issuing mysqlbinlog --verbose helps out:

BEGIN /*!*/; # at 183 # at 237 #141020 12:35:51 server id 1  end_log_pos 237   Table_map: `test`.`pseudo_gtid` mapped to number 33 #141020 12:35:51 server id 1  end_log_pos 359   Update_rows: table id 33 flags: STMT_END_F BINLOG ' d9dEVBMBAAAANgAAAO0AAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE d9dEVBgBAAAAegAAAGcBAAAAACEAAAAAAAEAA///+AEAAABt10RUJDc1MWJkYzEwLTU4M2MtMTFl NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAd9dEVCQ3YjExZDQzYy01ODNjLTExZTQtOTc2Mi0zYzk3 MGVhMzFlYTg= '/*!*/; ### UPDATE `test`.`pseudo_gtid` ### WHERE ###   @1=1 ###   @2=1413797741 ###   @3='751bdc10-583c-11e4-9762-3c970ea31ea8' ### SET ###   @1=1 ###   @2=1413797751 ###   @3='7b11d43c-583c-11e4-9762-3c970ea31ea8'

and that's something we can work with. However, I like to do stuff from within MySQL, and rely as little as possible on external tools. How do the binary log entries look via SHOW BINLOG EVENTS? Not good.

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000058' limit 20; +------------------+------+-------------+-----------+-------------+---------------------------------------+ | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                  | +------------------+------+-------------+-----------+-------------+---------------------------------------+ | mysql-bin.000058 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4 | | mysql-bin.000058 |  107 | Query       |         1 |         183 | BEGIN                                 | | mysql-bin.000058 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)       | | mysql-bin.000058 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F        | | mysql-bin.000058 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5460 */                 | | mysql-bin.000058 |  386 | Query       |         1 |         462 | BEGIN                                 | | mysql-bin.000058 |  462 | Table_map   |         1 |         516 | table_id: 33 (test.pseudo_gtid)       | | mysql-bin.000058 |  516 | Update_rows |         1 |         638 | table_id: 33 flags: STMT_END_F        | | mysql-bin.000058 |  638 | Xid         |         1 |         665 | COMMIT /* xid=5471 */                 | | mysql-bin.000058 |  665 | Query       |         1 |         741 | BEGIN                                 | | mysql-bin.000058 |  741 | Table_map   |         1 |         795 | table_id: 33 (test.pseudo_gtid)       | | mysql-bin.000058 |  795 | Update_rows |         1 |         917 | table_id: 33 flags: STMT_END_F        | | mysql-bin.000058 |  917 | Xid         |         1 |         944 | COMMIT /* xid=5474 */                 | | mysql-bin.000058 |  944 | Query       |         1 |        1020 | BEGIN                                 | | mysql-bin.000058 | 1020 | Table_map   |         1 |        1074 | table_id: 33 (test.pseudo_gtid)       | | mysql-bin.000058 | 1074 | Update_rows |         1 |        1196 | table_id: 33 flags: STMT_END_F        | | mysql-bin.000058 | 1196 | Xid         |         1 |        1223 | COMMIT /* xid=5476 */                 | | mysql-bin.000058 | 1223 | Query       |         1 |        1299 | BEGIN                                 | | mysql-bin.000058 | 1299 | Table_map   |         1 |        1353 | table_id: 33 (test.pseudo_gtid)       | | mysql-bin.000058 | 1353 | Update_rows |         1 |        1475 | table_id: 33 flags: STMT_END_F        | +------------------+------+-------------+-----------+-------------+---------------------------------------+

The representation of row-format entries in the SHOW BINLOG EVENTS output is really poor. Why, there's nothing to tell me at all about what's been done, except that this is some operation on test.pseudo_gtid. Obviously I cannot find anything unique over here.

Not all is lost. How about DDL statements? Those are still written in SBR format (there's no rows to log upon creating a table). A solution could be somehow manipulating a unique value in a DDL statement. There could be various such solutions, and I chose to use a CREATE VIEW statement, dynamically composed of a UUID():

drop event if exists test.update_pseudo_gtid_rbr_event; delimiter ;; create event if not exists   test.update_pseudo_gtid_rbr_event   on schedule every 10 second starts current_timestamp   on completion preserve   enable   do     begin       set @pseudo_gtid := uuid();       set @_create_statement := concat('create or replace view test.pseudo_gtid_v as select \'', @pseudo_gtid, '\' from dual');       PREPARE st FROM @_create_statement;       EXECUTE st;       DEALLOCATE PREPARE st;         end ;; delimiter ;

And this is how it looks on runtime (running this new event along with the old one):

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000060' limit 20; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000060 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 | | mysql-bin.000060 |  107 | Query       |         1 |         183 | BEGIN                                                                                                                                                                                 | | mysql-bin.000060 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000060 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000060 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5802 */                                                                                                                                                                 | | mysql-bin.000060 |  386 | Query       |         1 |         638 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual | | mysql-bin.000060 |  638 | Query       |         1 |         714 | BEGIN                                                                                                                                                                                 | | mysql-bin.000060 |  714 | Table_map   |         1 |         768 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000060 |  768 | Update_rows |         1 |         890 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000060 |  890 | Xid         |         1 |         917 | COMMIT /* xid=5811 */                                                                                                                                                                 | | mysql-bin.000060 |  917 | Query       |         1 |        1169 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual | | mysql-bin.000060 | 1169 | Query       |         1 |        1245 | BEGIN                                                                                                                                                                                 | | mysql-bin.000060 | 1245 | Table_map   |         1 |        1299 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000060 | 1299 | Update_rows |         1 |        1421 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000060 | 1421 | Xid         |         1 |        1448 | COMMIT /* xid=5819 */                                                                                                                                                                 | | mysql-bin.000060 | 1448 | Query       |         1 |        1700 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Scroll to the right to find the unique value injected into the view's creation statement.

Does it replicate well? Looking at a slave's binary logs:

slave3 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000064'; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000064 |    4 | Format_desc |       103 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 | | mysql-bin.000064 |  107 | Query       |         1 |         166 | BEGIN                                                                                                                                                                                 | | mysql-bin.000064 |  166 | Table_map   |         1 |         220 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000064 |  220 | Update_rows |         1 |         342 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000064 |  342 | Xid         |         1 |         369 | COMMIT /* xid=3184 */                                                                                                                                                                 | | mysql-bin.000064 |  369 | Query       |         1 |         601 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual | | mysql-bin.000064 |  601 | Query       |         1 |         660 | BEGIN                                                                                                                                                                                 | | mysql-bin.000064 |  660 | Table_map   |         1 |         714 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000064 |  714 | Update_rows |         1 |         836 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000064 |  836 | Xid         |         1 |         863 | COMMIT /* xid=3194 */                                                                                                                                                                 | | mysql-bin.000064 |  863 | Query       |         1 |        1095 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual | | mysql-bin.000064 | 1095 | Query       |         1 |        1154 | BEGIN                                                                                                                                                                                 | | mysql-bin.000064 | 1154 | Table_map   |         1 |        1208 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       | | mysql-bin.000064 | 1208 | Update_rows |         1 |        1330 | table_id: 33 flags: STMT_END_F                                                                                                                                                        | | mysql-bin.000064 | 1330 | Xid         |         1 |        1357 | COMMIT /* xid=3198 */                                                                                                                                                                 | | mysql-bin.000064 | 1357 | Query       |         1 |        1589 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Not as pretty; I hate DDL operations that are not strictly required; but this solves the problem, plus rewriting the view means we're not littering the tablespace.

Next post will describe the steps towards achieving GTID-like behaviour based on the above.

 


PlanetMySQL Voting: Vote UP / Vote DOWN

MariaDB 10.1.1: FLUSH and SHOW for plugins

One of the most popular plugin types both in MariaDB and MySQL is INFORMATION_SCHEMA plugin type. INFORMATION_SCHEMA plugins add new tables to the INFORMATION_SCHEMA. There are lots of INFORMATION_SCHEMA plugins, because they can be used to show just anything to the user and are very easy to write.

MariaDB 10.1.1 comes with nine INFORMATION_SCHEMA plugin:

  • Feedback — shows the anonymised server usage information and can optionally send it to the configured url.
  • Locales — lists compiled-in server locales, implemented by Roberto Spadim
  • METADATA_LOCK_INFO — Lists metadata locks in the server. Implemented by Kentoku Shiba
  • QUERY_CACHE_INFO — Lists queries in the query cache. Originally by Roland Bouman
  • QUERY_RESPONSE_TIME — Shows distribution of query response times. Originally implemented in Percona
  • CLIENT_STATISTICS — part of Percona “userstat” patch
  • USER_STATISTICS — part of Percona “userstat” patch
  • INDEX_STATISTICS — part of Percona “userstat” patch
  • TABLE_STATISTICS — part of Percona “userstat” patch

Also there are many INFORMATION_SCHEMA plugins that come together with storage engines and show various information about them. InnoDB comes with 28 of such plugins. XtraDB — with 32, TokuDB — with 12. And if you google you can find many more INFORMATION_SCHEMA plugins — for example, a plugin that shows various system information or a plugin that lists all created user variables. INFORMATION_SCHEMA plugins are indeed numerous and very popular.

If you look at the these plugins, you can see that there are plugins that display some kind of a current state of something, and there are plugins that accumulate and display some statistics. For this second group of plugins it is sometimes desirable to reset this statistics — but there was no natural way for a user to do that. For status variables (as in SHOW STATUS statement) one can use FLUSH STATUS, but for plugins there was nothing similar. But now MariaDB 10.1.1 extends INFORMATION_SCHEMA plugin API by introducing reset_table callback. For example, look at the QUERY_RESPONSE_TIME plugin initialization function:

static int query_response_time_info_init(void *p) { ST_SCHEMA_TABLE *i_s_query_response_time= (ST_SCHEMA_TABLE *) p; i_s_query_response_time->fields_info= query_response_time_fields_info; i_s_query_response_time->fill_table= query_response_time_fill; i_s_query_response_time->reset_table= query_response_time_flush; query_response_time_init(); return 0; }

See the highlighted line — it sets the reset_table callback to the query_response_time_flush function. When this plugin is loaded, MariaDB will automatically start supporting new statement

FLUSH QUERY_RESPONSE_TIME;

and it will invoke this callback that will, in turn, reset query response time statistics. Similarly “userstat” tables CLIENT_STATISTICS, USER_STATISTICS, INDEX_STATISTICS, and TABLE_STATISTICS support the FLUSH statement in a similar way.

MariaDB 10.1.1 also implements another enhancement for the INFORMATION_SCHEMA plugins — the SHOW statement. Indeed, until 10.1.1 only native server INFORMATION_SCHEMA tables could have a SHOW counterpart. To query a plugin INFORMATION_SCHEMA table one would need to type a lengthy SELECT and than see how long lines wrap around in the terminal window, making it impossible to see what column each value belongs to. You all know it very well:

MariaDB [test]> select * from information_schema.locales; +-----+-------+-------------------------------------+-----------------------+- --------------------+---------------+--------------+------------------------+ | ID | NAME | DESCRIPTION | MAX_MONTH_NAME_LENGTH | MAX_DAY_NAME_LENGTH | DECIMAL_POINT | THOUSAND_SEP | ERROR_MESSAGE_LANGUAGE | +-----+-------+-------------------------------------+-----------------------+- --------------------+---------------+--------------+------------------------+ | 0 | en_US | English - United States | 9 | 9 | . | , | english | | 1 | en_GB | English - United Kingdom | 9 | 9 | . | , | english | | 2 | ja_JP | Japanese - Japan | 3 | 3 | . | , | japanese | | 3 | sv_SE | Swedish - Sweden | 9 | 7 | , | | swedish | | 4 | de_DE | German - Germany | 9 | 10 | , | . | german | ...

So we’ve introduced a SHOW statement for INFORMATION_SCHEMA plugins that is quick to type and is supposed to provide just enough columns to fit nicely on the screen:

MariaDB [test]> show locales; +-----+-------+-------------------------------------+------------------------+ | Id | Name | Description | Error_Message_Language | +-----+-------+-------------------------------------+------------------------+ | 0 | en_US | English - United States | english | | 1 | en_GB | English - United Kingdom | english | | 2 | ja_JP | Japanese - Japan | japanese | | 3 | sv_SE | Swedish - Sweden | swedish | | 4 | de_DE | German - Germany | german | ...

The API is very simple, a plugin does not need to implement anything special to support this. It only needs to decide what subset of columns will be visible in the SHOW statement and specify names for this columns when declaring INFORMATION_SCHEMA table fields. For example, in the LOCALES plugin:

static ST_FIELD_INFO locale_info_locale_fields_info[]= { {"ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Id", 0}, {"NAME", 255, MYSQL_TYPE_STRING, 0, 0, "Name", 0}, {"DESCRIPTION", 255, MYSQL_TYPE_STRING, 0, 0, "Description", 0}, {"MAX_MONTH_NAME_LENGTH", 4, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, {"MAX_DAY_NAME_LENGTH", 4, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, {"DECIMAL_POINT", 2, MYSQL_TYPE_STRING, 0, 0, 0, 0}, {"THOUSAND_SEP", 2, MYSQL_TYPE_STRING, 0, 0, 0, 0}, {"ERROR_MESSAGE_LANGUAGE", 64, MYSQL_TYPE_STRING, 0, 0, "Error_Message_Language", 0}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} };

See, the highlighted lines specify column names for the SHOW statement and only these columns are visible in SHOW. It is, of course, a sole responsibility of the plugin writer to pick up a reasonable subset of columns so that the resulting table is not too wide but still contains enough information to be useful. If a plugin does not specify any column names for SHOW, the SHOW statement will not work for this plugin.

An inquisitive reader may note that “userstat” tables CLIENT_STATISTICS, USER_STATISTICS, INDEX_STATISTICS, and TABLE_STATISTICS supported FLUSH and SHOW also in 10.0 and in earlier versions of MariaDB. That’s right, but before 10.1.1 these tables were not plugins, and the syntax support was hard-coded into the server. Now they are plugins which, indeed, required INFORMATION_SCHEMA plugins to support FLUSH and SHOW. That’s why these extensions were implemented.


PlanetMySQL Voting: Vote UP / Vote DOWN

Shinguz: Get rid of wrongly deleted InnoDB tables

Taxonomy upgrade extras: BackupRestoreRecoveryinnodbtable

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.* Analysis

We do some analysis first:

mysql> DROP TABLE test; ERROR 1051 (42S02): Unknown table 'test' mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following information:

141022 17:09:04 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 141022 17:09:04 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './test/test.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue.
Fix

User claims that he does NOT need the table and/or the data any more but wants to get rid of the error messages and/or create a new table with the same name.

mysql> CREATE SCHEMA recovery; mysql> use recovery mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; mysql> \! cp $datadir/recovery/test.frm $datadir/test/ mysql> DROP SCHEMA recovery; mysql> use test mysql> DROP TABLE test; Prove

To prove it works we create a new table and fill in some records:

mysql> CREATE TABLE test (id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(64), ts TIMESTAMP) ENGINE = InnoDB; mysql> INSERT INTO test VALUES (NULL, 'Test data', NULL); Literature
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL sys version 1.2.0 released

MySQL sys version 1.2.0 has just been released.

All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes.

There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that.

Here’s a full summary of the other changes:

Backwards Incompatible Changes

  • The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency.
  • The host_summary_by_file_io_type and user_summary_by_file_io_type latency column was renamed to total_latency, for consistency.

Improvements

  • Made the truncation length for the format_statement view configurable. This includes adding a new persistent sys_config table to store the new variable – statement_truncate_len – see the README for usage
  • Added total_latency to the schema_tables_with_full_table_scans view, and added an x$ counterpart
  • Added innodb_buffer_free to the schema_table_statistics_with_buffer view, to summarize how much free space is allocated per table in the buffer pool
  • The schema_unused_indexes view now ignores indexes named PRIMARY (primary keys)
  • Added rows_affected and rows_affected_avg stats to the statement_analysis views
  • The statements_with_full_table_scans view now ignores any SQL that starts with SHOW
  • Added a script, generate_sql_file.sh, that can be used to generate a single SQL file, also allowing substitution of the MySQL user to use, and/or whether the SET sql_log_bin … statements should be omitted. This is useful for those using RDS, where the root@localhost user is not accessible, and sql_log_bin is disabled (Issue #5)
  • Added a set of memory_by_thread_by_current_bytes views, that summarize memory usage per thread with MySQL 5.7′s memory instrumentation
  • Improved each of the host specific views to return aggregate values for background threads, instead of ignoring them, in the same way as the user summary views

Bug Fixes

  • Added the missing memory_by_host view for MySQL 5.7
  • Added missing space for hour notation within the format_time function
  • Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Crash-safe replication, Binlog Servers and Percona Live London

I just publish a post on the Booking.com blog: http://blog.booking.com/better_crash_safe_replication_for_mysql.html  Spoiler: it uses Binlog Servers.

This is also the opportunity to tell you that I will be at Percona Live London at the beginning of November, and that I will give a talk about Binlog Servers: High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers.  I will not talk too much about Binlog Server for crash-safe replication, but I will present a new use-case for Binlog Servers that I did not blog about yet.  I am looking forward to meet you there.
PlanetMySQL Voting: Vote UP / Vote DOWN

libAttchSQL Hits First GA!

We have come a long way since the first code was put down for libAttachSQL on the 4th July. It has been a fantastic project to work on so I am very pleased to announce our first GA release.

For those who haven't seen it so far libAttachSQL is a non-blocking, lightweight C API for MySQL servers. It is Apache 2.0 licensed so is compatible with most Open Source and commercial licensing. HP's Advanced Technology Group saw a need in this field not just for HP itself but for other companies and projects too.

As for the GA release itself, there are not many changes over the RC release beyond stability fixes. A full list can be seen in the version history documentation.

In addition to the GA release we have recently had a driver for Sysbench merged into their trunk so libAttachSQL can be used for benchmarking MySQL servers. We have also started work on a tool called AttachBench which when complete will run similar MySQL tests as Sysbench but will allow for multiple connections per thread (something libAttachSQL excels at). At the moment AttachBench requires the tables from Sysbench's "Select" test already setup and I don't recommend tinkering with it yet unless you don't mind getting a bit dirty.

With the release of libAttachSQL 1.0.0 we have also launched a new website on libattachsql.org. It is a basic Pelican based site (very much like this blog) but will make it much easier for anyone to add content, just like this blog all the source is in RST files on GitHub.

Download links for libAttachSQL 1.0.0 can be found on the News section of the project website. There is a source package as well as packages for RHEL/CentOS 6.x and 7.x. Packages for Ubuntu 12.04 and 14.04 are waiting to be built in the PPA at time of posting. We hope to have releases for more operating systems in the near future.

Rest assured we are not stopping here. I already have ideas of what I want to see in 1.1 and we have some spin-off projects planned. If you would like to learn more please come along to my talk on libAttachSQL at Percona Live London. I'm also talking to several people outside of HP to see what they would like in libAttachSQL and am happy to talk to anyone else who wants to know more and has feedback.

Many thanks to everyone who has helped us get this far.


PlanetMySQL Voting: Vote UP / Vote DOWN

Why I moved my Joomla website to MariaDB

Wed, 2014-10-22 09:02svetoslavsavov

Next time you want to build a fast, well-optimized website, don't forget the database system. Many content management systems (CMS) let you choose a relational database management system (RDBMS) to use on the back end. MySQL, famous for its stability and security, is a popular choice; in addition to its numerous features, it has a large community, many contributors, and good documentation. However, MySQL is now owned by Oracle, and its future is not clear, which makes MariaDB, an application compatible database system, an excellent alternative.

Joomla, a popular CMS, is written in PHP and by default uses MySQL as its database system. Since MariaDB can provide improved functionality, performance, and stability, you might want to use MariaDB instead of MySQL with Joomla. Although MariaDB is not listed in Joomla's technical requirements, it is safe to migrate your Joomla site's database to it.

I'll walk through the process on a CentOS server, since it is a popular distribution for hosting web servers. While the syntax might differ slightly for other Linux distributions, the algorithm is the same.

Before you migrate, you might want to do some benchmarks, so you can see whether your efforts have led to improvements. You can turn on the Joomla debugging option through the admin's back end or by executing the command # sed -i "s@\$debug = '0';@\$debug = '1';@" configuration.php. After that, you can load the front page of your website and see the full list of the queries that are run. Navigate to the bottom of the page, click on the Database Queries link and the queries will be listed. Copy them and turn off the debugging if you are benchmarking your live website; if you don't, your visitors will see its output after each page's footer. Then use a text editor like vim or nano to paste them in your test .sql file, put each query on a single line, removing unnecessary blank spaces in them – for example, the format should be as follows:

SELECT folder AS type, element AS name, params FROM gpa_extensions WHERE enabled >= 1 AND type ='plugin' AND state >= 0 AND access IN (1,1) ORDER BY ordering

– and save the SQL file. To run a benchmark, use a database server load emulator called mysqlslap, like this:

# mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.275 seconds Minimum number of seconds to run all queries: 0.233 seconds Maximum number of seconds to run all queries: 0.351 seconds Number of clients running queries: 100 Average number of queries per client: 3

That command simulates 100 MySQL clients that concurrently run several sample SELECT queries grabbed from the debugging database queries output and saved in the several_joomla_queries.sql file on your Joomla database. The connection to the database is established with the database name, the associated database username and the password specified in the Joomla’s configuration.php file. The queries in my test are a small excerpt from the Joomla debugging tool database queries list, which I specified should iterate 10 times. Once the migration to MariaDB is completed, you can perform the same test and compare the results, since the load emulator is replicated in MariaDB.

Now you can proceed with the migration. Create a full backup of the database files and your MySQL configuration file, just in case something goes wrong, then stop the MySQL server:

# cp /var/lib/mysql/ mysql_backup -r # cp /etc/my.cnf /etc/my.cnf_backup # service mysql stop Shutting down MySQL (Percona Server)... SUCCESS!

Next, remove the existing MySQL 5.5 installation. First, list the packages that you plan to remove (rpm -qa | grep -i mysql-). Then complete the actual process; use the following command with caution: # for i in `rpm -qa | grep -i mysql-`; do rpm -e --nodeps $i; done.

Next, generate a MariaDB repository file for your architecture and place it under the /etc/yum.repos.d/ folder:

# cat /etc/yum.repos.d/MariaDB.repo # MariaDB 5.5 CentOS repository list - created 2014-09-28 19:49 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

Install the MariaDB 5.5 server and client with the command # yum install MariaDB-server MariaDB-client. . Since MariaDB is a drop-in replacement for MySQL, the Joomla website should now work without further modifications. Clear your web browser's cache and test it. If Joomla works, you can run the benchmark again. When I did that, I saw better results with my benchmark test queries:

# mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.185 seconds Minimum number of seconds to run all queries: 0.122 seconds Maximum number of seconds to run all queries: 0.237 seconds Number of clients running queries: 100 Average number of queries per client: 3

As you can see, in a very short time, replacing MySQL with a fast and reliable database system like MariaDB can improve a database application's user experience.

Tags: CMSHowto About the Author Svetoslav Savov

Svetoslav Savov an a Linux enthusiast with more than 8 years of experience on a Senior position in one of the leading web hosting companies. He has a master degree in the Computer networks and enjoys working with open-source software solutions. In his spare time he practices extreme sports and travels around the world. e-mail address: svetlio_81@yahoo.com


PlanetMySQL Voting: Vote UP / Vote DOWN

Pseudo GTID

Pseudo GTID is a method to implement a GTID-like solution where slaves are easily connected to one another. This blog post and the following ones will describe work in progress (some 80% completed), where simulation of GTID makes for a good enough basis for refactoring replication topologies. I'm coding this in orchestrator, which already provides a substantial infrastructure support for this.

The final goal: orchestrator will allow you to move a slave below another, using only the data available by those two slaves. The usage is obvious:

  • Easy master failover (master dead? Orchestrator will choose the most advanced slave to promote and make it master of its siblings)
  • Slave promotion in complex topologies (with deep nested topologies, be able to move a slave up the hierarchy even if its local master is corrupted).

This can all happen with your normal, non GTID, MySQL replication, using your normal binary log files & positions.

This work in progress is inspired by Sam Lambert at GitHub, who has worked on a similar solution with different implementation. I also recall discussions with other DBAs having similar solution.

Pseudo GTID

First thing's first, the basis for proposed solution is a pseudo-GTID. A unique entry in the binary logs (not necessarily sequential; not necessarily in ascending order). While in GTID implementations we have a unique identifier for each entry in the binary log, with pseudo-GTID we accept an occasional (or frequent) unique entry in the binary log.

There are many ways to do so. Certainly a client can generate a unique Id and invoke some statement on MySQL involving that ID. That would serve as valid grounds for the proposed solution. But I like things to be contained within MySQL. Consider, for example, the following event, which would be my preferred choice in Statement Based Replication (for RBR solution, see next post):

drop table if exists test.pseudo_gtid; create table if not exists test.pseudo_gtid (   id int unsigned not null primary key,   ts timestamp,   gtid varchar(64) charset ascii ); drop event if exists test.update_pseudo_gtid_event; delimiter ;; create event if not exists   test.update_pseudo_gtid_event   on schedule every 10 second starts current_timestamp   on completion preserve   enable   do     begin       set @pseudo_gtid := uuid();       insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid);     end ;; delimiter ;

The above is based on Making UUID() and RAND() replication safe. What do we get? Once in 10 seconds (or what have you), a unique entry is written to the binary log.

Consider that the event is already running by now, and the next conventional statements executed by the application:

master [localhost] {msandbox} (test) > create table test.vals(id int); master [localhost] {msandbox} (test) > insert into test.vals (id) values (17); master [localhost] {msandbox} (test) > insert into test.vals (id) values (18); master [localhost] {msandbox} (test) > insert into test.vals (id) values (19); master [localhost] {msandbox} (test) > insert into test.vals (id) values (23); master [localhost] {msandbox} (test) > show master logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000036 |       531 | | mysql-bin.000037 |      1269 | | mysql-bin.000038 |      6627 | | mysql-bin.000039 |      3313 | +------------------+-----------+

Let's look at the binary logs content:

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000039'; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                        | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000039 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                       | | mysql-bin.000039 |  107 | Query       |         1 |         183 | BEGIN                                                                                                                                       | | mysql-bin.000039 |  183 | User var    |         1 |         263 | @`pseudo_gtid`=_utf8 0x37383435623633382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 |  263 | Query       |         1 |         461 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 |  461 | Xid         |         1 |         488 | COMMIT /* xid=74 */                                                                                                                         | | mysql-bin.000039 |  488 | Query       |         1 |         581 | use `test`; create table test.vals(id int)                                                                                                  | | mysql-bin.000039 |  581 | Query       |         1 |         657 | BEGIN                                                                                                                                       | | mysql-bin.000039 |  657 | User var    |         1 |         737 | @`pseudo_gtid`=_utf8 0x37653362616434382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 |  737 | Query       |         1 |         935 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 |  935 | Xid         |         1 |         962 | COMMIT /* xid=82 */                                                                                                                         | | mysql-bin.000039 |  962 | Query       |         1 |        1038 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 1038 | User var    |         1 |        1118 | @`pseudo_gtid`=_utf8 0x38343331396662332D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 | 1118 | Query       |         1 |        1316 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 | 1316 | Xid         |         1 |        1343 | COMMIT /* xid=84 */                                                                                                                         | | mysql-bin.000039 | 1343 | Query       |         1 |        1411 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 1411 | Query       |         1 |        1512 | use `test`; insert into test.vals (id) values (17)                                                                                          | | mysql-bin.000039 | 1512 | Xid         |         1 |        1539 | COMMIT /* xid=84 */                                                                                                                         | | mysql-bin.000039 | 1539 | Query       |         1 |        1607 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 1607 | Query       |         1 |        1708 | use `test`; insert into test.vals (id) values (18)                                                                                          | | mysql-bin.000039 | 1708 | Xid         |         1 |        1735 | COMMIT /* xid=85 */                                                                                                                         | | mysql-bin.000039 | 1735 | Query       |         1 |        1803 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 1803 | Query       |         1 |        1904 | use `test`; insert into test.vals (id) values (19)                                                                                          | | mysql-bin.000039 | 1904 | Xid         |         1 |        1931 | COMMIT /* xid=86 */                                                                                                                         | | mysql-bin.000039 | 1931 | Query       |         1 |        2007 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 2007 | User var    |         1 |        2087 | @`pseudo_gtid`=_utf8 0x38613237376232352D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 | 2087 | Query       |         1 |        2285 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 | 2285 | Xid         |         1 |        2312 | COMMIT /* xid=89 */                                                                                                                         | | mysql-bin.000039 | 2312 | Query       |         1 |        2380 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 2380 | Query       |         1 |        2481 | use `test`; insert into test.vals (id) values (23)                                                                                          | | mysql-bin.000039 | 2481 | Xid         |         1 |        2508 | COMMIT /* xid=89 */                                                                                                                         | | mysql-bin.000039 | 2508 | Query       |         1 |        2584 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 2584 | User var    |         1 |        2664 | @`pseudo_gtid`=_utf8 0x39303164373731612D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 | 2664 | Query       |         1 |        2862 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 | 2862 | Xid         |         1 |        2889 | COMMIT /* xid=92 */                                                                                                                         | | mysql-bin.000039 | 2889 | Query       |         1 |        2965 | BEGIN                                                                                                                                       | | mysql-bin.000039 | 2965 | User var    |         1 |        3045 | @`pseudo_gtid`=_utf8 0x39363133363965382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000039 | 3045 | Query       |         1 |        3243 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000039 | 3243 | Xid         |         1 |        3270 | COMMIT /* xid=94 */                                                                                                                         | | mysql-bin.000039 | 3270 | Rotate      |         1 |        3313 | mysql-bin.000040;pos=4                                                                                                                      | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

Marked in bold are the pseudo-GTID statements, aptly read "@`pseudo_gtid`=_utf8 0x...", and which are the resulting entry of the set @pseudo_gtid := uuid(); statement. These are interleaved with our normal statements. In busier servers there could be hundreds or thousands of statements between any two pseudo-GTID entries.

We have a replicating slave to the above, which uses log_slave_updates. For reasons to be explained later, I prefer and require log_slave_updates, and will examine the slave's binary logs (instead of directly looking at the slave's relay logs):

slave3 [localhost] {msandbox} ((none)) > show master logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000046 |      1077 | | mysql-bin.000047 |       126 | | mysql-bin.000048 |       150 | | mysql-bin.000049 |       150 | | mysql-bin.000050 |     13860 | | mysql-bin.000051 |       107 | +------------------+-----------+ slave3 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000051'; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                        | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000051 |    4 | Format_desc |       103 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                       | | mysql-bin.000051 |  107 | Query       |         1 |         174 | BEGIN                                                                                                                                       | | mysql-bin.000051 |  174 | User var    |         1 |         254 | @`pseudo_gtid`=_utf8 0x37383435623633382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000051 |  254 | Query       |         1 |         452 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000051 |  452 | Xid         |         1 |         479 | COMMIT /* xid=2141 */                                                                                                                       | | mysql-bin.000051 |  479 | Query       |         1 |         572 | use `test`; create table test.vals(id int)                                                                                                  | | mysql-bin.000051 |  572 | Query       |         1 |         639 | BEGIN                                                                                                                                       | | mysql-bin.000051 |  639 | User var    |         1 |         719 | @`pseudo_gtid`=_utf8 0x37653362616434382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000051 |  719 | Query       |         1 |         917 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000051 |  917 | Xid         |         1 |         944 | COMMIT /* xid=2150 */                                                                                                                       | | mysql-bin.000051 |  944 | Query       |         1 |        1011 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 1011 | User var    |         1 |        1091 | @`pseudo_gtid`=_utf8 0x38343331396662332D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000051 | 1091 | Query       |         1 |        1289 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000051 | 1289 | Xid         |         1 |        1316 | COMMIT /* xid=2152 */                                                                                                                       | | mysql-bin.000051 | 1316 | Query       |         1 |        1375 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 1375 | Query       |         1 |        1476 | use `test`; insert into test.vals (id) values (17)                                                                                          | | mysql-bin.000051 | 1476 | Xid         |         1 |        1503 | COMMIT /* xid=2154 */                                                                                                                       | | mysql-bin.000051 | 1503 | Query       |         1 |        1562 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 1562 | Query       |         1 |        1663 | use `test`; insert into test.vals (id) values (18)                                                                                          | | mysql-bin.000051 | 1663 | Xid         |         1 |        1690 | COMMIT /* xid=2156 */                                                                                                                       | | mysql-bin.000051 | 1690 | Query       |         1 |        1749 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 1749 | Query       |         1 |        1850 | use `test`; insert into test.vals (id) values (19)                                                                                          | | mysql-bin.000051 | 1850 | Xid         |         1 |        1877 | COMMIT /* xid=2158 */                                                                                                                       | | mysql-bin.000051 | 1877 | Query       |         1 |        1944 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 1944 | User var    |         1 |        2024 | @`pseudo_gtid`=_utf8 0x38613237376232352D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000051 | 2024 | Query       |         1 |        2222 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000051 | 2222 | Xid         |         1 |        2249 | COMMIT /* xid=2160 */                                                                                                                       | | mysql-bin.000051 | 2249 | Query       |         1 |        2308 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 2308 | Query       |         1 |        2409 | use `test`; insert into test.vals (id) values (23)                                                                                          | | mysql-bin.000051 | 2409 | Xid         |         1 |        2436 | COMMIT /* xid=2162 */                                                                                                                       | | mysql-bin.000051 | 2436 | Query       |         1 |        2503 | BEGIN                                                                                                                                       | | mysql-bin.000051 | 2503 | User var    |         1 |        2583 | @`pseudo_gtid`=_utf8 0x39303164373731612D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     | | mysql-bin.000051 | 2583 | Query       |         1 |        2781 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) | | mysql-bin.000051 | 2781 | Xid         |         1 |        2808 | COMMIT /* xid=2164 */                                                                                                                       | | mysql-bin.000051 | 2808 | Rotate      |       103 |        2851 | mysql-bin.000052;pos=4                                                                                                                      | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

We can see the "@`pseudo_gtid`=_utf8 0x" entries are replicated well, and are identical throughout the topology (this continue to work well on second-level slaves etc.).

To be continued.

 


PlanetMySQL Voting: Vote UP / Vote DOWN

MariaDB 10.1.1: Compound statements

Every now and then there is a need to execute certain SQL statements conditionally. Easy, if you do it from your PHP (or Java or whatever) application. But if all you have is pure SQL? There are two techniques that MariaDB and MySQL use in the mysql_fix_privilege_tables.sql script (applied by mysql_upgrade tool).

  1. Create a stored procedure with IF statements inside, call it once and drop it. This requires the user to have the CREATE ROUTINE privilege and mysql.proc table must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
  2. Use dynamic SQL, like
    SET @str = IF (@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log ( event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL ) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; DROP PREPARE stmt;
    which is not very readable and doesn’t work well if you need to execute many statements conditionally.

This may be standard, but I never understood why one cannot simply use SQL control statements (besides CALL that is) directly from the mysql command line tool prompt. Imagine a bash variant that only supports if and while in scripts, but not in the interactive mode from the command line — how would you like it?

May be Antony Curtis was asking himself similar questions when he contributed a patch for compound statements in MDEV-5317. Either way, we thought it’s a great idea and implemented this feature, based on the Antony’s contribution.

Now one can use BEGIN, IF, CASE, WHILE, LOOP, REPEAT statements directly in SQL scripts and from the mysql command line prompt — outside of stored programs. For example, one can rewrite the above as

IF @have_csv = 'YES' THEN CREATE TABLE IF NOT EXISTS general_log ( event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL ) engine=CSV CHARACTER SET utf8 comment="General log"; END IF

One can use BEGIN ... END blocks and loops without having CREATE ROUTINE privilege or with corrupted (or missing mysql.proc table). This all works as you would expect it to, with no artificial “standard says so” limitations.

Still, there are some limitations to keep in mind:

  • You cannot use a simple BEGIN to start a block, this is historically used to start a transaction. Use the standard syntax BEGIN NOT ATOMIC.
  • Compound statements from the mysql command line prompt cannot start with a label.
  • Not all statements that can be used in the stored program are supported from the mysql command line prompt, only those listed above are.

These limitations, though, only apply to the top-level statement. For example, if you need a labeled loop or SIGNAL, you start a block and put your statement inside it:

BEGIN NOT ATOMIC DECLARE v1 INT DEFAULT 500; label1: WHILE v1 > 0 DO INSERT t1 (v) VALUES (v1); SET v1 = v1 - 1; IF (SELECT COUNT(*) FROM t1) > 500 THEN LEAVE label1; END IF; END WHILE label1; END

Enjoy!


PlanetMySQL Voting: Vote UP / Vote DOWN

Percona XtraDB Cluster: How to run a 2-node cluster on a single server

I reckon there’s little sense in running 2 or more Percona XtraDB Cluster (PXC) nodes in a single physical server other than for educational and testing purposes – but doing so is still useful in those cases. The most popular way of achieving this seems to be with server virtualization, such as making use of Vagrant boxes. But in the same way you can have multiple instances of MySQL running in parallel on the OS level in the form of concurrent mysqld processes, so too can you have multiple Percona XtraDB Cluster nodes. And the way to achieve this is precisely the same: using dedicated datadirs and different ports for each node.

 

Which ports?4 tcp ports are used by Pecona XtraDB Cluster:
  • the regular MySQL port (default 3306)
  • port for group (Galera) communication (default 4567)
  • port for State Transfer (default 4444)
  • port for Incremental State Transfer (default is: port for group communication (4567) + 1 = 4568)
Of course, when you have multiple instances in the same server default values won’t work for all of them so we need to define new ports  for the additional instances and make sure to have the local firewall open to them, if there is one active (iptables, selinux,…).

[{ loading ... }]

Installing Percona XtraDB Cluster, configuring and starting the first nodeMy test server was a fresh CentOS 6.5 configured with Percona yum repository, from which I installed the latest Percona XtraDB Cluster (5.6.20-25.7.888.el6); note that you’ll need the EPEL repository as well to install socat, which is a dependency (see this bug). To avoid confusion, I’ve prevented the mysql service to start automatically:chkconfig --level 3 mysql off chkconfig --del mysql
I could have installed PXC from the tarball but I decided to do it from the repositories to have all dependencies covered by yum. This is how my initial /etc/my.cnf looked like (note the use of default values):

[mysqld] datadir = /var/lib/mysql port=3306 socket=/var/lib/mysql/mysql-node1.sock pid-file=/var/lib/mysql/mysql-node1.pid log-error=/var/lib/mysql/mysql-node1.err binlog_format=ROW innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_name = singlebox wsrep_node_name = node1 wsrep_cluster_address=gcomm://I’ve started by manually bootsrapping the cluster with this single node with the command:$ mysqld_safe --defaults-file=/etc/my.cnf --wsrep-new-cluster
You should then be able to access this node through the local socket:

$ mysql -S /var/lib/mysql/mysql-node1.sock

 

Configuring and starting the second nodeThen I created a similar configuration configuration file for the second instance, which I named /etc/my2.cnf, with the following modifications:[mysqld] datadir = /var/lib/mysql2 port=3307 socket=/var/lib/mysql2/mysql-node2.sock pid-file=/var/lib/mysql2/mysql-node2.pid log-error=/var/lib/mysql2/mysql-node2.err binlog_format=ROW innodb_autoinc_lock_mode=2 wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_name = singlebox wsrep_node_name = node2 wsrep_cluster_address=gcomm://127.0.0.1:4567,127.0.0.1:5020 wsrep_provider_options = "base_port=5020;"
Note the use of base_port: by having it defined, port 5020 is used for group communication and 5021 (the one above it) is reserved for IST (it’s the same as using gmcast.listen_addr=tcp://127.0.0.1:5021, just simpler).

You need to create and setup the right permissions to the datadir on this second instance, otherwise MySQL won’t be able to create some files (like .pid and .err), though you don’t need to run the mysql_install_db script:$ chown -R mysql:mysql /var/lib/mysql2You can then start this second instance with the following command:$ mysqld_safe --defaults-file=/etc/my2.cnfWhile it starts, watch the log to observe how this second node starts, communicates with the primary node and join the cluster. On a different terminal from the one you’ve started the instance, execute:$ tail -f /var/log/mysql2/mysql-node2.errRemember that at any time you can use mysqladmin to stop the nodes, you only need to provide the right socket as argument, like follows:$ mysqladmin -S /var/lib/mysql/mysql-node1.sock shutdownFinally, once you have the whole cluster up you should edit the my.cnf of the first node with a complete wsrep_cluster_addres, as show in /etc/my2.cnf above.

 

Using mysqld_multiMy last blog post was on running multiple instances of MySQL with myslqd_multi. It applies here as well, the only exception is that you need to make sure to use “wsrep_cluster_address=gcomm://” in the first node whenever you bootstrap the cluster – and pay attention to start it before the other nodes.The only advantage I see in using mysqld_multi is facilitating the management (start/stop) of the nodes and concentrating all configuration in a single my.cnf file. In any case, you shouldn’t be running a PXC cluster in a single box for any purpose other than educational.

 

Adding a second Percona XtraDB Cluster node to a production serverWhat if you have a production cluster composed of multiple physical servers and you want to add a second node to one of them? It works the same way – you’ll just need to use the server’s IP address when configuring it instead of the loopback network interface. Here’s an example of a PXC cluster composed initially by three nodes: 192.168.70.1, 192.168.70.2, and 192.168.70.3. I’ve added a 4th node running on the server that is already hosting the 3rd – the wsrep_cluster_address line looks like as follows after the changes:wsrep_cluster_address = gcomm://192.168.70.1,192.168.70.2,192.168.70.3:4567,192.168.70.3:5020

 

Additional ressourcesWe have a documentation page on “How to setup 3 node cluster on single box” that contains more details of what I’ve covered above with a slightly different approach.

 

The post Percona XtraDB Cluster: How to run a 2-node cluster on a single server appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Pages