]]>
]]>

You are here

Feed aggregator

Bootstrap cluster when DNS entry is empty?

Lastest Forum Posts - March 23, 2015 - 6:53pm
Hi, I'm using XtraDB 5.6 with consul for service discovery via DNS and the cluster is failing to bootstrap when there are no nodes yet available.

In my.cnf I have this set up on all nodes, and it works great when the cluster is bootstrapped on one node: wsrep_cluster_address=gcomm://db.service.consul

With the above, I can automatically add any number of new nodes to my cluster without intervention.
But when all three of my nodes are down I get this in my error.log and the cluster fails to bootstrap: "Failed to resolve tcp://db.service.consul:4567" and my cluster won't bootstrap.

I guess this isn't totally unexpected behavior, but if I were to leave my cluster_address as empty gcomm:// my cluster bootstraps as expected, and since DNS is returning 0 node addresses I would think the node would bootstrap itself.


Can anyone think of a workaround for this, or is this a bug?

Thanks for your time and input!

Percona Master-Master lag issues

Lastest Forum Posts - March 23, 2015 - 7:53am
I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation.

What could be causing this lag? It rises sometimes up to >1000 secs of lag and sunddenly it fells to 0 secs. I have checked it with Percona's pt-heartbeat tool and is real lag.

This are the server configurations, both have 8 cores, 32GB RAM and SSD disks:

DB-01 (Xeon E3-1245 V2 @ 3.40GHz)
: [mysqld] server-id = 1 log_bin = /var/lib/mysql/mysql-bin.log binlog-ignore-db = mysql binlog-ignore-db = test datadir = /var/lib/mysql expire-logs-days = 10 auto_increment_increment= 2 auto_increment_offset = 1 max_connections=3000 query_cache_size=0 query_cache_type=0 innodb_file_per_table innodb_file_format=barracuda innodb_flush_method=O_DIRECT innodb_log_file_size=128M innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=1 sync_binlog=1 # ~90% of memory innodb_buffer_pool_size=27000M # 4 * # of CPU cores innodb_read_io_threads=32 innodb_write_io_threads=32 innodb_io_capacity = 5000
DB-02 (i7-4770 CPU @ 3.40GHz)
: // same that db-01 but with this options different: server-id = 2 auto_increment_offset = 2 The output of SHOW SLAVE STATUS on each server:

DB-01
: Slave_IO_State: Waiting for master to send event Master_Host: db-02 Master_Log_File: mysql-bin.000022 Read_Master_Log_Pos: 3348962 Relay_Log_File: db-01-relay-bin.000042 Relay_Log_Pos: 3349125 Relay_Master_Log_File: mysql-bin.000022 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3348962 Relay_Log_Space: 3349462 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Seconds_Behind_Master: 0 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: DB-02
: Slave_IO_State: Waiting for master to send event Master_Host: db-01 Master_Log_File: mysql-bin.000019 Read_Master_Log_Pos: 38905060 Relay_Log_File: db-02-relay-bin.000048 Relay_Log_Pos: 17834447 Relay_Master_Log_File: mysql-bin.000019 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 28196753 Relay_Log_Space: 28542928 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Seconds_Behind_Master: 310 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp:

pt-online-schema-change concepts

Lastest Forum Posts - March 23, 2015 - 2:59am
Hi

I am reading the doc for the relevant tool in order to use it, well, actually I have performed a test with great success but when i came across to tables with foreign keys i got troubled . So i have a few questions on the subject which i don’t understand:

1) "When the tool renames the original table to let the new one take its place, the foreign keys “follow” the renamed table, and must be changed to reference the new table instead." I dont understand this. I mean, why does the child table "follows" the old table? The way I think of it , the child table will look for the foreign keys according to the table name (right?), and since the new table (the altered) was renamed and has the proper name, how come the child table tries to reference the foreign keys to the old one. Also, as it is stated on the manual, the old table is dropped which means the child table tries to reference a table that doesn’t exist?

What change on the foreign keys will be made on the child table? I mean, the table name and the rows are the same ...

I am not an expert of mysql so excuse me in advance if i ask something obvious.

Recovering data from a corrupt embedded MySQL Server.

Lastest Forum Posts - March 22, 2015 - 10:30pm
Dear All, I have inherited an embedded database from a customer, which seems apparently encrypted. You cannot read the data from the FRM and MYD & MYI files by copying them into the mysql directory since we get an 'Incorrect Information from .... table_name.frm' file. I have worked on this fulltime since 3 days and I'm getting nowhere. Is there a possibility to extract data from the MYD file alone ? I know the FRM file is encrypted, and the former software can still read the data from the database. Any pointers will be highly welcomed.

Thanks

Cannot find MYSQL extension

Lastest Forum Posts - March 22, 2015 - 6:39am
Hello,

I installed Percona following the directions at https://documentation.cpanel.net/dis...L+with+Percona. However, now my website says: "Your PHP installation appears to be missing the MySQL extension which is required by WordPress.". What is up?

Thanks!

Failing to connect 2nd node

