Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

35% Faster Than The Filesystem (2017)

sqlite.org

Post reply on HN
Print view View original

121–130 of 166 posts

Re: 35% Faster Than The Filesystem (2017)

#121

Post by kukx »

Const-me wrote: post #73
karmakaze wrote: post #67

Earlier quoted context omitted.

There's the other obvious explanation that because the Win7 machine has slower storage it automatically uses a larger cache than the Win10 machine that more than offsets the difference.

Windows kernel uses all free RAM as a file cache. Low priority one i.e. when an app wants to allocate RAM, the file system cached pages are evicted. Their Win7 test machine has 4GB RAM, Win10 16GB RAM. I think I know what’s going on. They don’t publish absolute numbers, they only publish result compared to SQLight on the same machine. Windows has approximately fixed overhead when opening files. Most of that overhead…

Nitpick, but file permissions in Linux take normally 12 bits. You probably did not count SUID, SGID and sticky bit.

Re: 35% Faster Than The Filesystem (2017)

#122

Post by realshowbiz »

I remember a job interview where the take home project was to parse a log file and search/sort by various top attributes as fast as possible.

I thought about writing something from scratch, but I knew that whatever I could optimize in an hour or two would be nothing compared to more robust tools that already existed. So I used sqlite to ingest the log once, and query it in various ways to produce the requested reports. The script was just a dozen lines long or so, with one dependency (sqlite pkg).

The interviewer didn’t like this answer. didn’t say why. But I’m glad to see that I wasn’t way off base about how performant sqlite can be.

My answer was of course not a prod solution, just an answer to a question with a narrow scope.

Re: 35% Faster Than The Filesystem (2017)

#123

Post by pdimitar »

SQLite wrote: post #113
muststopmyths wrote: post #104

stat to get filesize is a pathlogically bad pattern on Windows. If I remove the call to fileSize() in kvtest and instead replace it with 16k (since the max size in the post is 12k), I can significantly improve the time measured on Windows 10. Before: C:\temp>kvtest run test1.dir --count 100k --blob-api --count 100000 --max-id 1000 --asc Total elapsed time: 9.495 Microseconds per BLOB read: 94.950 Content read rate: 1…

Thanks for this. I did not realize that fileSize() was a performance issue on Windows. I'll attempt to update kvtest.c to deal with that, rerun the tests, and update the page, as I have opportunity.

Apologies for a hijack but since you're here, I really wanted to say thank you for sqlite! Saved me many headaches and made me a better programmer. You're amazing for creating it.

And if you don't mind a question: are you guys up for making a strictly typed variant of sqlite? I'm aware it is going to be a completely different beast and many wouldn't expect backwards compatibility.

Just curious if such a strongly-typed variant is on your radar at all.

Re: 35% Faster Than The Filesystem (2017)

#124

Post by otterley »

realshowbiz wrote: post #122

I remember a job interview where the take home project was to parse a log file and search/sort by various top attributes as fast as possible. I thought about writing something from scratch, but I knew that whatever I could optimize in an hour or two would be nothing compared to more robust tools that already existed. So I used sqlite to ingest the log once, and query it in various ways to produce the requested report…

The interviewer didn't like the answer because it didn't answer the implicit question, which is how well you can write your own software. We know that you can load a table into SQLite and have it do the heavy lifting for you. But we're often not looking for software _users_ as much as we're looking for _engineers_.

When we interview people, the answers we want often aren't real-world answers because we're trying to get a deeper understanding of how you think or how well you know the details of a complex mechanism.

Re: 35% Faster Than The Filesystem (2017)

#125

Post by clairity »

realshowbiz wrote: post #122

I remember a job interview where the take home project was to parse a log file and search/sort by various top attributes as fast as possible. I thought about writing something from scratch, but I knew that whatever I could optimize in an hour or two would be nothing compared to more robust tools that already existed. So I used sqlite to ingest the log once, and query it in various ways to produce the requested report…

that seems to be a great asnwer to the question as posed, and as would be faced in real-world situations rather than academic ones.

Re: 35% Faster Than The Filesystem (2017)

#126

Post by notyourday »

otterley wrote: post #124
realshowbiz wrote: post #122

I remember a job interview where the take home project was to parse a log file and search/sort by various top attributes as fast as possible. I thought about writing something from scratch, but I knew that whatever I could optimize in an hour or two would be nothing compared to more robust tools that already existed. So I used sqlite to ingest the log once, and query it in various ways to produce the requested report…

The interviewer didn't like the answer because it didn't answer the implicit question, which is how well you can write your own software. We know that you can load a table into SQLite and have it do the heavy lifting for you. But we're often not looking for software _users_ as much as we're looking for _engineers_. When we interview people, the answers we want often aren't real-world answers because we're trying to g…

> But we're often not looking for software _users_ as much as we're looking for _engineers_.

