]]>
]]>

You are here

Feed aggregator

Explanation of the entries in grastate.dat file

Lastest Forum Posts - February 27, 2015 - 12:49am
Hello.

I'm trying to understand the entries in grastate.dat file, particularly seqno value. If I understand correctly, the 'seqno' value increases as node gets written to. If all nodes are shut down I can use 'seqno' to figure out which node was last written to - it is the one which has the highest 'seqno' value.

Now, am I correct in assuming that in the working (not degraded) cluster all the seqno values should be the same? That is, if I stop the writes completely, then shut down all the units, the seqno values across the cluster should be the same? (Which, in turn, means that I can safely boostrap from any pxc node?)

What also confuses me are values of '0' and '-1' for the seqno. I'm imagining that -1 corresponds to an error of some kind. But, when I bootstrap off the note which has '-1' as a seqno, and then shut it down, the seqno won't change - how am I to determine, then, if the node has failed?
What does seqno value of '0' represents? I was under impression that '0' means that there were no writes to the cluster since the bootstrap, but that seems to be in contradiction with '-1' described above.

Lastly, when is grastate.dat file created, when it gets written to, and is it ever removed by the pxc?
Mario

3 handy tools to remove problematic MySQL processes

Latest MySQL Performance Blog posts - February 27, 2015 - 12:00am

DBAs often encounter situations where they need to kill queries to ensure there are no long-running queries on a MySQL server that would impact performance. Long-running queries can be the result of many factors. Fortunately, Percona Server contains some handy tools to remove problematic MySQL processes. I will highlight all of the tools via some examples in this post.

pt-kill:
There have been some good posts on this blog about the pt-kill tool, like this one by Arunjith Aravindan titled “How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill.” Let’s dive into pt-kill a bit further with a few more examples. What does pt-kill do? It kills MySQL connections. Say you wanted to run pt-kill from a cronjob and then get an email on every killed process/query. Here is typical example for that.

$ pt-kill --interval 1 --run-time 1 --busy-time 5 --log /path/to/kill_long_running_thread.log --match-info "^(select|SELECT|Select)" --kill --print --user=xxxxxx --password=xxxxxxxxxx

Assume this is running from a cronjob, When pt-kill executes, it will kill queries longer than 5 seconds. By default, pt-kill runs forever –run-time option tells how long pt-kill to run before exiting If –interval and –busy-time parameters are used together then the explicit –interval value is used. Otherwise the default interval is 30 seconds. Note: this will only kill all read queries as per the –match-info parameter.

The above command will log all killed queries in the file referenced with the –log option. If you need to be notified via email for every killed query, the command below will do it. Off-course, you need to have the system configured to send e-mail.

#!/bin/bash tail -n 0 -F /path/to/kill_long_running_thread.log | while read LOG do echo "$LOG" | mail -s "pt-kill alert" sample@test.com done

You can execute this shell script in the background within screen or with the nohup utility continuously to monitor the log file. It will send an email whenever any new killed query arrives to the referenced log file in the pt-kill command. Unfortunately, there is no option to notify-by-email in pt-kill at the moment, so this is sort of a workaround.

In order to log all killed queries into a database table you will need to use the –log-dsn option as per the example below.

$ pt-kill --interval 1 --busy-time 1 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --match-info "^(select|SELECT|Select)" --kill

All killed queries will be logged into percona.kill_log table. The –daemonize option will run this command in the background forever and will kill all SELECT queries running longer than 1 second (–busy-time 1). The –interval option instructs pt-kill to scan processes every 1 second (–interval 1).

mysql> select * from kill_log; +---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+ | kill_id | server_id | timestamp | reason | kill_error | Id | User | Host | db | Command | Time | State | Info | Time_ms | +---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+ | 17 | 1 | 2015-01-10 08:38:33 | Query matches Info spec | | 35146 | root | localhost | NULL | Query | 0 | User sleep | SELECT SLEEP(5) | NULL | | 20 | 1 | 2015-01-10 08:38:34 | Query matches Info spec | | 35223 | root | localhost | NULL | Query | 0 | User sleep | SELECT SLEEP(5) | NULL | +---------+-----------+---------------------+-------------------------+------------+-------+------+-----------+------+---------+------+------------+-----------------+---------+

With the help of logging killed queries into a database tables. You can easily get all the trends/and /statistics on killed queries via SQL.

By default the tool kills the oldest of the queries that would have been running for more than a given –busy-time.  If you need to kill all the threads that have been busy for more than a specified –busy-time, then this will do it:

$ pt-kill --victims=all --busy-time=60

Statement Timeout in Percona Server:
The max-statement-time feature is ported from the Twitter patches. This feature can be used to limit the query execution time by specifying the timeout value in the max_statement_time variable. When the specified number of milliseconds is reached the server aborts the statement and returns the error below to the client.

ERROR 1877 (70101): Query execution was interrupted, max_statement_time exceeded

Let me demonstrate this through another example:

mysql [localhost] {msandbox} (world) > SET max_statement_time=1; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (world) > show variables like 'max_statement_time'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_statement_time | 1 | +--------------------+-------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (world) > SELECT * FROM City WHERE District = 'abcd'; ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded mysql [localhost] {msandbox} (world) > UPDATE City SET District='abcd' WHERE ID = 2001; ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded mysql [localhost] {msandbox} (world) > ALTER TABLE City ADD INDEX district_idx (district); ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded

As you can see from this example statement, the timeout feature works for all statements including SELECT/DML/DDL queries.

mysql [localhost] {msandbox} (world) > show status like 'Max_statement%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Max_statement_time_exceeded | 3 | | Max_statement_time_set | 19 | | Max_statement_time_set_failed | 0 | +-------------------------------+-------+ 3 rows in set (0.00 sec)

The above mentioned status variables are stats for a statement timeout feature. Max_statement_time_exceeded will inform you that the total number of statements exceeded the defined timeout. Max_statement_time_set defines the number of statements for which execution time limit was set. You can find more details in this documentation. The statement timeout feature was introduced in Percona Server 5.6. You can check if your specific version of Percona Server supports this feature or not via the have_statement_timeout variable.

mysql [localhost] {msandbox} (world) > show global variables like 'have_statement_timeout'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_statement_timeout | YES | +------------------------+-------+ 1 row in set (0.00 sec)

Bugs you should be aware of:

https://bugs.launchpad.net/percona-server/+bug/1388533 -> This affects how the feature interacts with stored procedures. If you use stored procedures, max_statement_time might not behave as you expect.
https://bugs.launchpad.net/percona-server/+bug/1307432 -> This is documentation bug. Percona Server timeouts might not be safe for some statements like DDL and should not be used with such queries, The documentation does not reflect this. You should be very careful if you set a global statement timeout, It affects data changing queries as well. For best results set the max_statement_time variable in a session before running queries that you want to be killed if they execute too long, instead of using a global variable.
https://bugs.launchpad.net/percona-server/+bug/1376934 -> This affects the statement timeout feature on the query level. You must set max_statement_time in a session or globally instead however, this bug is fixed in latest version i.e. Percona Server 5.6.22-72.0

InnoDB Kill Idle Transactions:
This feature was introduced in Percona Server 5.5. It limits the age of idle XtraDB transactions and will kill idle transactions longer than a specified threshold for innodb_kill_idle_transaction. This feature is useful when autocommit is disabled on the server side and you are relying on the application to commit transactions and want to avoid long running transactions that are uncommitted. Application logic errors sometimes leaves transactions uncommitted. Let me demonstrate it quickly through one example:

mysql [localhost] {msandbox} (world) > show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ mysql [localhost] {msandbox} (world) > show global variables like 'innodb_kill_idle_transaction'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | innodb_kill_idle_transaction | 10 | +------------------------------+-------+ mysql [localhost] {msandbox} (world) > START TRANSACTION; SELECT NOW(); INSERT INTO City_backup (Name,CountryCode,District,Population) VALUES ('Karachi','PK','Sindh','1000000'); Query OK, 0 rows affected (0.00 sec) +---------------------+ | NOW() | +---------------------+ | 2015-01-31 07:11:39 | +---------------------+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (world) > SHOW ENGINE INNODB STATUSG *************************** 1. row *************************** ------------ TRANSACTIONS ------------ ---TRANSACTION 173076, ACTIVE 10 sec 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1 MySQL thread id 15, OS thread handle 0x7f862e9bf700, query id 176 localhost msandbox init SHOW ENGINE InnoDB STATUS TABLE LOCK table `world`.`City_backup` trx id 173076 lock mode IX ---------------------------- END OF INNODB MONITOR OUTPUT ============================ mysql [localhost] {msandbox} (world) > SELECT NOW(); SELECT * FROM City_backup; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 16 Current database: world +---------------------+ | NOW() | +---------------------+ | 2015-01-31 07:12:06 | +---------------------+ 1 row in set (0.01 sec) Empty set (0.00 sec)

 Conclusion:
In this post, I shared some tools that can help you get rid of long-running transactions to help ensure that you don’t run into performance issues. This is one of the many good reasons to use Percona Server, which has some extra features on top of vanilla MySQL server.

 

The post 3 handy tools to remove problematic MySQL processes appeared first on MySQL Performance Blog.

whole cluster crashed due to table not synced

Lastest Forum Posts - February 26, 2015 - 2:23am
Hi All,
We had an 7 nodes cluster which crossing three datacenter. with 2/2/3 on different datacenter.
we got problem as the whole cluster not function due to table not synced with 11 minutes delay..

the table was first created on node 1 with the time 2015-02-25 23:59:50, but it not synced to other nodes immediately, and other node failed at 0:11:00 when there is some operation on the table. this make the node 1 an standalone node.

we want to understanding why the replication not replicated, is there any monitoring metric could be alerting in such case ?
any bug for such case?

logs from other 6 nodes
-------------------------------------

150226 0:11:00 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1146, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 0 seqno: 4190686)
150226 0:11:00 [ERROR] Slave SQL: Error executing row event: 'Table 'keystone.credential' doesn't exist', Error_code: 1146
150226 0:11:00 [Warning] WSREP: RBR event 4963 Write_rows apply warning: 1146, 4190686
150226 0:11:00 [Warning] WSREP: Failed to apply app buffer: seqno: 4190686, status: 1
at galera/src/replicator_smm.cpp:apply_wscoll():57
Retrying 2th time
..........
..........
150226 0:11:04 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1146, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 0 seqno: 4190686)
150226 0:11:04 [ERROR] Slave SQL: Error executing row event: 'Table 'keystone.credential' doesn't exist', Error_code: 1146
150226 0:11:04 [Warning] WSREP: RBR event 4963 Write_rows apply warning: 1146, 4190686
150226 0:11:04 [ERROR] WSREP: Failed to apply trx: source: 8b8e06eb-594e-11e4-99d3-822b0d796705 version: 2 local: 0 state: APPLYING flags: 1 conn_id: 15113818 trx_id: 4453338 seqnos (l: 4198354, g: 4190686, s: 4190685, d: 4190660, ts: 1424934617949390287)
150226 0:11:04 [ERROR] WSREP: Failed to apply trx 4190686 10 times
150226 0:11:04 [ERROR] WSREP: Node consistency compromized, aborting...
150226 0:11:04 [Note] WSREP: Closing send monitor...
150226 0:11:04 [Note] WSREP: Closed send monitor.
150226 0:11:04 [Note] WSREP: gcomm: terminating thread
150226 0:11:04 [Note] WSREP: gcomm: joining thread
150226 0:11:04 [Note] WSREP: gcomm: closing backend
150226 0:11:04 [Note] WSREP: view(view_id(NON_PRIM,3630e80f-594f-11e4-9fd8-b2a0ad1b9db3,33) memb {
adb89b17-5a25-11e4-833d-c63263a4e8ac,
} joined {
} left {
} partitioned {
3630e80f-594f-11e4-9fd8-b2a0ad1b9db3,
8b8e06eb-594e-11e4-99d3-822b0d796705,
9fd32195-594f-11e4-937f-2b0004fbf107,
cd7de3db-594f-11e4-bc4d-c70bf0d0977b,
f19c53ac-9c38-11e4-a5cb-df74780de7f1,
f814b175-594e-11e4-b14e-becb87dc9620,
})
150226 0:11:04 [Note] WSREP: view((empty))
150226 0:11:04 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
150226 0:11:04 [Note] WSREP: gcomm: closed
150226 0:11:04 [Note] WSREP: Flow-control interval: [16, 16]
150226 0:11:04 [Note] WSREP: Received NON-PRIMARY.
150226 0:11:04 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 4190686)
150226 0:11:04 [Note] WSREP: Received self-leave message.
150226 0:11:04 [Note] WSREP: Flow-control interval: [0, 0]
150226 0:11:04 [Note] WSREP: Received SELF-LEAVE. Closing connection.
150226 0:11:04 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 4190686)
150226 0:11:04 [Note] WSREP: RECV thread exiting 0: Success
150226 0:11:04 [Note] WSREP: recv_thread() joined.
150226 0:11:04 [Note] WSREP: Closing replication queue.
150226 0:11:04 [Note] WSREP: Closing slave action queue.
150226 0:11:04 [Note] WSREP: /mysql/home/products/mysql/bin/mysqld: Terminated.

