improving SQLite writing performances (WAS: Re: inserts into a sqlite3 database are not delayed)
On Wed, Oct 15, 2014 at 10:56 AM, Balazs Scheidler <bazsi77@gmail.com> wrote:
It immediately runs the query, flush_lines() controls the transaction size, which can be enabled using flags(explicit-commits).
Ok, I see and I can confirm noticeable improvements on a system under heavy I/O and CPU load using explicit-commits (or better: I see it gets a lot worse disabling it, since I already had it on ;)) Plus, I'm seeing minor improvements with session_statements set to "PRAGMA synchronous=OFF; PRAGMA count_changes=OFF;" Beside this, has anyone found other ways to improve writing performances using syslog-ng with sqlite? On (physical) systems with high load, I've noticed that the system is much more responsive if the elevator is set to noop or deadline instead of cfq. But I guess this is highly dependent on the specific circumstances of the system. Thanks! -- Davide Alberani <davide.alberani@gmail.com> [PGP KeyID: 0x465BFD47] http://www.mimante.net/
Well, changing syslog-ng to use the bulk loading API for various databases would probably increase performance a lot. But that's a nontrivial code change, and would probably not work through libdbi. On Oct 27, 2014 5:39 PM, "Davide Alberani" <davide.alberani@gmail.com> wrote:
On Wed, Oct 15, 2014 at 10:56 AM, Balazs Scheidler <bazsi77@gmail.com> wrote:
It immediately runs the query, flush_lines() controls the transaction
size,
which can be enabled using flags(explicit-commits).
Ok, I see and I can confirm noticeable improvements on a system under heavy I/O and CPU load using explicit-commits (or better: I see it gets a lot worse disabling it, since I already had it on ;))
Plus, I'm seeing minor improvements with session_statements set to "PRAGMA synchronous=OFF; PRAGMA count_changes=OFF;"
Beside this, has anyone found other ways to improve writing performances using syslog-ng with sqlite?
On (physical) systems with high load, I've noticed that the system is much more responsive if the elevator is set to noop or deadline instead of cfq. But I guess this is highly dependent on the specific circumstances of the system.
Thanks!
-- Davide Alberani <davide.alberani@gmail.com> [PGP KeyID: 0x465BFD47] http://www.mimante.net/
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.balabit.com/wiki/syslog-ng-faq
participants (2)
-
Balazs Scheidler
-
Davide Alberani