Skip to content

SQLiteJournal: performance improvements #34

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
sallyx wants to merge 1 commit into from
Closed

SQLiteJournal: performance improvements #34

sallyx wants to merge 1 commit into from

Conversation

@sallyx
Copy link
Contributor

@sallyx sallyx commented Oct 24, 2015

SQLiteJournal: performance improvements

@sallyx sallyx force-pushed the optimize-sqlitejournal branch from e32571a to 4e8833f Compare October 24, 2015 09:29
milo
milo reviewed Oct 24, 2015
View reviewed changes
src/Caching/Storages/SQLiteJournal.php
Copy link
Member

@milo milo Oct 24, 2015

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good point when there are no foreign keys.

@milo
Copy link
Member

milo commented Oct 24, 2015

Could you change commit message to be consistent and add test to priority bugfix?

@sallyx
Copy link
Contributor Author

sallyx commented Oct 24, 2015

I have updated the test file already.

@Majkl578
Copy link
Contributor

Majkl578 commented Oct 25, 2015

fixed bug - keys with multiple priority

This should be in separate PR (or commit at least).

@sallyx
Optimized SQLiteJournal
cd0583a 
disabled foreign keys check
removed unnecessary indices
enabled journal_mode WAL
@sallyx sallyx force-pushed the optimize-sqlitejournal branch from 4e8833f to cd0583a Compare October 28, 2015 09:48
@sallyx
Copy link
Contributor Author

sallyx commented Oct 28, 2015

I have created a new PR #35 for the bug fix.

@milo
Copy link
Member

milo commented Oct 30, 2015

With following synthetic benchmark:

use Nette\Caching\Cache;
use Nette\Caching\Storages\SQLiteJournal;

require __DIR__ . '/../caching/vendor/autoload.php';

$keys = $tags = $dels = [];
foreach (range(1, 200) as $i) {
    $keys[] = "key-$i";
    $tags[] = "tag-$i";
    if (($i % 4) === 0) $dels[] = "tag-$i";
}

$journal = new SQLiteJournal(__DIR__ . '/bench.s3db');

foreach (range(1, 10) as $loop) {
    # One key, multiple tags
    $journal->write('key', [Cache::TAGS => $tags]);

    # n-keys, one tag
    foreach ($keys as $key) {
        $journal->write($key, [Cache::TAGS => ['tag']]);
    }

    # Delete some of tags
    $journal->clean([Cache::TAGS => $dels]);
}

Apache sequential bench:

$ rm -f bench.s3db
$ ab -c 1 -n 10 http://localhost/dev/nette/caching.tests/bench.php

# Before patch
Connect:        0    0   0.0      0       0
Processing:  3906 5036 836.1   5288    6365
Waiting:     3906 5036 836.1   5288    6365
Total:       3906 5036 836.1   5288    6365

# With patch
Connect:        0    0   0.0      0       0
Processing:   686  783 149.3    704    1145
Waiting:      686  783 149.3    704    1145
Total:        686  783 149.3    704    1145

Apache parallel bench:

$ rm -f bench.s3db
$ ab -c 8 -n 10 http://localhost/dev/nette/caching.tests/bench.php

# Before patch
Connect:        0    0   0.1      0       0
Processing:  6567 36465 16157.5  45939   51092
Waiting:     6567 36465 16157.5  45939   51092
Total:       6567 36465 16157.6  45939   51092

# With patch
Connect:        0    0   0.1      0       0
Processing:  2739 4437 1425.3   4443    6845
Waiting:     2739 4437 1425.3   4443    6845
Total:       2740 4437 1425.3   4444    6845

So 👍 for merge.

@milo
Copy link
Member

milo commented Oct 30, 2015

@sallyx Could you change the commit message to something like: SQLiteJournal: performance improvements

@sallyx sallyx changed the title Optimized SQLiteJournal SQLiteJournal: performance improvements Oct 30, 2015
@dg
Copy link
Member

dg commented Nov 4, 2015

@sallyx not title but commit message

@dg dg closed this in 96e1d58 Nov 29, 2015
dg pushed a commit that referenced this pull request Nov 29, 2015
@sallyx @dg
SQLiteJournal: optimizations [Closes #34]
f60c176 
disabled foreign keys check
removed unnecessary indices
enabled journal_mode WAL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

4 participants

@sallyx @milo @Majkl578 @dg