3 ms·
"Backup or restore while a transaction is active" Is it possible to turn off transactions alltogether in SQLite? So they function like a MyIsam table in MySql
by TekMol 5y ago
"Backup or restore while a transaction is active"
Is it possible to turn off transactions alltogether in SQLite? So they function like a MyIsam table in MySql or a Aria table in MariaDB?
I have crunched many billions of queries over the last years in MySql and then MariaDB. I like MariaDB because it offers tables that are way faster due to no transaction overhead.
I know this goes against the popular opinion to use transactional tables for everything. But depending on the task at hand, the performance gain can be very well worth turning transactions off.
I consider trying SQLite. But if you cannot switch off transactions, performance will probably not be up to par.
- dataflow 5y agoAFAIK there's PRAGMA journal_mode = OFF, which may help with performance, but if you want to go beyond that, you may [1] have to modify the code or write your own VFS driver. [1] https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg33057.html https://www.mail-archive.com/sqlite-users@mailinglists.sqlit...
- capableweb 5y agoTo expand on this, journal_mode controls if SQLite should document the transaction or not, not if there should be transactions at all, so you're effectively disabling logging, which indeed should also contribute a bit to better performance.
- hnlmorg 5y agoUnless you are referring to another meaning for the term “transactions” that I’m unaware of, I’ve found running your SQLite queries inside transactions grately increases throughout.
- TekMol 5y agoBatching multiple queries into a single transactions of course greatly increases performance compared to have one transaction per query. But it does not give you the performance you get if there are no transactions in the first place.
- capableweb 5y agoI'm no expert on SQLite, so someone please correct me if I'm wrong here, I'm effectively guessing. I don't there is a way of turning off transactions completely, you can either do it explicitly or implicitly. However, if you do it explicitly (wrapping BEGIN/COMMIT manually), you can batch all your INSERT/SELECT statements inside one transaction instead of one transaction per query. This should make things a bit faster as the lock would only have to be acquired/released once instead of once per operation.
- TekMol 5y agoYes, it makes things faster. But not to the level of performance you can get out of a completely transactionless setup. At least according to my experiments with MyISAM tables vs InnoDB. I never got InnoDB tables to perform as well as MyISAM tables.
- capableweb 5y agoNo, that makes sense. I don't think SQLite was ever built for creating the fastest possible setup, nor one that can have the most concurrent usage, because there are plenty of others projects aiming for those goals, while SQLite goals are different.
- jonathanstrange 5y agoMay I ask whether these databases provide full ACID-compliance? I thought that some form of transactions is always needed internally in order to rollback non-atomic filesystem changes in case of an error. Wouldn't you get an inconsistent database otherwise?
- TekMol 5y agoThats the point. I prefer to go without ACID compliance, becasause it is such a performance hog. How often do you experience "errors" that corrupt your database files? How much performance downgrade would you be willing to take to avoid those?
- zinekeller 5y agoWhat do you mean "transactions"? I agree with others that you may have other definitions of transaction. If you meant "I don't care about corruption, just make it as fast as possible" (this is mainly about about write performance though, since reads are already fast), then there are two PRAGMA commands related to this: PRAGMA synchronous=OFF; PRAGMA journal_mode=OFF; The first one yields saving to the OS instead of SQLite verifying that it has been written properly (https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous). The second one is basically turning off all journal capabilities, trading ACID for maximum performance (https://sqlite.org/pragma.html#pragma_journal_mode https://sqlite.org/pragma.html#pragma_journal_mode).
- trollied 5y agoYou obviously work with or design software that doesn’t care about transactions/ACID. It’s either scary that you’re not aware (read something, assume it is still correct, another concurrent process does the same, free £$ etc), or are ridiculously clever that it doesn’t matter.