0

I'm trying to deal with many connections and sometime I get following error:

ERROR : Lock wait timeout exceeded; try restarting transaction

The bigger number of queries the least number of connections working.

It's happening with all queries (INSERT, UPDATE, SELECT). For example, simple query:

INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP).

Here are some results:

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=2 --number-of-queries=1000 --iterations=5 --delimiter=";" 
Enter password: 
Benchmark
    Average number of seconds to run all queries: 0.804 seconds
    Minimum number of seconds to run all queries: 0.793 seconds
    Maximum number of seconds to run all queries: 0.843 seconds
    Number of clients running queries: 2
    Average number of queries per client: 500



[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=50 --number-of-queries=1000 --iterations=5 --delimiter=";" 
Enter password: 
Benchmark
    Average number of seconds to run all queries: 0.837 seconds
    Minimum number of seconds to run all queries: 0.795 seconds
    Maximum number of seconds to run all queries: 0.884 seconds
    Number of clients running queries: 50
    Average number of queries per client: 20



[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=4 --number-of-queries=10000 --iterations=5 --delimiter=";" 
Enter password: 
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction


[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=2 --number-of-queries=5000 --iterations=5 --delimiter=";" 
Enter password: 
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=1 --number-of-queries=10000 --iterations=5 --delimiter=";" 
Enter password: 
Benchmark
    Average number of seconds to run all queries: 7.639 seconds
    Minimum number of seconds to run all queries: 7.237 seconds
    Maximum number of seconds to run all queries: 7.903 seconds
    Number of clients running queries: 1
    Average number of queries per client: 10000


So, what the matter? As you can see I can work with only one thread when trying to deal with high number of queries (but, for some reason, the speed remains the same, not as in usual MySQL). How can I fix it?

UPD: I didn't use real names in examples, but i have asked other people from work, they say I can use. So, here is actual table structure:

 t_likes | CREATE TABLE `t_likes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `like_to` int(11) unsigned NOT NULL,
  `like_to_type` int(11) unsigned NOT NULL,
  `like_from` int(11) unsigned NOT NULL,
  `like_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `like_to_like_to_type_like_from` (`like_to`,`like_to_type`,`like_from`),
  KEY `like_from` (`like_from`),
  KEY `like_to_type` (`like_to_type`),
 CONSTRAINT `t_likes_ibfk_2` FOREIGN KEY(`like_to_type`) REFERENCES `t_objects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `t_likes_ibfk_1` FOREIGN KEY(`like_from`) REFERENCES `t_users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster AUTO_INCREMENT=1375 DEFAULT CHARSET=utf8 
  • Can you show the table structure (SHOW CREATE TABLE)? I have a theory. – jynus Aug 20 '14 at 9:34
  • I have updated question, there is table structure now. – ckorzhik Aug 20 '14 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.