Re: [percona-group] Causing Deadlock error and asking to restart the transaction in xtradb cluster

[Jay Janssen]

Koya,

  And you are sure there are no writes happening on the other node at that time?  Deadlock errors (that are not actually dealocks) are what Galera returns to the client when there are multi-node writing errors.  Hence, you would not expect to see any deadlock reported in SHOW ENGINE INNODB STATUS at that time.  

  Do either of the two variables described here: http://www.mysqlperformanceblog.com/2012/11/20/understanding-multi-node-writing-conflict-metrics-in-percona-xtradb-cluster-and-galera/ increment at that time?

  Can you see wsrep_replicated increasing on the nodes?  If so, which ones?

  Can you send the output of "SHOW GLOBAL STATUS like 'wsrep%'"?

  

On Feb 8, 2013, at 9:48 AM, koya.sr...@cbsinteractive.com wrote:

I running Xtradb cluster with 2 physical nodes  and galera arbitrator on node 3.

when i try to create table as or insert to table with selects from multiple tables with joins it is fails with below error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

i am running on node1 directly.

I am trying to insert 2000000 rows.

when i check show engine innodb status or even pt-deadlock-logger no dead locks detected.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

Percona Live in Santa Clara, CA  April 22nd-25th 2013

http://www.percona.com/live/mysql-conference-2013/

[koya.sr...@cbsinteractive.com]

Hi Jay,

thanks for your response.

mysql> SHOW GLOBAL STATUS LIKE 'wsrep_%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c5334cde-645c-11e2-0800-2b97b2deedcb |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 993084                               |
| wsrep_replicated           | 8471                                 |
| wsrep_replicated_bytes     | 1035866364                           |
| wsrep_received             | 19                                   |
| wsrep_received_bytes       | 440                                  |
| wsrep_local_commits        | 7671                                 |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 2.500000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced (6)                           |
| wsrep_cert_index_size      | 7                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | c5334cde-645c-11e2-0800-2b97b2deedcb |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 1                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <in...@codership.com>    |
| wsrep_provider_version     | 2.2(r114)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
39 rows in set (0.00 sec)

mysql>


sure no writes are going on i even shutdown node2.

thanks.

[koya.sr...@cbsinteractive.com]

Hi Jay,

I notice below in error log:

130208 14:53:47 [Warning] WSREP: transaction size limit (1073741824) exceeded: 1073774592
130208 14:53:47 [ERROR] WSREP: rbr write fail, data_len: 0, 1026
130208 15:16:17 [Warning] WSREP: transaction size limit (1073741824) exceeded: 1073774592
130208 15:16:17 [ERROR] WSREP: rbr write fail, data_len: 0, 1026

[koya.sr...@cbsinteractive.com]

Hello Experts any suggestions....

[Ernie Souhrada]

At the risk of suggesting something obvious, what happens if you try a
smaller transaction size? Rather than trying to create ... select or
insert ... select something which is going to generate a 2M-row insert,
can you chunk this into two inserts of 1M rows each, or, better yet, 4
inserts of 500K ?

If you're trying to do 2M rows in a single statement, those rows would
only need to be slightly larger than 512 bytes each to run into a
situation like this:

130208 14:53:47 [Warning] WSREP: transaction size limit (1073741824)
exceeded: 1073774592

e.

> --
> You received this message because you are subscribed to the Google
> Groups "Percona Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to percona-discuss...@googlegroups.com.
> To post to this group, send email to percona-d...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

--
Ernie Souhrada, CMDBA, RHCE, CCNA
Senior Consultant, Percona. [ http://www.percona.com ]
email: ernest....@percona.com
phone: +1-888-401-3401 x543 [ US/Arizona : GMT-7 ]
skype: ravyn440

[koya.sr...@cbsinteractive.com]

When i split into small chunks of 500K insert works fine.

This case we need  to create many procedures.

original insert(2Mils) rows works fine in other Percona versions( standalone) only issue in xtradb cluster.

thanks.

[Jay Janssen]

On Feb 8, 2013, at 10:23 PM, koya.sr...@cbsinteractive.com wrote:

130208 14:53:47 [Warning] WSREP: transaction size limit (1073741824) exceeded: 1073774592

130208 14:53:47 [ERROR] WSREP: rbr write fail, data_len: 0, 1026
130208 15:16:17 [Warning] WSREP: transaction size limit (1073741824) exceeded: 1073774592
130208 15:16:17 [ERROR] WSREP: rbr write fail, data_len: 0, 1026

When i split into small chunks of 500K insert works fine.

This case we need  to create many procedures.

original insert(2Mils) rows works fine in other Percona versions( standalone) only issue in xtradb cluster.

thanks.

See also:

http://www.percona.com/doc/percona-xtradb-cluster/wsrep-index.html#wsrep_max_ws_rows

http://www.percona.com/doc/percona-xtradb-cluster/wsrep-index.html#wsrep_max_ws_size

Those are some big transactions!  You can bump up the max_ws thresholds, but I think you're better off with smaller chunks.  

[Peter Boros]

Hi Koya,

There are 2 parameters in galera to control this. One of them is
wsrep_max_ws_size and the other is wsrep_max_ws_rows, you are hitting
wsrep_max_ws_size here.

However, this means your write set is over 1 GB, which is huge. Large
transactions are not recommended anyway in MySQL, but they can
especially hurt when you are using XtraDB cluster. The good solution
is to split the transactions to chunks, most likely even 500M rows is
too much if the original workload produced a write set more than 1 GB.

Peter Boros

> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to percona-discuss...@googlegroups.com.
> To post to this group, send email to percona-d...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>



--

Peter Boros, Consultant, Percona
Telephone: +1 888 401 3401 ext 546
Emergency: +1 888 401 3401 ext 911
Skype: percona.pboros

Join us in Santa Clara for the annual Percona Live MySQL Conference & Expo 2013!
http://www.percona.com/live/mysql-conference-2013/

[koya.sr...@cbsinteractive.com]

Hello Experts,

I am trying to do create table as select from multiple tables with Joins which will create a new table with 2Mil records but reads few more Mils.
Can we come with a tool like online-schema-change for create table as which by default splits create table as statement to 5K rows and will create new table required.
I have no idea now how to by pass this or i have go back to normal master and slave setup.

[Justin Swanhart]

You could use select into outfile to dump into a flat file and then split that file into chunks.

[koya.sr...@cbsinteractive.com]

Yes i doing same way dumping to text file and then i am using pt-fifo-split to load back data to table, but i( any dba) cannot do this every time developers want to do create table as <new> with millions of rows.

I think it is better we add new tool to our Percona-toolkit for xtradb cluster to use for "create table as" which will default use "pt-fifo-split".

[Justin Swanhart]

Hi,

You can always increase the size of the variables if you feel your workload justifies increasing them.  Just be aware that you will probably use more memory and have a higher likelihood of conflicts if you are writing to more than one node.  The 1G limit is a sensible default, but defaults are meant to be changed.  You may also need to adjust max_binlog_cache_size (defaults to 4G) if you increase the wsrep limits past 4G.

--Justin