What the applicant illustrated is that he is a much better engineer than the interviewer and the interviewer did not like it.

Good software engineering is about leveraging existing robust tools in a new way, not about re-inventing a wheel while making is square.

For me that would have been an insta-hire.

Re: 35% Faster Than The Filesystem (2017)

#127

Post by pdimitar »

pedrocr wrote: post #96
bob1029 wrote: post #91

Earlier quoted context omitted.

I would recommend giving it a try regardless. This is the exact approach we use today and we are seeing zero issues. I am not sure why you think you will see failed transactions. It is a perfectly legitimate/recommended approach to create a new connection per logical transaction.

If the locking is moved to the database concurrent transactions can happen. If those touch the same rows one of them will have to fail to maintain consistency. The bottleneck wasn't there though. Even a single thread doing INSERTs is more than enough to slow down the database. You probably don't have a write-heavy workload.

Well, sqlite is not recommended for write-heavy workflows anyway.

I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking.

If you do however have a write-heavy workflow then it's likely time to replace sqlite.

Re: 35% Faster Than The Filesystem (2017)

#128

Post by otterley »

notyourday wrote: post #126
otterley wrote: post #124

Earlier quoted context omitted.

The interviewer didn't like the answer because it didn't answer the implicit question, which is how well you can write your own software. We know that you can load a table into SQLite and have it do the heavy lifting for you. But we're often not looking for software _users_ as much as we're looking for _engineers_. When we interview people, the answers we want often aren't real-world answers because we're trying to g…

> But we're often not looking for software _users_ as much as we're looking for _engineers_. What the applicant illustrated is that he is a much better engineer than the interviewer and the interviewer did not like it. Good software engineering is about leveraging existing robust tools in a new way, not about re-inventing a wheel while making is square. For me that would have been an insta-hire.

I both agree and disagree with you.

I agree that the answer was a good one in the real-world sense, and one that I would like an engineer to use under the right circumstances.

I disagree that the answer demonstrated engineering prowess, however. Sometimes you have to assume the lack of existence of certain tools to understand how a candidate would have implemented the solution him- or herself to get a more thorough understanding of his or her engineering chops.

Re: 35% Faster Than The Filesystem (2017)

#129

Post by pedrocr »

pdimitar wrote: post #127
pedrocr wrote: post #96

Earlier quoted context omitted.

If the locking is moved to the database concurrent transactions can happen. If those touch the same rows one of them will have to fail to maintain consistency. The bottleneck wasn't there though. Even a single thread doing INSERTs is more than enough to slow down the database. You probably don't have a write-heavy workload.

Well, sqlite is not recommended for write-heavy workflows anyway. I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking. If you do however have a write-heavy workflow then it's likely time to replace sqlite.

>I have the same impressions as your parent commenter: passing around an sqlite connection handle is thread-safe and always will be faster than application-level locking.

I'm not debating that. It's just that that's not the current bottleneck so no point in dealing with the extra complexity. The current solution can saturate my SSD with low CPU usage.

>If you do however have a write-heavy workflow then it's likely time to replace sqlite.

That's definitely true. But there's no other SQL database to embed that I know of. That's why I mentioned wanting postgres as a lib. I know of other options that are non-SQL and will require extra rework. If there's an SQL option I'm all ears.

Re: 35% Faster Than The Filesystem (2017)

#130

Post by saltcured »

zzzcpan wrote: post #78
Spivak wrote: post #77

Earlier quoted context omitted.

Doesn't that introduce the desync problem again? Is there something that can enforce consistency across the two databases?

Backups are fundamentally limited to eventual consistent, there is no need for databases to be synchronously replicated for backups. I mean splitting database has no effect on backup consistency, although a more decent way of dealing with it is not splitting database, but simply running an async replica to do backups from.

Are you are talking past each other...? I suspect the grandparent post is concerned about the loss of ACID properties for updating data and metadata if they are split across two databases. This is a concern for regular application access too. Meanwhile, you assume that this consistency problem is already solved in the application, and so of course the backup and restoration problem can also then be handled.

To the grandparent: you typically solve this application consistency issue by using something like immutable object store semantics with versioned references. The object store is capable of answering requests for multiple versions of the asset, and the application metadata store tracks individual versions. You can sequence the order in which you commit to these stores, so the asset is always available before it is published in the metadata store. Alternatively, you can make consumers aware of temporary unavailability, so they can wait for the asset to become available even if they stumble on the new version reference before the content is committed.

You can also find hybrids where the metadata store is used to track the asset lifecycle, exposing previous and next asset version references in a sort of two-phase commit protocol at the application level. This can allow consuming applications to choose whether they wait for the latest or move forward with older data. It also makes it easier to handle failure/recovery procedures such as canceling an update that is taking too long and reclaiming resources.

Post reply on HN