On þri 5.júl 2016 23:49, Andrey Stepnov wrote: Hi Palli!
Thanks for reply. Here is code with transactions example: || |mysql_query(con, "SET autocommit = 0") Generally you do not have to change this, rely on the default setting of the database(?). mysql_query(con, "START TRANSACTION;") Yes, non-standard in MySQL.. One of the reasons to use some beginTransaction(). mysql_query(con, "SET autocommit = 1") [And you would then not change again. When your connection closes, setting are usually lost. I'm sure there are other ways to change globally for all users.] > Then do your INSERTs (prepared statments can be better here for security but not speed, while can also help for speed in queries), e.g in a loop. Yes, you right! It works with small data but if I have: And "works" with big data.. while it's extremely slow. at least in MySQL (yes, also 4x faster in one statement in PostgreSQL, I see from the link I provide below. I've only used COPY, never equivalent(?) multi-INSERT that is actually there I see, and COPY only outside of the application). Solution: string with non-safe big-insert: [It can also be made safe, look into escape functions. They work, just more brittle to work with, as you have to take care for each instruction. That is why prepared statements are better (and some abstraction layers auto-prepare for you) and can be also faster in other cases.] That confirms this isn't a Julia issue. I'm not an expert on MySQL. I'll go into little more detail, but this belongs in some MySQL (or generic db) forum. I'm glad you're using Julia, less glad that you're not using PostgreSQL. That might also have helped, as it is a superior database (MySQL had a few things going for it against PostgreSQL, none I think any more, it had clustering but not the other, maybe theirs (or really from a third party) is better for special cases of clustering (or not)). All databases will be slow if they need to commit each INSERT (or say UPDATE) to stable storage (disk, less slowdown for SSDs..). You could monitor if that is happening (with strace, in Linux/Unix), maybe by slowing down your loop. All databases will be faster if you really manage to commit more rows at a time, in a transaction (each instruction at least is one), but they probably also have non-standard ways. PostgreSQL has COPY (similar to MySQL's multi-INSERT, neither are standardized, so it doesn't need their way, except for compatibility for MySQL code https://kaiv.wordpress.com/2007/07/19/faster-insert-for-multiple-rows/ ) as it's faster than the non-default that pg_dump exports (for compatibility with other databases; you can fairly easily migrate to and from it). [Informix db has non-standard dbexport and dbimport commands, then also HPL, high-performance loader, that I never used..] or even https://dev.mysql.com/doc/refman/5.5/en/load-data.html "LOAD DATA [LOW_PRIORITY | CONCURRENT]" might be more similar to COPY. https://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html "INSERT DELAYED ... The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete." Might also be what you're after. Possibly, you need to set isolations level(?), if other users are seeing your INSERTs trickle in, it's a clue that they are also going to disk. They should see them all or none, if transactions are working. MySQL, was really bad, while you could declare FOREIGN KEYs (and probably open transactions), they where just ignored! It might still be happening. Not sure what the default is. Transactions and foreign keys etc. are only defined in the sane engines, e.g. InnoDB. I didn't follow what came later, those probably are sane also. -- Palli.
