-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
Prior to implementing monthly mantainance script for poker-network
database, Loic wanted me to benchmark deletion of delete of 20 000 000
rows from user2money table.
I successfully filled user2money with 20M rows using this python script:
size = 200000
count = 100
fp = open("user2money-%i.sql" % (size*count), "w")
fp.write("DELETE FROM `user2money`;\n")
for j in range(count):
min = j*size+1
fp.write("INSERT INTO `user2money` VALUES ")
fp.write("(%i,42,1000,1000,100),(%i,43,1000,1000,1000)" % (min, min))
for i in range(min+1, min+size):
fp.write(",(%i,42,1000,1000,1000),(%i,43,1000,1000,1000)" % (i, i))
fp.write(";\n")
fp.close()
and then
mysql -u root pythonpokernetwork < user2money-20000000.sql
But got the following error when trying to delete using:
delete from user2money where currency_serial = 42;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
I thought it was worth sharing #mysql ebergen comments on this issues:
proppy: is there a way to get around this error ?
proppy: ERROR 1206 (HY000): The total number of locks exceeds the lock
table size
proppy: I'm trying to delete 20M row from a table
ebergen: an innodb table?
proppy: ebergen: yep
proppy: trying to set innodb_buffer_pool_size in my.cnf
ebergen: how many rows are in the table?
proppy: ebergen: 40 000 000
proppy: ebergen: I want to delete 20 000 000 rows
ebergen: hm
ebergen: you have basically two choices
ebergen: 1 delete the rows in small transactions
ebergen: 2. create a new table with the rows you want and do an atomic
rename
ebergen: innodb simply wasn't built to delete 20mil rows in a single
transaction
proppy: ebergen: atomic swap you mean ?
proppy: rename would likely fail if the table exist ?
ebergen: proppy rename table a to b, b to a;
ebergen: err
ebergen: a to b, c to a
proppy: ebergen: what if there are request to a in the between ?
ebergen: rename table locks all the tables before renaming them
ebergen: that's why it's an atomic rename
proppy: oh you mean renaming the both table is atomic ?
ebergen: there is an example at the top of:
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
proppy: ebergen: thanks a lot
I also filled a bug against mysql documentation here:
http://bugs.mysql.com/bug.php?id=40534
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkkR6JsACgkQZmEdV9SHoe7eXgCfRQ2cSwym+w9lAKGDsiAHwqzl
2SUAn0fYCwVJkBGKERMHooAs/VSL2woB
=YeFA
-----END PGP SIGNATURE-----
_______________________________________________
Pokersource-users mailing list
[email protected]
https://mail.gna.org/listinfo/pokersource-users
