4 ms·
This appears to me as a ridiculously good idea. I've no clue whether it's really novel, but to me it is. Anyone got a clue about the implications? Also, how di
by skrebbel 15y ago
This appears to me as a ridiculously good idea. I've no clue whether it's really novel, but to me it is.
Anyone got a clue about the implications? Also, how difficult to do? (besides than the author's own impressions) Would it be possible make a more generic solution?
- fleitz 15y agoIt's really simple. I did it back in 2001, when I did it I used XML and a stored proc to read the XML and write directly to a cursor. It probably takes an hour to do it the first time and about 15 to 20 minutes once you're used to the idea of it. (on SQL Server 2000) With something like SQL Server 2005 it's super simple (< 5 mins) you can basically write an XML file as a stream to your DB server and it eats it and writes directly to the table. (SQL Server supports transforming XML into a table using XPath). You can basically treat the connection like a filehandle and dump XML directly to the stream. Just make sure you open/close the connection often enough not to cause the lock optimizer to grab a table lock. (Or just grab a table lock if you really want it to scream) All you need to ensure is that you don't hold the steam open too long. If you don't mind a little logic in your DB server you can even consume complex object graphs. If you go the XML route you can max the CPU on your DB server which is generally a bad thing for the most part on modern systems it won't matter unless your DB server has 200+ disks. You can use this type of system to stream 200 or 300 MB worth of data to your server in a few seconds. This assumes your DB server isn't a piece of crap. (If it can't keep up with a gig ethernet card on random IO or is virtualized it's most likely a piece of crap). The more generic version is to take advantage of the INSERT syntax and use multiples sets of values. eg. INSERT INTO foo (id) VALUES (1),(2),(3); instead of INSERT INTO foo (id) VALUES (1); INSERT INTO foo (id) VALUES (2); INSERT INTO foo (id) VALUES (3); Now because MySQL is one of the most retarded (I mean that in the literal sense of the word) databases in the world it doesn't support prepared statements so you get huge overhead when you issue SQL to it, compared to a prepared statement. Also, make sure you explicitly start a transaction before you issue a bunch of inserts, else each statement becomes its own transaction. Also on the readside to increase performance use something like MARS and issue multiple SELECT statements. eg. SELECT * from parent where id = 5; SELECT * from child where parent_id = 5; If you have to do more than a few levels consider a SHAPE query. Or use map reduce and 'fold' your join back into something sane.
- dkoch 15y agoMySQL has had server-side prepared statements since 5.0.
- SomeOtherGuy 15y agoYou forgot the asterisk and the list of limitations, bugs and "features" that are like bugs in that they cause problems, only were done intentionally.
- julochrobak 15y agoWhat do you find so rediculously good about this? I find it complicated. If I understood it properly, it's just making inserts asynchrounous (removing durability) and batches data into single statement (removing atomicity). Or did I miss something?
- willvarfar 15y agoIts still ACID. Its the same callee code as you use in, for example, twisted already to have DB async. It just gets way better performance because the DB speed is measured in requests/sec and not how big those requests are. So merging many requests into a single one where possible means you get more throughput.
- marshray 15y agoBut it's not ACID. For example, if the server or process running his database worker queue were to crash, records would be lost. Of course, it's quite likely an acceptable trade-off for for these records. Relational DBs sold everyone on ACID in the 90's, but a lot of data doesn't really deserve its cost. He hasn't found a way around Brewer's Conjecture (and I wish I had a nickel for every time it's rediscovered :-).
- willvarfar 15y agoBut then the callback to the request would timeout and the callee code waiting on the web request would be in no misunderstanding that the thing was committed or not. Things in the queue get acknowledged - and sometimes resultsets or IDs returned, in the case of SELECT and INSERTs that return auto keys.
- marshray 15y agoOK, I see. So your batching related inserts to cut down on the per-transaction overhead, but not actually returning anything to the client until the commit is confirmed. I think this is a good method, as long as you're careful about these things. (I assume you've taken care of them, just talking about this method in general). 1) If there were multiple transactions in the web request, previously the second ones likely wouldn't have been run if the first were to fail. This would likely change that and it could even necessitate having rollback for the second transaction. 2) Now there is a potential "thundering herd" problem. When the transaction batch commit notification is received, all 1000 web requests made over that 100 ms period become ready to execute and start competing for network/disk/database resources simultaneously. A disk that was just sitting idle now has 1000 different things to do at once. If everybody hits the network at once it can cause temporary packet loss. When packets are lost, connections can be dropped or delayed by retransmits. Sometimes client retries and retransmits can happen synchronously. In the worst case, such a system can end up oscillating wildly between excessive load and excessive idle.