Lastest Forum Posts - March 21, 2015 - 9:45pm
I'm trying to bring up a xtradb cluster of 2 nodes running on two separate Ubuntu 12.04 servers, I was able to install Percona on the 1st node and get the service to start, however, when I try to start the service for the 2nd node, the service always fails to start.

In the below error.log file for the joiner, I'm going some error's which I can't figure out how to solve. I can see that the joiner node (dg-mysql02 does know about the other node in the cluster, dg-mysql01, but it seems like there is an issue when they try to sync.

Any help is appreciated.
WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /var/lib/mysql//.sst from previous state transfer (20150322 04:38:24.233)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20150322 04:38:24.239)
WSREP_SST: [INFO] Proceeding with SST (20150322 04:38:24.255)
WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20150322 04:38:24.258)
WSREP_SST: [INFO] Cleaning the binlog directory /var/log/mysql as well (20150322 04:38:24.275)
rm: cannot remove `/var/log/mysql/*.index': No such file or directory
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20150322 04:38:24.283)
2015-03-22 04:38:25 31140 [Note] WSREP: (4535124a, 'tcp://0.0.0.0:4567') turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20150322 04:38:34.422)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20150322 04:38:34.425)
2015-03-22 04:38:34 31140 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'dg-mysql02' --auth 'sstuser:s3cretPass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '31140' --binlog '/var/log/mysql/mysql-bin' : 2 (No such file or directory)
2015-03-22 04:38:34 31140 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2015-03-22 04:38:34 31140 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2015-03-22 04:38:34 31140 [ERROR] Aborting

2015-03-22 04:38:34 31140 [Warning] WSREP: 1.1 (dg-mysql01.reston.visp.verizon.com): State transfer to 0.1 (dg-mysql02.reston.visp.verizon.com) failed: -22 (Invalid argument)
2015-03-22 04:38:34 31140 [ERROR] WSREP: gcs/src/gcs_group.cpp:int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():731: Will never receive state. Need to abort.
2015-03-22 04:38:34 31140 [Note] WSREP: gcomm: terminating thread
2015-03-22 04:38:34 31140 [Note] WSREP: gcomm: joining thread
2015-03-22 04:38:34 31140 [Note] WSREP: gcomm: closing backend
2015-03-22 04:38:35 31140 [Note] WSREP: view(view_id(NON_PRIM,4535124a,12) memb {
4535124a,1
} joined {
} left {
} partitioned {
65bd84ba,1
})
2015-03-22 04:38:35 31140 [Note] WSREP: view((empty))
2015-03-22 04:38:35 31140 [Note] WSREP: gcomm: closed
2015-03-22 04:38:35 31140 [Note] WSREP: /usr/sbin/mysqld: Terminated.
Aborted
150322 04:38:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

MySQL fragmentation(reorg)

Lastest Forum Posts - March 20, 2015 - 1:53am
Hello
This is my first question. ^^

I have a question about the fragmentation (reorg) in mysql.

Q. If the statement("delete/insert" ) is a frequent table, should be run periodically to "optimize table"?
Because "randomI/O" is bad (not table size). Is it right?
Q. Is there a way to query the table fragmentation like "innodb_scan_pages_contiguous, Innodb_scan_pages_jumpy".


Got error 5 during commit, wsrep_max_ws_size limit

Lastest Forum Posts - March 19, 2015 - 12:12pm
I'm running percona xtradb cluster 5.6 (5.6.22-72.0)

So I'm having problem importing a mysqldump of an innodb database that's rather large. The error I get from the client is "Got error 5 during commit"

Some google-fu says this nondescript error is usually from an insert going over the wsrep_max_ws_size.

I've tried adjusting this value higher, but the limit seems to be at 4GB (4294901759 bytes). My insert for one table is quite a bit larger than this. Is this limit my actual problem, and if so... why can't I increase beyond this 4GB limit?

xtradb galera cluster direct ethernet connection

Lastest Forum Posts - March 19, 2015 - 10:25am
Hi

We are planning to setup percona/galera cluster for master-master sync replication between 2 servers.

Is it possible to use direct Ethernet connection over gigabit ethernet port on the 2 servers?

Is there any settings in the cluster that specifies the ip-address? or will the galera cluster use the default port (which might be connected to overloaded switch)?

Thanks

Percona MySQL Server source code

Lastest Forum Posts - March 19, 2015 - 3:26am
Hi All,

As I know, Percona 5.6 is optimized and based on Oracle's MySQL 5.6 community edition.

Will future Percona server be optimized and based on MariaDB source please?

Thanks

Choosing a good sharding key in MongoDB (and MySQL)

Latest MySQL Performance Blog posts - March 19, 2015 - 12:00am

MongoDB 3.0 was recently released. Instead of focusing on what’s new – that is so easy to find, let’s rather talk about something that has not changed a lot since the early MongoDB days. This topic is sharding and most specifically: how to choose a good sharding key. Note that most of the discussion will also apply to MySQL, so if you are more interested in sharding than in MongoDB, it could still be worth reading.

When do you want to shard?

In general sharding is recommended with MongoDB as soon as any of these conditions is met:

  • #1: A single server can no longer handle the write workload.
  • #2: The working set no longer fits in memory.
  • #3: The dataset is too large to easily fit in a single server.

Note that #1 and #2 are by far the most common reason why people need sharding. Also note that in the MySQL world, #2 does not imply that you need sharding.

What are the properties of a good sharding key?

The starting point is that a cross-shard query is very expensive in a sharded environment. It is easy to understand why: the query has to be executed independently on several shards, and then results have to be merged.

With MongoDB, mongos will transparently route queries to the right shards and will automatically merge the results: this is very handy but the hidden complexity can also make you forget that you have executed a very poorly optimized query.

This is where the choice of a sharding key is so critical: choose the right key and most queries will be simple and efficient, choose a wrong one and you’ll have ugly and slow queries.

Actually a good sharding does not need to have tens of properties, but only two:

  • Insertions should be as much balanced as possible across all shards.
  • Each query should be able to be executed by retrieving data from as little shards as possible (ideally 1 shard).

Sounds quite easy, right? However depending on your use case, it may be quite difficult to find a good sharding key. Let’s look at a few examples.

Social Network

Say we have users who can be connected to other users, who can read or write posts and who have their own wall.

All 3 collections can become very large, so sharding all will be necessary over time.

For the user and wall collection the user_id field is an obvious choice and it actually meets both criteria for a good sharding key.

For the post collection, user_id also looks like an obvious choice, but if you think about how the collection is accessed for reads, you will realize that you will need to fetch it using its post_id, not its user_id (simply because a user can have multiple posts). If you shard by user_id, any read to a single post will be broadcast to all shards: this is clearly not a good option.

So using post_id is a better choice. However it only meets criteria #2: most posts are never updated, so all the writes are insertions that will go to a single shard. However the traffic on the post collection is strongly in favor of reads, so being able to speed up reads while not slowing down writes is probably an acceptable tradeoff.

Access Logs

The workload here is very specific: write-intensive and append-only.

Sharding by ObjectId is definitely a bad idea: while data can be easily spread across all shards, all writes will only go to one shard, so you will have no benefit compared to a non-sharded setup when it comes to scale the writes.

A better solution is to use a hash of the ObjectId: that way data AND writes will be spread across all shards.

Another good option would be to use another field in your documents that you know is evenly distributed across the whole dataset. Such field may not exist though, that’s why hashing the ObjectId is a more generic solution.

Ecommerce

MongoDB can be a good option to store a product catalog: being schemaless, it can easily store products with very different attributes.

To be usable such a catalog must be searchable. This means that many indexes need to be added, and the working set will probably grow very quickly. In this case your main concern is probably not to scale the writes, but to make reads as efficient as possible.

Sharding can be an option because if done properly, each shard will act as a coarse-grained index. Now the issue is to find which field(s) will evenly distribute the dataset. Most likely a single field will not be enough, you will have to use a compound sharding key.

Here I would say that there is no generic solution, but if the products are for instance targeted at either kid, woman or man and if you have several categories of products, a potential sharding key would be (target, category, sku).

Note that in this case, reading from secondaries may be enough to ensure good scalability.

Conclusion

As you can see, choosing a correct sharding key is not always easy: do not assume that because some apps are sharded by some fields, you should do the same. You may need a totally different strategy or sharding may even not be the right solution for your issues.

If you are interested in learning more about MongoDB, I will be presenting a free webinar on March 25 at 10 a.m. Pacific time. It will be an introduction to MongoDB for MySQL DBAs and developers. Register here if you are interested.

The post Choosing a good sharding key in MongoDB (and MySQL) appeared first on MySQL Performance Blog.

pt-table-sync password in the old format please change the password to the new format

Lastest Forum Posts - March 18, 2015 - 11:06pm
Hi All,

I'm looking at doing an upgrade from 5.5 to 5.6. I have 5.6 as a slave to 5.5 then i promote 5.6 as master later on.

I want to ensure data integrity by running pt-sync, but i'm getting this error:

DBI connect(';host=172.16.1.154;mysql_read_default_gro up=client','myadmin',...) failed: Server is running in --secure-auth mode, but 'myadmin'@'172.16.1.154' has a password in the old format; please change the password to the new format at /usr/bin/pt-table-sync line 2220

But on master that user is already using new format:

select User,Host,Password from user where user='myadmin' and Host='172.16.1.154';
+------------+------------+-------------------------------------------+
| User | Host | Password |
+------------+------------+-------------------------------------------+
| myadmin | 172.16.1.154 | *F1234567893F0BBAFABBA0A6EE6AE9987654321A |
+------------+------------+-------------------------------------------+
1 row in set (0.00 sec)

May i know if anyone has encountered and have fixed the said issue. Thank you in advanced.

Nhadie

Baracuda innoDB file format question

Lastest Forum Posts - March 18, 2015 - 11:22am
When innodb_file_format is set to Baracuda, what change is actually happening? From what I read, if innodb_file_format is set to Baracuda, and new tables are created with the ROW_FORMAT "Dynamic" or "Compressed" they will use the Baracuda file format. If the ROW_FORMAT is set to "Compact" or "Redundant", then the Antelope file format is used. When innodb_file_format is set to Baracuda, does that mean that the Baracuda file format is loaded in addition to Antelope so all 4 ROW_FORMATS can be supported? Or does Baracuda also support the older Antelope ROW_FORMATS? For new MySQL servers we build, I would like to set the innodb_file_format to Baracuda, but I want to make sure I understand how the older ROW_FORMATS are supported.

UPDATE: sorry, I just noticed that I posted this in the Cloud forum. I meant to post in the "MySQL and Percona Server" forum.

Thanks,
Mike

Baracuda innoDB file format question

Lastest Forum Posts - March 18, 2015 - 11:22am
When innodb_file_format is set to Baracuda, what change is actually happening? From what I read, if innodb_file_format is set to Baracuda, and new tables are created with the ROW_FORMAT "Dynamic" or "Compressed" they will use the Baracuda file format. If the ROW_FORMAT is set to "Compact" or "Redundant", then the Antelope file format is used. When innodb_file_format is set to Baracuda, does that mean that the Baracuda file format is loaded in addition to Antelope so all 4 ROW_FORMATS can be supported? Or does Baracuda also support the older Antelope ROW_FORMATS? For new MySQL servers we build, I would like to set the innodb_file_format to Baracuda, but I want to make sure I understand how the older ROW_FORMATS are supported.

UPDATE: sorry, I just noticed that I posted this in the Cloud forum. I meant to post in the "MySQL and Percona Server" forum.

Thanks,
Mike

Getting started guide for OpenStack contributors

Latest MySQL Performance Blog posts - March 18, 2015 - 6:00am

So you want to contribute to OpenStack? I can help!

For the last year or so I have been involved with OpenStack and more specifically the Trove (DBaaS) project as sort of an ambassador for Percona, contributing bits of knowledge, help and debugging wherever I could and thought I would share some of my experience with others that wanted to get involved with OpenStack development, documentation, testing, etc. Getting started with OpenStack contributions is also the idea behind my talk next month at Percona OpenStack Live 2015. (Percona Live attendees have access to OpenStack Live)

Back at the last OpenStack Conference and Design Summit in Paris last November, I had the amazing opportunity to attend the two-day OpenStack Upstream Training hosted by Stefano Maffulli, Loic Dachary and several other very kind and generous folks. If you ever find yourself in a position to attend one of these training sessions, I strongly suggest that you take advantage of the opportunity, you will not be disappointed.

Using some of the material from the OpenStack Foundation and a little personal experience, I’m going to go through some of the basics of what you’ll need to know if you want to contribute. There are several steps but they are mostly painless:

– It all starts with a little bit of legal work such as signing a either an individual or corporate contributor agreement.

– You will need to decide on a project or projects that you want to contribute to. Chances are that you already have one in mind.

– Find the various places where other contributors to that project hang out, usually there is a mailing list and IRC channel. Logon, introduce yourself, make some friends and sit and listen to what they are working on. Find the PTL (Project Team Lead) and remember his/her name. Let him/her know who you are, who you work for, what you are interested in, etc. Sit in on their meetings, ask questions but don’t be a pest. Observe a little etiquette, be polite and humble and you will reap many rewards later on.

– Eventually you will need to find and get the code and install whatever tools are necessary for that project, build it, stand up a test/working environment, play with it and understand what the various moving parts are. Ask more questions, etc.

– Do you think you are ready to do some coding and submit a patch? Talk to the PTL and get a lightweight bug or maybe a documentation task to work on.

– In order to submit a patch you will need to understand the workflow use the OpenStack gerrit review system which takes a little bit of time to understand if you have never used gerrit before. You’ll need to find and install git-review. Here is where making friends above really helps out. In every project there are usually going to be a few folks around with the time and patience to help you work through your first review.

– Find a bit of a mentor to help you with the mechanics in case you run into trouble, could just be the PTL if he/she has the time, make your patch, send it in and work through the review process.

– As with most peer review situations, you’ll need to remember never to take things personally. A negative review comment is not an insult to you and your family! Eventually your patch will either be accepted and merged upstream (yay!) or rejected and possibly abandoned in favor of some alternative (boo!). If rejected, fret not! Talk to the PTL and your new friends to try and understand the reason why if the review comments were unclear and simply try again.

It is that easy!

Come join me on Tuesday, April 14th in Santa Clara, California and we’ll chat about how you can begin contributing to OpenStack.

The post Getting started guide for OpenStack contributors appeared first on MySQL Performance Blog.

MySQL QA Episode 1: Bash/GNU Tools & Linux Upskill & Scripting Fun

Latest MySQL Performance Blog posts - March 17, 2015 - 9:12am

MySQL QA Episode #1: Bash/GNU Tools & Linux Upskill & Scripting Fun

This episode consists of 13 parts, and an introduction. See videos below

In HD quality (set your player to 720p!)

Introduction

Part 1: echo, ls, cp, rm, vi, cat, df, du, tee, cd, clear, uname, date, time, cat, mkdir

Part 2: find, wc, sort, shuf, tr, mkdir, man, more

Part 3: Redirection, tee, stdout, stderr, /dev/null, cat

Part 4: Vars, ‘ vs “, $0, $$, $!, screen, chmod, chown, export, set, whoami, sleep, kill, sh, grep, sudo, su, pwd

Part 5: grep, regex (regular expressions), tr

Part 6: sed, regex (regular expressions)

Part 7: awk

Part 8: xargs

Part 9: subshells, shells, sh

Part 10: if, for, while, seq, head, grep & grep -q, sleep, tee, read & more

Part 11: Arrays, lynx, grep, egrep, awk, redirection, variable, printf, while, wget, read

Part 12: Production scripting examples

Part 13: Gnuwin32, Gnuwin32 escaping & path name/binary selection gotcha’s, untar, unzip, gzip for Windows

If you enjoyed these video’s leave us a comment below!

The post MySQL QA Episode 1: Bash/GNU Tools & Linux Upskill & Scripting Fun appeared first on MySQL Performance Blog.

Free MySQL QA & Bash/Linux Training Series

Latest MySQL Performance Blog posts - March 17, 2015 - 6:00am

Welcome to the MySQL QA Training Series!

If you have not read our introductory blog post on pquery yet, I’d recommend reading that one first to get a bit of background. The community is enthuastic about pquery, and today I am happy to announce a full training series on pquery and more. Whether you are a Linux or MySQL newbie or a seasoned QA engineer, there is something here for you. From Bash scripting (see episode 1 below), to every aspect of the new pquery framework, it is my hope that you enjoy this series. If you do, please leave us a comment

Database quality assurance is not as straightforward as it may seem. It’s not a matter of point-and-click, but rather of many intertwined tools and scripts. Beyond that, due to the complexity of the underlying product, it’s about having an overall plan or vision on how to adequately test the product in every aspect.

Take for example the SELECT statement; it allows specifying about 30 different clauses or modifiers (GROUP BY, WHERE, ORDER, LIMIT, HAVING, …). Then, think further about what one could do inside these clauses, or inside subselects etc. The number of possible combinations (exhaustive testing) of all commands (and all formats and variations thereof) plus all mysqld options (nearly 500 of them) is for all intents and purposes infinite, and thus seemingly impossible to test.

In Episode 13, an approach is proposed which, in our view, adequately solves this test infinite-possibility coverage problem through the use of random spread coverage testing and sql interleaving.Knowing your Bash/Linux/Gnu scripting well is also an almost definite prerequisite to getting started with mysqld QA. Episode 1 in this series is over 3.5 hours of in-depth (from easy to advanced) training on many often-used Bash commands and topics. From ls to sed, from cp to xargs and from variables to arrays. Enjoy!

Without further ado, here are the planned upcoming episodes:

MySQL QA Episode 1: Bash/GNU Tools & Linux Upskill & Scripting Fun
MySQL QA Episode 2: Build a MySQL Server: Git, Compiling, Build Tools
MySQL QA Episode 3: Debugging: GDB, Backtraces, Frames, Library Dependencies
MySQL QA Episode 4: QA Framework Setup Time! percona-qa, pquery, reducer & more
MySQL QA Episode 5: Preparing Your QA Run: mtr_to_sql.sh and pquery-run.sh
MySQL QA Episode 6: Analyzing & Filtering: pquery-prep-red.sh, -clean-known.sh & pquery-results.sh
MySQL QA Episode 7: Reducing Testcases for Beginners: single-threaded reducer.sh
MySQL QA Episode 8: Reducing Testcases for Engineers: tuning reducer.sh
MySQL QA Episode 9: Reducing Testcases for Experts: multi-threaded reducer.sh
MySQL QA Episode 10: Reproducing and Simplifying: How to get it Right
MySQL QA Episode 11: Valgrind Testing: Pro’s, Con’s, Why and How
MySQL QA Episode 12: Multi-node Cluster Testing Using Docker
MySQL QA Episode 13: A Better Approach to all MySQL Regression, Stress & Feature Testing: Random Coverage Testing & SQL Interleaving

A short introduction on each episode:

As episodes are finished, the series titles above will be linked so it’s easy to check this page for updates.

Enjoy!

The post Free MySQL QA & Bash/Linux Training Series appeared first on MySQL Performance Blog.

Percona 5.6.21 crashing frequently

Lastest Forum Posts - March 16, 2015 - 1:56pm
Repeatedly on my slave server, I am getting assertion failures after 7-8 hrs for semaphore wait and has lasted for more than 600 seconds.
General load on server - jobs run on this server creating temporary tables
MySQL Version - 5.6.21-70.1
OS - Linux

----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
2015-03-16 08:24:21 7fd529214700 InnoDB: Assertion failure in thread 140553494808320 in file srv0srv.cc line 2137
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/...-recovery.html
InnoDB: about forcing recovery.
15:24:21 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=8388608
read_buffer_size=16777216
max_used_connections=60
max_threads=602
thread_count=31
connection_count=29
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 19743388 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bdcec]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x6512a1]
/lib64/libpthread.so.0[0x3bdc60f710]
/lib64/libc.so.6(gsignal+0x35)[0x3bdbe32625]
/lib64/libc.so.6(abort+0x175)[0x3bdbe33e05]
/usr/sbin/mysqld[0x999852]
/lib64/libpthread.so.0[0x3bdc6079d1]
/lib64/libc.so.6(clone+0x6d)[0x3bdbee88fd]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
150316 08:30:00 mysqld_safe Number of processes running now: 0
150316 08:30:00 mysqld_safe mysqld restarted
2015-03-16 08:30:00 0 [Warning] Using unique option prefix character_set_client instead of character-set-client-handshake is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-16 08:30:00 0 [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client-handshake' due to invalid value 'utf8'
2015-03-16 08:30:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-16 08:30:00 31543 [Note] Plugin 'FEDERATED' is disabled.
2015-03-16 08:30:00 7f0306f237e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-03-16 08:30:00 31543 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-16 08:30:00 31543 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-16 08:30:00 31543 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-16 08:30:00 31543 [Note] InnoDB: Memory barrier is not used
2015-03-16 08:30:00 31543 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-16 08:30:00 31543 [Note] InnoDB: Using Linux native AIO
2015-03-16 08:30:00 31543 [Note] InnoDB: Using CPU crc32 instructions
2015-03-16 08:30:00 31543 [Note] InnoDB: Initializing buffer pool, size = 96.0G
2015-03-16 08:30:06 31543 [Note] InnoDB: Completed initialization of buffer pool
2015-03-16 08:30:13 31543 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-16 08:30:13 31543 [Note] InnoDB: Log scan progressed past the checkpoint lsn 27626464771723
2015-03-16 08:30:13 31543 [Note] InnoDB: Database was not shutdown normally!
2015-03-16 08:30:13 31543 [Note] InnoDB: Starting crash recovery.
2015-03-16 08:30:13 31543 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-03-16 08:30:18 31543 [Note] InnoDB: Restoring possible half-written data pages
2015-03-16 08:30:18 31543 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 27626470014464
InnoDB: Doing recovery: scanned up to log sequence number 27626475257344
InnoDB: Doing recovery: scanned up to log sequence number 27626480500224
InnoDB: Doing recovery: scanned up to log sequence number 27626485743104
InnoDB: Doing recovery: scanned up to log sequence number 27626490985984
InnoDB: Doing recovery: scanned up to log sequence number 27626496228864
InnoDB: Doing recovery: scanned up to log sequence number 27626501471744
InnoDB: Doing recovery: scanned up to log sequence number 27626506714624
InnoDB: Doing recovery: scanned up to log sequence number 27626511957504
InnoDB: Doing recovery: scanned up to log sequence number 27626517200384
InnoDB: Doing recovery: scanned up to log sequence number 27626522443264
InnoDB: Doing recovery: scanned up to log sequence number 27626527686144
InnoDB: Doing recovery: scanned up to log sequence number 27626532929024
InnoDB: Doing recovery: scanned up to log sequence number 27626538171904
InnoDB: Doing recovery: scanned up to log sequence number 27626543414784
InnoDB: Doing recovery: scanned up to log sequence number 27626548657664
InnoDB: Doing recovery: scanned up to log sequence number 27626553900544
InnoDB: Doing recovery: scanned up to log sequence number 27626559143424
InnoDB: Doing recovery: scanned up to log sequence number 27626564386304
InnoDB: Doing recovery: scanned up to log sequence number 27626569629184
InnoDB: Doing recovery: scanned up to log sequence number 27626571649635
InnoDB: 2 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 7 row operations to undo
InnoDB: Trx id counter is 14572560384
2015-03-16 08:31:37 31543 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed

Vagrant+Vbox, first steps to XtraDB. Bootstrap first node shows an error

Lastest Forum Posts - March 16, 2015 - 4:51am
Hi

I've deployed a small environment of test with Vagrant and Vbox with 3 nodes (ubuntu TLS)

IPs are private network, 1 per node

192.168.33.50
192.168.33.51
192.168.33.52

1 cpu and 1 GB RAM per node also
No firewall inside Ubuntu
each node can ping another and all knows the hostname of the others

I've read some guides about Xtradb Cluster, finally I choose:

http://redcrackle.com/blog/how-set-p...cluster-ubuntu

but when I wan to restart the first node with bootsrap it finish with an error:

2015-03-16 12:13:59 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-16 12:13:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-16 12:13:59 10647 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-03-16 12:13:59 10647 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/libgalera_smm.so'
2015-03-16 12:13:59 10647 [Note] WSREP: wsrep_load(): Galera 3.9(r93aca2d) by Codership Oy <info@codership.com> loaded successfully.
2015-03-16 12:13:59 10647 [Note] WSREP: CRC-32C: using "slicing-by-8" algorithm.
2015-03-16 12:13:59 10647 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
2015-03-16 12:13:59 10647 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.33.50; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false
2015-03-16 12:13:59 10647 [Note] WSREP: Service thread queue flushed.
2015-03-16 12:13:59 10647 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
2015-03-16 12:13:59 10647 [Note] WSREP: wsrep_sst_grab()
2015-03-16 12:13:59 10647 [Note] WSREP: Start replication
2015-03-16 12:13:59 10647 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
2015-03-16 12:13:59 10647 [Note] WSREP: protonet asio version 0
2015-03-16 12:13:59 10647 [Note] WSREP: Using CRC-32C for message checksums.
2015-03-16 12:13:59 10647 [Note] WSREP: backend: asio
2015-03-16 12:13:59 10647 [Note] WSREP: restore pc from disk successfully
2015-03-16 12:13:59 10647 [Note] WSREP: GMCast version 0
2015-03-16 12:13:59 10647 [Note] WSREP: (673c2adf, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2015-03-16 12:13:59 10647 [Note] WSREP: (673c2adf, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2015-03-16 12:13:59 10647 [Note] WSREP: EVS version 0
2015-03-16 12:13:59 10647 [Note] WSREP: gcomm: bootstrapping new group 'PXC'
2015-03-16 12:13:59 10647 [Note] WSREP: start_prim is enabled, turn off pc_recovery
2015-03-16 12:13:59 10647 [Note] WSREP: Node 673c2adf state prim
2015-03-16 12:13:59 10647 [Note] WSREP: view(view_id(PRIM,673c2adf,3) memb {
673c2adf,0
} joined {
} left {
} partitioned {
})
2015-03-16 12:13:59 10647 [Note] WSREP: save pc into disk
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.33.50:4567
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr proto entry 0x20e1d60
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.33.51:4567
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr proto entry 0x20ea4f0
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.33.52:4567
2015-03-16 12:13:59 10647 [Note] WSREP: discarding pending addr proto entry 0x20f2ce0
2015-03-16 12:13:59 10647 [Note] WSREP: clear restored view
2015-03-16 12:13:59 10647 [ERROR] WSREP: failed to open gcomm backend connection: 11: : 11 (Resource temporarily unavailable)
at gcs/src/gcs_gcomm.cpp:connect():253
2015-03-16 12:13:59 10647 [ERROR] WSREP: gcs/src/gcs_core.cpp:long int gcs_core_open(gcs_core_t*, const char*, const char*, bool)():206: Failed to open backend connection: -11 (Resource temporarily unavailable)
2015-03-16 12:13:59 10647 [ERROR] WSREP: gcs/src/gcs.cpp:long int gcs_open(gcs_conn_t*, const char*, const char*, bool)():1379: Failed to open channel 'PXC' at 'gcomm://192.168.33.50,192.168.33.51,192.168.33.52': -11 (Resource temporarily unavailable)
2015-03-16 12:13:59 10647 [ERROR] WSREP: gcs connect failed: Resource temporarily unavailable
2015-03-16 12:13:59 10647 [ERROR] WSREP: wsrep::connect() failed: 7
2015-03-16 12:13:59 10647 [ERROR] Aborting

2015-03-16 12:13:59 10647 [Note] WSREP: Service disconnected.
2015-03-16 12:14:00 10647 [Note] WSREP: Some threads may fail to exit.
2015-03-16 12:14:00 10647 [Note] Binlog end
2015-03-16 12:14:00 10647 [Note] /usr/sbin/mysqld: Shutdown complete

150316 12:14:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended


It's very hard for me to debug what is happening... any ideas?

Thanks!

Deep dive into MySQL’s innochecksum tool

Latest MySQL Performance Blog posts - March 16, 2015 - 12:00am

One of our Percona Support customers recently reported that Percona XtraBackup failed with a page corruption error on an InnoDB table. The customer thought it was a problem or bug in the Percona XtraBackup tool. After investigation we found that an InnoDB page was actually corrupted and a Percona XtraBackup tool caught the error as expected and hence the backup job failed.

I thought this would be an interesting topic and worthy of a blog post. In this article I will describe the innochecksum tool, when and how to use it and what are the possible fixes if an InnoDB table suffers from page corruption.

The innochecksum tool is an offline tool that prints checksums for InnoDB files. This tool reads an InnoDB tablespace file, calculates the checksum for each page and reports mismatches, if any. A checksum mismatch is an indication of corrupt pages. Being as offline tool, innochecksum can’t be used on tablespace file that a MySQL server is currently using,  hence you need to shutdown the server prior to running the innochecksum tool. If you try to run the innochecksum tool on a running MySQL server, then there is a chance that innochecksum crashes or reports a bad checksum for a good page, resulting false positives results. There is chance when you run innochecksum on a tablespace file that is opened by mysqld, that pages are dirty and not checksummed yet by the InnoDB storage engine itself.

The point: don’t run innochecksum against a running server.

InnoDB corruption can be caused by many factors (e.g. power lost, faulty hardware, bugs).  The InnoDB storage engine validates calculated checksum while reading pages from a tablespace on disk to the stored checksum in the page. In case, InnoDB finds page checksum mismatch it will force down the MySQL server.

Let me show you a page corruption error identified by Percona XtraBackup during a backup run in which the backup failed afterward.

[01] xtrabackup: Database page corruption detected at page 25413, retrying... [01] xtrabackup: Database page corruption detected at page 25413, retrying... [01] xtrabackup: Database page corruption detected at page 25413, retrying...

First, we need to identify if the tablespace is really corrupted for that particular table. I do that with the help of the innochecksum utility as shown below. As I mentioned earlier, make sure to shut down MySQL before using the innochecksum tool.

$ innochecksum -p 25413 /path/to/datadir/database_name/table_name.ibd

I passed the -p (page) flag for innochecksum to only check the specific pages that were reported corrupt by Percona XtraBackup. Without passing any option to the innochecksum tool, it will check entire tablespace for corruption which will required additional server downtime. The innochecksum tool also supports the -d (debug) option to print the checksum for each page and the -v (verbose) parameter to print a progress indicator. You can find more details in the manual. If the tool reports page corruption then database table is really corrupted as below.

page 25413 invalid (fails log sequence number check)

In order to fix this issue, the first thing you should try is to mysqldump the corrupted table and If mysqldump succeeded then problem exists in secondary indexes for that tablespace. This is because the mysqldump utility doesn’t touch indexes as indexes are created after all rows are inserted.

If mysqldump succeeds then the problem is associated with indexes. I would suggest following options to fix the corruption.

— Execute OPTIMIZE TABLE on that table which rebuilds indexes. The table will be locked during the operation prior to MySQL 5.6.17. Since MySQL 5.6.17 OPTIMIZE TABLE is an online operation.
— Rebuild table with the pt-online-schema-change tool from Percona Toolkit. This will give the same result as OPTIMIZE TABLE a non-blocking way as the pt-online-schema=change tool is online schema change tool.
— Drop all secondary indexes and then recreate them. The table will be locked during that operation for writes only. Again, you can use pt-online-schema-change tool for this purpose without sacrificing read/writes ability on the table during the drop and create indexes operation.

Finally, I would suggest to re-run the innochecksum tool to verify the tables integrity again as this will make sure there is no more page corruption. In this case we found that the table was actually corrupted and fixing table corruption through the backup/reload table fixed the problem and Percona XtraBackup ran fine during the next run.

It is possible that mysqldump crashes a MySQL server for a corrupted table. Fortunately, Percona Server contains innodb_corrupt_table_action which you can enable. The configuration variable is dynamic in nature, this means enabling it doesn’t requires a MySQL server restart. Prior to Percona Server 5.6 innodb_corrupt_table_action was known as innodb_pass_corrupt_table. Once you enable this option, you can try mysqldump again. If you are using Oracle MySQL then I would suggest to try this with innodb_force_recovery in case mysqldump fails to dump the table contents.

As a side note, if your backup is successful without any errors while performing a backup with Percona Xtrabackup, this means your InnoDB tables don’t have any page checksum mismatch or corruption. Percona XtraBackup can validate page checksums and in case of errors it  logs error and exists as I mentioned above.

There is also a modified version of the innochecksum made available by Facebook’s Mark Callaghan and can be found in this bug report which provides extra stats on tablespace undo blocks. There is another tool made by Google’s Jeremy Cole known as the InnoDB Ruby Tool to examine the internals of InnoDB.

LIMITATIONS:

  • Innochecksum is an offline InnoDB checksum tool. This means you must stop MySQL server. Otherwise it produces “Unable to lock file” error since MySQL 5.7.
  • Old versions of innochecksum only supports files up to 2GB in size. However, since MySQL 5.6 innochecksum supports files greater than 2GB in size.
  • Percona Server variable innodb_corrupt_table_action is supported on tables existing in their tablespace (i.e. innodb_file_per_table).
  • If you are using compressed tables (ROW_FORMAT=COMPRESSED) , then you must use innochecksum from MySQL 5.7.2 or greater, as earlier versions of innochecksum don’t support compressed tables. Check this bug for details.

New Features for the innochecksum tool from MySQL 5.7:

  • As I mentioned above, since MySQL 5.7 innochecksum supports file sizes greater than 2GB.
  • Since MySQL 5.7 you can log the output with the –log option.
  • –page-type-summary option added for page type summaries.
  • MySQL 5.7 also includes another nice option –page-type-dump which dumps the details of each page to standard output (STDOUT) or standard error (STDERR).
  • Since MySQL 5.7 innochecksum can be executed on multiple user-defined system tablespace files.
  • Since MySQL 5.7 innochecksum can be executed on multiple system tablespace files.

You can read more about this is in the MySQL 5.7 manual page of innochecksum.

Conclusion:
In this post, we identified InnoDB page corruption using the logs generated by Percona XtraBackup and fixed  them by using the mysqldump tool. But again, unfortunately, there are chances that Percona XtraBackup will not always fail in the same way when it finds corruption. So in some cases, it’s not easy to tell whether Percona XtraBackup has failed due to a bad checksum or a bug of its own. But in most cases, page corruption is the culprit if Percona XtraBackup fails to complete.

To summarize, I would say that Percona XtraBackup is a good way of verifying whether or not InnoDB pages are corrupted – and you can also verify the same thing via the mysqldump utility.

The post Deep dive into MySQL’s innochecksum tool appeared first on MySQL Performance Blog.

Pages

Subscribe to Percona aggregator
]]>