Comment 7 for bug 1349915

XtraDB Cluster 5.6.19-67.0

On Tue, Jul 29, 2014 at 5:48 PM, Jay Janssen <email address hidden> wrote:
> What version was this?
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1349915
>
> Title:
> Duplicate keys with table_open_cache_instances > 1
>
> Status in Percona XtraDB Cluster - HA scalable solution for MySQL:
> New
>
> Bug description:
> If all the nodes are written, and table_open_cache_instances is > 1, the following thing happens:
> # sysbench --test=/opt/sysbench/sysbench/tests/db/insert_autoinc.lua --oltp-table-size=100000 --oltp-tables-count=1 --num-threads=32 --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=sbtest --mysql-port=3307 --report-interval=1 --max-requests=0 --max-time=0 run
> WARNING: Both max-requests and max-time are 0, running endless test
> sysbench 0.5: multi-threaded system evaluation benchmark
>
> Running the test with following options:
> Number of threads: 32
> Report intermediate results every 1 second(s)
> Random number generator seed is 0 and will be ignored
>
>
> Threads started!
>
> [ 1s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 267.06, response time: 158.38ms (95%)
> [ 2s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 219.00, response time: 202.14ms (95%)
> [ 3s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 244.00, response time: 167.30ms (95%)
> [ 4s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 253.00, response time: 140.89ms (95%)
> [ 5s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 280.00, response time: 141.01ms (95%)
> [ 6s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 271.00, response time: 150.66ms (95%)
> [ 7s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 270.00, response time: 152.70ms (95%)
> [ 8s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 272.00, response time: 147.67ms (95%)
> [ 9s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 295.00, response time: 138.17ms (95%)
> [ 10s] threads: 32, tps: 0.00, reads/s: 0.00, writes/s: 250.00, response time: 149.71ms (95%)
> ALERT: failed to execute MySQL query: `INSERT INTO sbtest1 (k, c, pad) VALUES (50004, '89929769380-86884577498-44821537687-01291340378-94877073036-86937829586-68219997234-17550189837-39233820479-95928832298', '24150738052-65325743530-90033340743-93376107484-35784626790')`:
> ALERT: Error 1062 Duplicate entry '342031' for key 'PRIMARY'
> FATAL: failed to execute function `event': (null)
>
> It doesn't happen if table_open_cache_instances is 1 (default), or if
> I write only to one node. In order to produce this, a custom lua is
> needed for sysbench:
>
> # cat sysbench/tests/db/insert_autoinc.lua
> pathtest = string.match(test, "(.*/)") or ""
>
> dofile(pathtest .. "common.lua")
>
> function thread_init(thread_id)
> set_vars()
> end
>
> function event(thread_id)
> local table_name
> local i
> local c_val
> local k_val
> local pad_val
>
> table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
>
> if (oltp_auto_inc) then
> i = 0
> else
> i = sb_rand_uniq(1, oltp_table_size)
> end
> k_val = sb_rand(1, oltp_table_size)
> c_val = sb_rand_str([[
> ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
> pad_val = sb_rand_str([[
> ###########-###########-###########-###########-###########]])
>
> rs = db_query("INSERT INTO " .. table_name .. " (k, c, pad) VALUES " .. string.format("(%d, '%s', '%s')", k_val, c_val, pad_val))
>
> db_disconnect()
> end
>
> There are 2 changes compared to insert.lua:
> - The id column is removed from it and we let auto_increment to handle that.
> - db_disconnect is at the end, so sysbench will reconnect for every transaction.
>
> Locally, I had to set
> echo 1000 > /proc/sys/net/ipv4/tcp_max_tw_buckets
> so I don't run into TCP/IP port exhaustion.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1349915/+subscriptions

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