log from the node 1, which shows it can't connect to all other node after 0:11:04
-------------------------------
150226 0:11:04 [Note] WSREP: (8b8e06eb-594e-11e4-99d3-822b0d796705, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://10.126.52.43:4567
150226 0:11:05 [Note] WSREP: (8b8e06eb-594e-11e4-99d3-822b0d796705, 'tcp://0.0.0.0:4567') reconnecting to adb89b17-5a25-11e4-833d-c63263a4e8ac (tcp://10.126.52.43:4567), attempt 0
150226 0:11:05 [Note] WSREP: declaring 3630e80f-594f-11e4-9fd8-b2a0ad1b9db3 stable
150226 0:11:05 [Note] WSREP: declaring 9fd32195-594f-11e4-937f-2b0004fbf107 stable
150226 0:11:05 [Note] WSREP: declaring cd7de3db-594f-11e4-bc4d-c70bf0d0977b stable
150226 0:11:05 [Note] WSREP: declaring f19c53ac-9c38-11e4-a5cb-df74780de7f1 stable
150226 0:11:05 [Note] WSREP: declaring f814b175-594e-11e4-b14e-becb87dc9620 stable
150226 0:11:05 [Note] WSREP: Node 3630e80f-594f-11e4-9fd8-b2a0ad1b9db3 state prim
150226 0:11:05 [Note] WSREP: declaring cd7de3db-594f-11e4-bc4d-c70bf0d0977b stable
150226 0:11:05 [Note] WSREP: declaring f19c53ac-9c38-11e4-a5cb-df74780de7f1 stable
150226 0:11:05 [Note] WSREP: Node 8b8e06eb-594e-11e4-99d3-822b0d796705 state prim
150226 0:11:05 [Warning] WSREP: 8b8e06eb-594e-11e4-99d3-822b0d796705 sending install message failed: Resource temporarily unavailable

Thanks

Worrying about the ‘InnoDB: detected cycle in LRU for buffer pool (…)’ message?

Latest MySQL Performance Blog posts - February 26, 2015 - 12:00am

If you use Percona Server 5.5 and you have configured it to use multiple buffer pool instances than sooner or later you’ll see the following lines on the server’s error log and chances are you’ll be worried about them:

InnoDB: detected cycle in LRU for buffer pool 5, skipping to next buffer pool. InnoDB: detected cycle in LRU for buffer pool 3, skipping to next buffer pool. InnoDB: detected cycle in LRU for buffer pool 7, skipping to next buffer pool.

Worry not as this is mostly harmless. It’s becoming a February tradition for me (Fernando) to face a question about this subject (ok, it’s maybe a coincidence) and this time I’ve teamed up with my dear colleague and software engineer George Lorch to provide you the most complete blog post ever published on this topic(with a belated thank you! to Ernie Souhrada, with whom I’ve also discussed this same matter one year ago).

InnoDB internals: what is “LRU” ?

There’s a short and to-the-point section of the MySQL manual that explains in a clear way what is the InnoDB buffer pool, how it operates and why it plays such an important role in MySQL performance. If you’re interested in understanding InnoDB internals then that page is a good start. In this section we’ll refrain ourselves to explain what the “LRU” that shows in our subject message is so we’ll only slightly dig into InnoDB internals, enough to make for some context. Here’s a quick introduction to the buffer pool, quoting from the above manual page:

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. (…) Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.

In practice, however, we can rarely fit our whole dataset inside the InnoDB buffer pool so there must be a process to manage this limited pool of memory pages:

InnoDB manages the pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a new block to the pool, InnoDB evicts the least recently used block and adds the new block to the middle of the list.

There you go, InnoDB employs a variation of the Least Recently Used algorithm called midpoint insertion strategy to manage the pages within the buffer pool. We should mention it does makes exceptions, such as during a full table scan, when it knows the loaded pages might end up being read only a single time.

Dumping and reloading the buffer pool

Before we can get to the main point of this article lets first examine why would you want to dump the buffer pool to disk, which is at the core of the matter here: that’s when those warning messages we’re discussing may appear.

When you start a MySQL server the buffer pool is empty by default. Performance is at it’s worse at this point because no data can be found in memory so in practice each request for data results in an I/O operation to retrieve the data in the disk and bring it to memory. With time the buffer pool gets filled and performance improves – more and more data can now be found in memory. With yet more time we reach a peek performance state: the buffer pool not only is full but it is filled with the most popular data. The time between the start of the server and reaching this optimum state in the buffer pool is called server warm up. How long it takes depends mostly on two things: the size of the buffer pool and the level of activity of the server – the less busy it is the less requests it will get and thus more time is needed until the popular data is fully loaded.

Now, there could be a shortcut: what if before we save the buffer pool on a disk file before we stop MySQL? We could later use it to reload the buffer pool to an optimum state when we restart the server, thus decreasing the warm up period dramatically.

Percona was a pioneer in this field related to other MySQL distributions and implemented this functionality in Percona Server 5.5. Later on, MySQL 5.6 was released with a similar functionality which also allowed preloading the buffer pool for a faster warm up. Percona Server 5.6 incorporates this upstream feature, effectively replacing its own implementation.

“Detected cycle in LRU”

In the section above we introduced a functionality that allows to dump the contents of the buffer pool to disk so we can later reload it at server restart. What we didn’t mention was that this is yet most useful outside of maintenance time and planned shutdows – that is, when the server crashes. When a crash happens it’s that more important to bring it back to a warm up state soon, so it can resume providing data fast enough. And giving we cannot predict a crash the only way we can arrange to have the latest buffer pool on disk is by flushing it often.

While the buffer pool is divided into pages for efficiency of high-volume read operations it is implemented as a linked list of pages, for efficiency of cache management. During the process of dumping the buffer pool to disk a mutex is acquired on the LRU list. However, this mutex is not hold for the duration of the process – it is periodically released to prevent stalling of the system. The problem is: in between the release of the mutex and the moment it is acquired again the list may get reshuffled. Since the dump keeps a pointer to its position across the mutex boundry, the dump can get put into some artificial cycling.

Lets consider a linked list:

A > B > C > D > E

where each letter corresponds to a memory page. Now lets say the initial dump was partially taken and covered the first three pages, “A > B > C”, placing a pointer on “C” before releasing the mutex. Once the mutex is reacquired the list has been reshuffled:  “A > C > B > D > E”. The resulting junction of the partial list we have already copied and the reshuffled list now includes a loop, which would incur in a cycle: “(A > B > C) > B > D > E”. When the dumping process detects a cycle on the LRU list it stops copying from the actual buffer pool, throws in a warning message, and moves on to the next buffer pool instance – otherwise it would keep dumping in an infinite loop.

How harmless are those messages ?

It is fairly harmless except for the fact you will only have a partial LRU list dump for that buffer pool instance – that is, until the next dump occurs. If the server crashes or is shutdown before the next dump takes place the existing one won’t be totally up to date for the server warm up to complete – it will still be used and will still provide a partially filled, somewhat “warm” buffer pool, just not as optimal as it could have been if the last dump had been taken fully.

The post Worrying about the ‘InnoDB: detected cycle in LRU for buffer pool (…)’ message? appeared first on MySQL Performance Blog.

Software is not installed

Lastest Forum Posts - February 25, 2015 - 5:26am
I try to install the software on my VPS.
This is the server that I have:

PRETTY_NAME="Debian GNU/Linux 7 (wheezy)"
NAME="Debian GNU/Linux"
VERSION_ID="7"
VERSION="7 (wheezy)"
ID=debian

I added this line:

deb http://repo.percona.com/apt wheezy main
deb-src http://repo.percona.com/apt wheezy main

And I run this command: apt-get update
The result:
Hit http://security.debian.org wheezy/updates Release.gpg
Hit http://security.debian.org wheezy/updates Release
Hit http://security.debian.org wheezy/updates/main Sources
Hit http://security.debian.org wheezy/updates/main amd64 Packages
Hit http://security.debian.org wheezy/updates/main Translation-en
Hit http://repo.percona.com wheezy Release.gpg
Hit http://ftp.us.debian.org wheezy Release.gpg
Hit http://repo.percona.com wheezy Release
Hit http://repo.percona.com wheezy/main Sources
Hit http://ftp.us.debian.org wheezy-updates Release.gpg
Hit http://repo.percona.com wheezy/main amd64 Packages
Hit http://ftp.us.debian.org wheezy Release
Hit http://ftp.us.debian.org wheezy-updates Release
Hit http://ftp.us.debian.org wheezy/main Sources
Ign http://repo.percona.com wheezy/main Translation-en
Hit http://ftp.us.debian.org wheezy/main amd64 Packages
Hit http://ftp.us.debian.org wheezy/main Translation-en
Hit http://ftp.us.debian.org wheezy-updates/main Sources
Hit http://ftp.us.debian.org wheezy-updates/main amd64 Packages/DiffIndex
Hit http://ftp.us.debian.org wheezy-updates/main Translation-en/DiffIndex

Then I run this command: apt-get upgrade

If I look at the folder /usr/bin I dont see any files named innobackupex .
If I run this command: innobackupex --user=USER--password=PW /backups
I get this error:
innobackupex: command not found
Trying this command: /usr/bin/innobackupex --user=USER--password=PW /backups
Doest work too.

Its seems like the software is not installed.

Nagios State Unkown

Lastest Forum Posts - February 25, 2015 - 5:03am
Good day

we have a small problem with the percona monitoring plugins for nagios.

We use for a MySQL Galera Cluster several checks with pmp-check-mysql-status.

Primary Cluster
pmp-check-mysql-status -x wsrep_cluster_status -C '==' -T str -c non-Primary -L local

Node State
pmp-check-mysql-status -x wsrep_local_state_comment -C '\!=' -T str -w Synced -L local

Cluster Size
pmp-check-mysql-status -x wsrep_cluster_size -C "<=" -w 2 -c 1 -L local

Flow Control
pmp-check-mysql-status -x wsrep_flow_control_paused -w 0.1 -c 0.9 -L local

The Problem is if the database offline or not reachable or the login is wrong, the check change it state to unkown. But we want for this case an critical if the check cannot be processed.

So i could modify the script itself but it's a very dirty solution and i didn't find another solution at the moment.

What would be the best practice way?

Using MySQL Event Scheduler and how to prevent contention

Latest MySQL Performance Blog posts - February 25, 2015 - 3:00am

MySQL introduced the Event Scheduler in version 5.1.6. The Event Scheduler is a MySQL-level “cron job”, which will run events inside MySQL. Up until now, this was not a very popular feature, however, it has gotten more popular since the adoption of Amazon RDS – as well as similar MySQL database as a service offerings where there is no OS level.

What is important to understand about the Event Scheduler is that it does not have any protection against multiple execution (neither does linux cron). Let’s imagine you have created an event that executes every 10 seconds, but the logic inside the event (i.e. queries or stored procedure call) can take longer than 10 seconds (may be in case of the high load), so it can pile-up. In the worst case, when an event contains a set of “insert” + “update”/”delete” statement inside a transaction, it can cause a deadlock.

Adding “get_lock” conditions inside of the event will help to prevent such situation:

If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking. Read more at event_scheduler documentation.

Function GET_LOCK() can be used for communications between threads:

The following example can illustrate using get_lock:

DELIMITER // CREATE EVENT testlock_event ON SCHEDULE EVERY 2 SECOND DO BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN DO RELEASE_LOCK('testlock_event'); END; IF GET_LOCK('testlock_event', 0) THEN -- add some business logic here, for example: -- insert into test.testlock_event values(NULL, NOW()); END IF; DO RELEASE_LOCK('testlock_event'); END; // DELIMITER ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION is needed here to release lock even if the event failed or was killed.

The above GET_LOCK / RELEASE_LOCK combination will help to prevent contention inside the MySQL Event Scheduler.

The post Using MySQL Event Scheduler and how to prevent contention appeared first on MySQL Performance Blog.

XtraDB Initial SST fails.

Lastest Forum Posts - February 25, 2015 - 1:44am
Hello,

I've been following the Ubuntu installation guide, but have hit a few problems when trying to bring the second node into the cluster. The first node is bootstrapped and seems to be fine, but when I start the second node the initial State transfer fails.

Packages (same on both nodes)
ii percona-xtradb-cluster-server-5.6 5.6.21-25.8-938.trusty
ii percona-xtrabackup 2.2.9-5067-1.trusty

my.cnf

: # Path to Galera library wsrep_provider=/usr/lib/libgalera_smm.so # Cluster connection URL wsrep_cluster_address=gcomm://10.17.40.150,10.17.40.135,10.17.40.132 #wsrep_cluster_address=gcomm:// # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how |InnoDB| autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Authentication for SST method wsrep_sst_auth="sstuser:passwordhere" # Node #1 address wsrep_node_address=10.17.40.150 # SST method wsrep_sst_method=xtrabackup-v2 # Cluster name wsrep_cluster_name=brp #innodb_buffer_pool_size=145774M innodb_flush_log_at_trx_commit=2 innodb_file_per_table=1 innodb_data_file_path = ibdata1:100M:autoextend ## You may want to tune the below depending on number of cores and disk sub innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_io_capacity=200 innodb_doublewrite=1 innodb_log_file_size=1024M innodb_log_buffer_size=96M innodb_buffer_pool_instances=8 innodb_log_files_in_group=2 innodb_thread_concurrency=64 #innodb_file_format=barracuda innodb_flush_method = O_DIRECT innodb_autoinc_lock_mode=2 ## avoid statistics update when doing e.g show tables innodb_stats_on_metadata=0 innodb_data_home_dir=/var/lib/mysql innodb_log_group_home_dir=/var/lib/mysql
: xtrabackup would have been started with the following arguments: --wsrep_provider=/usr/lib/libgalera_smm.so --wsrep_cluster_address=gcomm://10.17.40.150,10.17.40.135,10.17.40.132 --binlog_format=ROW --default_storage_engine=InnoDB --innodb_autoinc_lock_mode=2 --wsrep_sst_auth=sstuser:passwordhere --wsrep_node_address=10.17.40.150 --wsrep_sst_method=xtrabackup-v2 --wsrep_cluster_name=brp --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --lc-messages-dir=/usr/share/mysql --skip-external-locking --bind-address=0.0.0.0 --key_buffer=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam-recover=BACKUP --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_file_size=1024M --innodb_log_buffer_size=96M --innodb_buffer_pool_instances=8 --innodb_log_files_in_group=2 --innodb_thread_concurrency=64 --innodb_flush_method=O_DIRECT --innodb_autoinc_lock_mode=2 --innodb_stats_on_metadata=0 --innodb_data_home_dir=/var/lib/mysql --innodb_log_group_home_dir=/var/lib/mysql --query_cache_limit=1M --query_cache_size=16M --log_error=/var/log/mysql/error.log --expire_logs_days=10 --max_binlog_size=100M Errors from mysql-error.log (node 2)

: 2015-02-25 09:32:48 26570 [Note] WSREP: State transfer required: Group state: 8e461731-bc35-11e4-8a30-32847f70120f:3 Local state: 00000000-0000-0000-0000-000000000000:-1 2015-02-25 09:32:48 26570 [Note] WSREP: New cluster view: global state: 8e461731-bc35-11e4-8a30-32847f70120f:3, view# 16: Primary, number of nodes: 2, my index: 0, protocol version 3 2015-02-25 09:32:48 26570 [Warning] WSREP: Gap in state sequence. Need state transfer. 2015-02-25 09:32:48 26570 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.17.40.135' --auth 'sstuser:passwordhere' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '26570' '' ' WSREP_SST: [INFO] Streaming with xbstream (20150225 09:32:48.996) WSREP_SST: [INFO] Using socat as streamer (20150225 09:32:49.000) WSREP_SST: [INFO] Stale sst_in_progress file: /var/lib/mysql//sst_in_progress (20150225 09:32:49.395) WSREP_SST: [INFO] Evaluating timeout -k 110 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20150225 09:32:49.465) 2015-02-25 09:32:49 26570 [Note] WSREP: Prepared SST request: xtrabackup-v2|10.17.40.135:4444/xtrabackup_sst//1 2015-02-25 09:32:49 26570 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2015-02-25 09:32:49 26570 [Note] WSREP: REPL Protocols: 6 (3, 2) 2015-02-25 09:32:49 26570 [Note] WSREP: Service thread queue flushed. 2015-02-25 09:32:49 26570 [Note] WSREP: Assign initial position for certification: 3, protocol version: 3 2015-02-25 09:32:49 26570 [Note] WSREP: Service thread queue flushed. 2015-02-25 09:32:49 26570 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (8e461731-bc35-11e4-8a30-32847f70120f): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():456. IST will be unavailable. 2015-02-25 09:32:49 26570 [Note] WSREP: Member 0.0 (brppxdbl02) requested state transfer from '*any*'. Selected 1.0 (brppxdbcl01)(SYNCED) as donor. 2015-02-25 09:32:49 26570 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 3) 2015-02-25 09:32:49 26570 [Note] WSREP: Requesting state transfer: success, donor: 1 WSREP_SST: [INFO] Proceeding with SST (20150225 09:32:50.439) WSREP_SST: [INFO] Cleaning the existing datadir (20150225 09:32:50.443) removed ‘/var/lib/mysql/ib_logfile1’ removed ‘/var/lib/mysql/ibdata1’ removed ‘/var/lib/mysql/ib_logfile0’ removed ‘/var/lib/mysql/backup-my.cnf’ removed ‘/var/lib/mysql/auto.cnf’ WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20150225 09:32:50.549) 2015-02-25 09:32:51 26570 [Note] WSREP: (426f677b, 'tcp://0.0.0.0:4567') turning message relay requesting off grep: /var/lib/mysql//xtrabackup_checkpoints: No such file or directory WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql/ (20150225 09:33:00.698) 2015-02-25 09:33:00 26570 [Warning] WSREP: 1.0 (brppxdbcl01): State transfer to 0.0 (brppxdbl02) failed: -22 (Invalid argument) 2015-02-25 09:33:00 26570 [ERROR] WSREP: gcs/src/gcs_group.cpp:int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():722: Will never receive state. Need to abort. 2015-02-25 09:33:00 26570 [Note] WSREP: gcomm: terminating thread 2015-02-25 09:33:00 26570 [Note] WSREP: gcomm: joining thread 2015-02-25 09:33:00 26570 [Note] WSREP: gcomm: closing backend WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20150225 09:33:00.707) WSREP_SST: [ERROR] Cleanup after exit with status:1 (20150225 09:33:01.308) 2015-02-25 09:33:01 26570 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.17.40.135' --auth 'sstuser:passwordhere' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '26570' '' : 1 (Operation not permitted) 2015-02-25 09:33:01 26570 [ERROR] WSREP: Failed to read uuid:seqno from joiner script. 2015-02-25 09:33:01 26570 [ERROR] WSREP: SST failed: 1 (Operation not permitted) 2015-02-25 09:33:01 26570 [ERROR] Aborting 2015-02-25 09:33:01 26570 [Note] WSREP: view(view_id(NON_PRIM,426f677b,16) memb { 426f677b,0 } joined { } left { } partitioned { 5ef44e98,0 }) 2015-02-25 09:33:01 26570 [Note] WSREP: view((empty)) 2015-02-25 09:33:01 26570 [Note] WSREP: gcomm: closed 2015-02-25 09:33:01 26570 [Note] WSREP: /usr/sbin/mysqld: Terminated. Aborted 150225 09:33:01 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I've seen several over posts similar to this, and so heres the permissions from my.cnf:
-rw-r--r-- 1 mysql root 4879 Feb 24 17:15 /etc/mysql/my.cnf
...and I do have datadir also, although it wasn't included in the snip above...
datadir = /var/lib/mysql


Could I please have some suggestions here? As a newcomer it is virtually impossible to troubleshoot, and this is a fairly basic setup following a guide on your website.

Thanks,

J / robothands

Is it safe to drop page cache on ubuntu ?

Lastest Forum Posts - February 24, 2015 - 5:01pm
Hello,

I am using MySQL 5.6 on ubuntu 14.04.
MySQL server was killed by OS yesterday. (oom kill)

I investigated the memory usage.

$ free -m
total used free shared buffers cached
Mem: 122952 121468 1483 0 285 44494
-/+ buffers/cache: 76689 46263
Swap: 0 0 0
$

It is a dedicated database server but so much "cached".
So I have a plan for dropping page cache.
  • To free pagecache:

    $ echo 1 > /proc/sys/vm/drop_caches
  • To free dentries and inodes:

    $ echo 2 > /proc/sys/vm/drop_caches
  • To free pagecache, dentries and inodes:

    $ echo 3 > /proc/sys/vm/drop_caches
Can anyone let me know it is safe to drop page cache ?
Does it can have any side effect ?

I have a ubuntu server that just one MySQL server is running on.

Thanks.

Circular and Multi-Master Replication tools

Lastest Forum Posts - February 24, 2015 - 5:24am
I am trying to find out the solution/tools of multi-master replication (4 mysql nodes) when one node goes offline then it should not effected the replication but when it back be online then all replication should work properly.
Please let me know if any tools for above situation.

Thanks

Circular and Multi-Master Replication tools

Lastest Forum Posts - February 24, 2015 - 5:24am
I am trying to find out the solution/tools of multi-master replication (4 mysql nodes) when one node goes offline then it should not effected the replication but when it back be online then all replication should work properly.
Please let me know if any tools for above situation.

Thanks

InnoDB: Warning: you are running out of new single-table tablespace id's.

Lastest Forum Posts - February 24, 2015 - 4:34am
I am looking for general information regarding this message:
InnoDB: Warning: you are running out of new single-table tablespace id's. InnoDB: Current counter is 2152000000 and it must not exceed 4294967280! InnoDB: To reset the counter to zero you have to dump all your tables and InnoDB: recreate the whole InnoDB installation.
Which counter? How do you query it? Does an entire restore fix this problem?

Thanks.

Is MySQL’s innodb_file_per_table slowing you down?

Latest MySQL Performance Blog posts - February 24, 2015 - 3:00am

MySQL’s innodb_file_per_table is a wonderful thing – most of the time. Having every table use its own .ibd file allows you to easily reclaim space when dropping or truncating tables. But in some use cases, it may cause significant performance issues.

Many of you in the audience are responsible for running automated tests on your codebase before deploying to production. If you are, then one of your goals is having tests run as quickly as possible so you can run them as frequently as possible. Often times you can change specific settings in your test environment that don’t affect the outcome of the test, but do improve throughput. This post discusses how innodb_file_per_table is one of those settings.

I recently spoke with a customer whose use case involved creating hundreds of tables on up to 16 schemas concurrently as part of a Jenkins testing environment. This was not in production, so performance was far more important than durability. They’d run their tests, and then drop the schemas. This process took close to 20 minutes. They asked “How can we make this faster?”

Due to the number of tables involved innodb_file_per_table seemed a likely culprit.

It’s been noted here on the MySQL Performance Blog that innodb_file_per_table can cause table creation and drops to slow down. But what exactly is the performance hit? We wanted to find out.

The innodb_file_per_table Test:

On a test server running CentOS release 6.5, xfs filesystem, and 5.6.22-71.0-log Percona Server, I ran the following homemade benchmark bash script:

[root@host ~]# time $(for db in {1..16}; do mysql -e "create database bench$db"; $(for tb in {1..500}; do $(mysql bench$db -e "create table tab${tb} (i int) engine=innodb"); done) & done)

If you open the mysql client in another screen or terminal, you should see something like this:

... +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+          | Id    | User | Host      | db      | Command | Time | State          | Info                                     | Rows_sent | Rows_examined |          +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+          | 80013 | root | localhost | NULL    | Query   |    0 | init           | show processlist                         |         0 |             0 |          | 89462 | root | localhost | bench5  | Query   |    0 | creating table | create table tab95 (i int) engine=innodb |         0 |             0 |          | 89466 | root | localhost | bench8  | Query   |    0 | creating table | create table tab81 (i int) engine=innodb |         0 |             0 |          | 89467 | root | localhost | bench1  | Query   |    0 | creating table | create table tab91 (i int) engine=innodb |         0 |             0 |          | 89468 | root | localhost | bench13 | Query   |    0 | creating table | create table tab90 (i int) engine=innodb |         0 |             0 |          | 89469 | root | localhost | bench15 | Query   |    0 | creating table | create table tab90 (i int) engine=innodb |         0 |             0 |          | 89472 | root | localhost | bench9  | Query   |    0 | creating table | create table tab86 (i int) engine=innodb |         0 |             0 |          | 89473 | root | localhost | bench10 | Query   |    0 | creating table | create table tab94 (i int) engine=innodb |         0 |             0 |          | 89474 | root | localhost | bench11 | Query   |    0 | creating table | create table tab80 (i int) engine=innodb |         0 |             0 |          | 89475 | root | localhost | bench3  | Query   |    0 | creating table | create table tab80 (i int) engine=innodb |         0 |             0 |          | 89476 | root | localhost | bench2  | Query   |    0 | creating table | create table tab82 (i int) engine=innodb |         0 |             0 |          | 89478 | root | localhost | bench4  | Query   |    0 | creating table | create table tab91 (i int) engine=innodb |         0 |             0 |          | 89479 | root | localhost | bench16 | Query   |    0 | creating table | create table tab88 (i int) engine=innodb |         0 |             0 |          | 89481 | root | localhost | bench12 | Query   |    0 | creating table | create table tab90 (i int) engine=innodb |         0 |             0 |          | 89483 | root | localhost | bench6  | Query   |    0 | creating table | create table tab96 (i int) engine=innodb |         0 |             0 |          | 89484 | root | localhost | bench14 | Query   |    0 | creating table | create table tab95 (i int) engine=innodb |         0 |             0 |          +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+          ...        

After creating the tables, I dropped all schemas concurrently:

[root@host ~]# time $(for db in {1..16}; do mysql -e "drop database bench${db}" & done)

So what was the difference with innodb_file_per_table ON vs OFF?

  • With innodb_file_per_table=ON
    • Schema and table creation = 1m54.852s
    • Schema drops = 1m21.682s
  • With innodb_file_per_table=OFF
    • Schema and table creation = 0m59.968s
    • Schema drops = 0m54.870s

So creation time decreased by 48%, drop time decreased by 33%.

I think its worth noting that this benchmark creates and drops empty tables. Dropping InnoDB tables created with innodb_file_per_table=ON can take much longer if they have large amounts of data.

Please also be aware that there are always trade-offs when modifying your InnoDB settings. That is outside the scope of this post, so please research and test before making changes. The MySQL documentation discusses that here.  In 5.6.6 and up, innodb_file_per_table is ON by default. MySQL 5.6 will also create temp tables as InnoDB, as noted here.

So there you have it. If your primary goal is to improve create and drop table time, turning OFF innodb_file_per_table will save significant amounts of time.

The post Is MySQL’s innodb_file_per_table slowing you down? appeared first on MySQL Performance Blog.

Running percona xtradb without the cluster part

Lastest Forum Posts - February 23, 2015 - 3:50pm
Hi

We are using percona xtradb cluster 5.6 on opensuse with 3 nodes.
Due to some another issue we won't be using cluster for next 2-3 months.

From my understanding percona xtradb can be run without cluster part if wsrep config is not included and mysql is started using start command instead of bootstrap.

I haven't tried it yet as it is running on live site but we are planning to shut down other 2 nodes as on the live machine mysql is eating lots of resources because it needs to sync across 3 nodes.

I think we can save some memory and cpu cycles by switching 2 nodes off and just starting mysql with start option.

Is that the right approach?
what precautions I should take while doing that?


Thanks in advance for your help.


percona cluster db on 2nd node fail to start

Lastest Forum Posts - February 23, 2015 - 10:59am
When I start mysql service in ubuntu. error.log keep looping at following message. 150223 23:23:55 [Note] WSREP: Read nil XID from storage engines, skipping position init
150223 23:23:55 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/libgalera_smm.so'
150223 23:23:55 [Note] WSREP: wsrep_load(): Galera 2.8(r165) by Codership Oy <info@codership.com> loaded successfully.
150223 23:23:55 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
150223 23:23:55 [Note] WSREP: Reusing existing '/var/lib/mysql//galera.cache'.
150223 23:23:55 [Note] WSREP: Passing config to GCS: base_host = 10.1.1.30; base_port = 4567; cert.log_conflicts = no; 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; 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; replicator.causal_read_timeout = PT30S; replicator.commit_order = 3
150223 23:23:55 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
150223 23:23:55 [Note] WSREP: wsrep_sst_grab()
150223 23:23:55 [Note] WSREP: Start replication
150223 23:23:55 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
150223 23:23:55 [Note] WSREP: protonet asio version 0
150223 23:23:55 [Note] WSREP: backend: asio
150223 23:23:55 [Note] WSREP: GMCast version 0
150223 23:23:55 [Note] WSREP: (ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
150223 23:23:55 [Note] WSREP: (ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
150223 23:23:55 [Note] WSREP: EVS version 0
150223 23:23:55 [Note] WSREP: PC version 0
150223 23:23:55 [Note] WSREP: gcomm: connecting to group 'cluster', peer '10.1.1.29:,10.1.1.30:'
150223 23:23:55 [Warning] WSREP: (ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc, 'tcp://0.0.0.0:4567') address 'tcp://10.1.1.30:4567' points to own listening address, blacklisting
150223 23:23:55 [Note] WSREP: (ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc, 'tcp://0.0.0.0:4567') address 'tcp://10.1.1.30:4567' pointing to uuid ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc is blacklisted, skipping
150223 23:23:56 [Note] WSREP: declaring 9cc1474d-bb83-11e4-af2d-4a569b62ed1c stable
150223 23:23:56 [Note] WSREP: Node 9cc1474d-bb83-11e4-af2d-4a569b62ed1c state prim
150223 23:23:56 [Note] WSREP: view(view_id(PRIM,9cc1474d-bb83-11e4-af2d-4a569b62ed1c,36) memb {
9cc1474d-bb83-11e4-af2d-4a569b62ed1c,
ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc,
} joined {
} left {
} partitioned {
})
150223 23:23:56 [Note] WSREP: gcomm: connected
150223 23:23:56 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
150223 23:23:56 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
150223 23:23:56 [Note] WSREP: Opened channel 'cluster'
150223 23:23:56 [Note] WSREP: Waiting for SST to complete.
150223 23:23:56 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
150223 23:23:56 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
150223 23:23:56 [Note] WSREP: STATE EXCHANGE: sent state msg: 53dffab6-bb8a-11e4-8a2b-e309e267ec98
150223 23:23:56 [Note] WSREP: STATE EXCHANGE: got state msg: 53dffab6-bb8a-11e4-8a2b-e309e267ec98 from 0 (Pluto)
150223 23:23:56 [Note] WSREP: STATE EXCHANGE: got state msg: 53dffab6-bb8a-11e4-8a2b-e309e267ec98 from 1 (mars)
150223 23:23:56 [Note] WSREP: Quorum results:
version = 2,
component = PRIMARY,
conf_id = 35,
members = 1/2 (joined/total),
act_id = 1964141,
last_appl. = -1,
protocols = 0/4/3 (gcs/repl/appl),
group UUID = a1ce21ad-b997-11e4-ad11-a6b52963ec45
150223 23:23:56 [Note] WSREP: Flow-control interval: [23, 23]
150223 23:23:56 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 1964141)
150223 23:23:56 [Note] WSREP: State transfer required:
Group state: a1ce21ad-b997-11e4-ad11-a6b52963ec45:1964141
Local state: 00000000-0000-0000-0000-000000000000:-1
150223 23:23:56 [Note] WSREP: New cluster view: global state: a1ce21ad-b997-11e4-ad11-a6b52963ec45:1964141, view# 36: Primary, number of nodes: 2, my index: 1, protocol version 3
150223 23:23:56 [Warning] WSREP: Gap in state sequence. Need state transfer.
150223 23:23:56 [Note] WSREP: Running: 'wsrep_sst_xtrabackup --role 'joiner' --address '10.1.1.30' --auth 'userass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '3589''
150223 23:23:56 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup --role 'joiner' --address '10.1.1.30' --auth 'userass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '3589'
Read: '(null)'
150223 23:23:56 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup --role 'joiner' --address '10.1.1.30' --auth 'userass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '3589': 2 (No such file or directory)
150223 23:23:56 [ERROR] WSREP: Failed to prepare for 'xtrabackup' SST. Unrecoverable.
150223 23:23:56 [ERROR] Aborting

150223 23:23:58 [Note] WSREP: Closing send monitor...
150223 23:23:58 [Note] WSREP: Closed send monitor.
150223 23:23:58 [Note] WSREP: gcomm: terminating thread
150223 23:23:58 [Note] WSREP: gcomm: joining thread
150223 23:23:58 [Note] WSREP: gcomm: closing backend
150223 23:23:59 [Note] WSREP: view(view_id(NON_PRIM,9cc1474d-bb83-11e4-af2d-4a569b62ed1c,36) memb {
ef9c60a1-bb84-11e4-ae0e-5f6dda01f2fc,
} joined {
} left {
} partitioned {
9cc1474d-bb83-11e4-af2d-4a569b62ed1c,
})
150223 23:23:59 [Note] WSREP: view((empty))
150223 23:23:59 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
150223 23:23:59 [Note] WSREP: gcomm: closed
150223 23:23:59 [Note] WSREP: Flow-control interval: [16, 16]
150223 23:23:59 [Note] WSREP: Received NON-PRIMARY.
150223 23:23:59 [Note] WSREP: Shifting PRIMARY -> OPEN (TO: 1964153)
150223 23:23:59 [Note] WSREP: Received self-leave message.
150223 23:23:59 [Note] WSREP: Flow-control interval: [0, 0]
150223 23:23:59 [Note] WSREP: Received SELF-LEAVE. Closing connection.
150223 23:23:59 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 1964153)
150223 23:23:59 [Note] WSREP: RECV thread exiting 0: Success
150223 23:23:59 [Note] WSREP: recv_thread() joined.
150223 23:23:59 [Note] WSREP: Closing replication queue.
150223 23:23:59 [Note] WSREP: Closing slave action queue.
150223 23:23:59 [Note] WSREP: Service disconnected.
150223 23:23:59 [Note] WSREP: rollbacker thread exiting
150223 23:24:00 [Note] WSREP: Some threads may fail to exit.
150223 23:24:00 [Note] /usr/sbin/mysqld: Shutdown complete


I ran wsrep_sst_xtrabackup command line and it seem ok.

user@home# wsrep_sst_xtrabackup --role 'joiner' --address '10.1.1.30' --auth 'userass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '3474'
WSREP_SST: [INFO] Streaming with tar (20150223 23:43:10.344)
WSREP_SST: [INFO] Using socat as streamer (20150223 23:43:10.347)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | tar xfi - --recursive-unlink -h; RC=( ${PIPESTATUS[@]} ) (20150223 23:43:10.368)
ready 10.1.1.30:4444/xtrabackup_sst

Any idea where's going wrong?

pt-archiver: multiple dependent tables?

Lastest Forum Posts - February 23, 2015 - 10:46am
Hi,

does the pt-archiver tool allows to archive multiple dependent tables in a single run?

Example:
- Table A has the column(s) with the functional archive critera (e.g. archive all rows where date_column older than 3 months)
- Table B references table A
- Table C contains rows which reference rows in table B
- Table C contains rows which has no references to table B and date_column older than 3 months
- ..

I would like to archive all rows which match the example to a different MySQL instance on a different host

Thanks for help,
Jörg

Percona XtraDB-Cluster 5.6.21-70.1-56 on CentOS 6.6 Second node won't connect to it.

Lastest Forum Posts - February 23, 2015 - 8:52am
Hello everyone.
In advanced thanks for this community. It has always been helpful for me.
Here is my Servers Configuration. I know this post will be a bit long but please bear with me.

Server A.
OS = CentOS = 6.6 Final
Memory = 4GB
Processors = 4
HDD = 30GB to start of space on /var partition
The root part "/" has 5.8 GB avail from 8 so only 20% used.
NIC with IP = 192.168.101.12


Server B.
OS = CentOS = 6.6 Final
Memory = 4GB
Processors = 4
HDD = 30GB to start of space on /var partition
The root part "/" has 5.8 GB avail from 8 so only 20% used.
NIC with IP = 192.168.101.13

Server A ... here is my.cnf for this server ..
File attached due to size of post

Server B ... here is my.cnf for this server ..
File attached due to size of post


On Server A I run ... netstat -tuln and I get ..

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN
tcp 0 0 :::22 :::* LISTEN
tcp 0 0 :::3306 :::* LISTEN

On Server A I stop the service and then I tail /var/lib/mysql/MySQL-Cluster0.err while starting and I get this ... NOTE ** starting the node with command .. /etc/init.d/mysql bootstrap-pxc

Here is the log ..
Log attached due to post size. Thanks


Now.
On both servers I have IPtables turned off and there is no firewall in between since they sit on an internal network as you all can see.
Now I still tail the log file while trying to connect the second node and this is what I get ..

Here is the log from the bootstrapped node. First node.. SERVER A
File attached due to post size



Now I go to the Server A (first node) and check
less /var/lib/mysql/innobackup.backup.log

and I get this ..

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150223 11:25:54 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_s ocket=/var/run/mysqld/mysqld.sock' (using password: NO).
innobackupex: got a fatal error with the following stacktrace: at /usr//bin/innobackupex line 3004
main::mysql_connect('abort_on_error', 1) called at /usr//bin/innobackupex line 1539
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_s ocket=/var/run/mysqld/mysqld.sock','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /usr//bin/innobackupex line 2988

Again ports 22 - 3306 - 4444 - 4567 - 4568 are all open I double checked that already.
I have read every thread and tried everything for the past 2 days ..
Sorry if I missed anything and for the long post but I tried to be specific as I could.
Thanks in advanced to everyone.

PS On Server A (node 1) root already has a password and sstuser is created with the same pass as on node 2 cnf file plus on both servera (Server a & B) under mysql DB mysql user with all rights its created too.


Thank you
Ed

Tables locked while incrementel backup

Lastest Forum Posts - February 23, 2015 - 8:34am
Hello Community

I want to do an incremental backup between 8 - 18 o'clock every 2 hours.

But the problem is while the backup is running the data will be locked and the user can't work.
In the database i have just innodb tables.

I run the script with the followed parameters:
innobackupex --user="$MYSQL_USER" --password="$MYSQL_PASS" --no-lock --incremental --incremental-basedir="$LAST_BACKUP" "$INCREMENTALS_DIRECTORY

I have no replication server.

Is it possible to do a hotbackup whitouht a replication server?
Do execute the backup-script with the correct parameter or some ohter mistakes?

Thanks for your help.
Christoph

MySQL community t-shirt contest for Percona Live 2015

Latest MySQL Performance Blog posts - February 23, 2015 - 3:00am

Have designs on Percona Live this April in Silicon Valley? Send them to us! The winning entry will appear on a cool limited edition t-shirt that we’ll award to a few dozen lucky recipients at our booth’s new Percona T-Shirt Bar. The winner will also get a t-shirt, of course, along with a pair “Beats by Dre” headphones!

That’s right: We’re calling on the creative types within the MySQL community to come up with designs for a unique t-shirt.

Let your imaginations run free! Just make sure to include “Percona Live 2015” in there somewhere. You might also want to include your signature, hanko, seal or mark… treat the cotton as the canvas of your masterpiece… let the world know who created it!

Send your t-shirt designs to me as a high-resolution PDF or in .EPS format. The deadline for entries is March 6. The winner will be chosen under the sole discretion of Percona’s marketing team, taking into consideration quality of design, values alignment, trademark clearance and general awesomeness. (Submitted designs assume unlimited royalty free rights usage by Percona. We also reserve the right to declare no winners if there are no suitable designs submitted. You do not need to register or attend the conference to submit a design.)

Click here to submit your design for the MySQL community t-shirt contest!

By the way, the image on this post is not a template. You have free rein so go get ‘em! And just to be clear: this won’t be the t-shirt that everyone receives at the registration booth. However, it just might be one of the most coveted t-shirts at the conference!

I’ll share the winning design the week of March 9. Good luck and I hope to see you all this April and the Percona Live MySQL Conference and Expo! The conference runs April 13-16 at the Hyatt Regency Santa Clara & the Santa Clara Convention Center.

 

The post MySQL community t-shirt contest for Percona Live 2015 appeared first on MySQL Performance Blog.

XtraDB Cluster loads, but no PID File, MySQL wont load.

Lastest Forum Posts - February 21, 2015 - 12:38pm
Hi there, I recently setup Percona XtraDB Cluster on CentOS6.6 following the documentation.(http://www.percona.com/doc/percona-x...ots_howto.html) however, when I reboot the servers (virtual machines running 1 CPU and 512 Ram) it takes a long time to boot, once booted MySQL fails to start/restart with the following error:

[root@www2 ~]# service mysql restart
Shutting down MySQL (Percona XtraDB Cluster) ERROR! MySQL (Percona XtraDB Cluster) PID file could not be found!
ERROR! MySQL (Percona XtraDB Cluster) is running but PID file could not be found
ERROR! Failed to restart server.
[root@www2 ~]# service mysql start
ERROR! MySQL (Percona XtraDB Cluster) is running but PID file could not be found
[root@www2 ~]# service mysql stop
Shutting down MySQL (Percona XtraDB Cluster) ERROR! MySQL (Percona XtraDB Cluster) PID file could not be found!

When I try and connect to the MySQL database I get told it could not connect via the sock

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

Which is obvious because MySQL isn;t actually running. But I can;t even start it witout Percona spitting out the No PID Error

Here is my config file (As per the instructions I followed at the link above)

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.5.101,192.168.5.102

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.5.101

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
.

I also added the following lines to the bottom of the file with no avail

# My Additions
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/lib/mysql/mysql.sock

My error logs show this:

150221 14:52:11 mysqld_safe Skipping wsrep-recover for 2a6bf596-b940-11e4-8a4a-6f644114be5a:2 pair
150221 14:52:11 mysqld_safe Assigning 2a6bf596-b940-11e4-8a4a-6f644114be5a:2 to wsrep_start_position
2015-02-21 14:52:15 0 [Note] WSREP: wsrep_start_position var submitted: '2a6bf596-b940-11e4-8a4a-6f644114be5a:2'
2015-02-21 14:52:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-21 14:52:16 1544 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-02-21 14:52:16 1544 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/libgalera_smm.so'
2015-02-21 14:52:16 1544 [Note] WSREP: wsrep_load(): Galera 3.8(rf6147dd) by Codership Oy <info@codership.com> loaded successfully.
2015-02-21 14:52:16 1544 [Note] WSREP: CRC-32C: using hardware acceleration.
2015-02-21 14:52:16 1544 [Note] WSREP: Found saved state: 2a6bf596-b940-11e4-8a4a-6f644114be5a:2
2015-02-21 14:52:16 1544 [Note] WSREP: Passing config to GCS: base_host = 192.168.5.101; 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; pc.npvo = false; pc.recove
2015-02-21 14:52:17 1544 [Note] WSREP: Service thread queue flushed.
2015-02-21 14:52:17 1544 [Note] WSREP: Assign initial position for certification: 2, protocol version: -1
2015-02-21 14:52:17 1544 [Note] WSREP: wsrep_sst_grab()
2015-02-21 14:52:17 1544 [Note] WSREP: Start replication
2015-02-21 14:52:17 1544 [Note] WSREP: Setting initial position to 2a6bf596-b940-11e4-8a4a-6f644114be5a:2
2015-02-21 14:52:17 1544 [Note] WSREP: protonet asio version 0
2015-02-21 14:52:17 1544 [Note] WSREP: Using CRC-32C for message checksums.
2015-02-21 14:52:17 1544 [Note] WSREP: backend: asio
2015-02-21 14:52:17 1544 [Warning] WSREP: access file(gvwstate.dat) failed(No such file or directory)
2015-02-21 14:52:17 1544 [Note] WSREP: restore pc from disk failed
2015-02-21 14:52:17 1544 [Note] WSREP: GMCast version 0
2015-02-21 14:52:17 1544 [Note] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2015-02-21 14:52:17 1544 [Note] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2015-02-21 14:52:17 1544 [Note] WSREP: EVS version 0
2015-02-21 14:52:17 1544 [Note] WSREP: gcomm: connecting to group 'my_centos_cluster', peer '192.168.5.101:,192.168.5.102:'
2015-02-21 14:52:17 1544 [Warning] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') address 'tcp://192.168.5.101:4567' points to own listening address, blacklisting
2015-02-21 14:52:17 1544 [Note] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') address 'tcp://192.168.5.101:4567' pointing to uuid 23dfb830 is blacklisted, skipping
2015-02-21 14:52:17 1544 [Note] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2015-02-21 14:52:18 1544 [Note] WSREP: declaring 8fbe1659 at tcp://192.168.5.102:4567 stable
2015-02-21 14:52:18 1544 [Warning] WSREP: no nodes coming from prim view, prim not possible
2015-02-21 14:52:18 1544 [Note] WSREP: view(view_id(NON_PRIM,23dfb830,8) memb {
23dfb830,0
8fbe1659,0
} joined {
} left {
} partitioned {
a4d14ed5,0
bc924c22,0
eb831cca,0
})
2015-02-21 14:52:18 1544 [Note] WSREP: gcomm: connected
2015-02-21 14:52:18 1544 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
2015-02-21 14:52:18 1544 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
2015-02-21 14:52:18 1544 [Note] WSREP: Opened channel 'my_centos_cluster'
2015-02-21 14:52:18 1544 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 2
2015-02-21 14:52:18 1544 [Note] WSREP: Flow-control interval: [23, 23]
2015-02-21 14:52:18 1544 [Note] WSREP: Received NON-PRIMARY.
2015-02-21 14:52:18 1544 [Note] WSREP: Waiting for SST to complete.
2015-02-21 14:52:18 1544 [Note] WSREP: New cluster view: global state: 2a6bf596-b940-11e4-8a4a-6f644114be5a:2, view# -1: non-Primary, number of nodes: 2, my index: 0, protocol version -1
2015-02-21 14:52:18 1544 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2015-02-21 14:52:21 1544 [Note] WSREP: (23dfb830, 'tcp://0.0.0.0:4567') turning message relay requesting off

And it does not create a .pid file or a .sock file.

Any ideas?

Pages

Subscribe to Percona aggregator
]]>