We're playing with the allowMultiQueries feature in Connector-J 3.1.7 which allows you to run multiple queries in one executeStatement... this can reduce the time for 1000 queries from 1000ms down to about 1ms which for batch updates it MUCH faster. (its also faster than PreparedStatement batch updates by about 1000x as each stmt in the batch takes 1ms).
In my tests I ran these against a single master but it looks like these will break replication. One of our engineers was playing with using this on our master and the query immediately broke all of our slaves:
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1061 WHERE USER_FEED.USE' at line 1' on query. Default database: 'ksa'. Query: 'UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 5239 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61025 AND USER_FEED.SUBJECT_ID >= 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1061 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61026 AND USER_FEED.SUBJECT_ID >= 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 4255 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61027 AND USER_FEED.SUBJECT_ID >= 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 400 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61028 AND USER_FEED.SUBJECT_ID >= 0 ; UPDATE USER_FEED SET USER_FEED.UNREAD_ARTICLE_COUNT = 1007 WHERE USER_FEED.USER_ID = 79 AND USER_FEED.FEED_ID = 61261 AND USER_FEED.SUBJECT_ID >=
Skip_Counter: 0
He ran the statement once with about 200 UPDATEs in the multiquery. It seems like the master wrote these as 200 FULL statements (not split into individual statements) so when the slave replayed the transaction it broke. The issue seems to be that while the SQL is executed correctly its not written to the binary log correctly and thus breaks all the masters.
Any thoughts here?
Obviously we can't move to allowMultiQueries since this seems like a fatal bug.
Thanks!
Kevin
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod!
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
