SQLite 3.8.7 is 50% faster than 3.7.17

http://permalink.gmane.org/gmane.comp.db.sqlite.general/90549

by anon1385 • 12 years ago

579 164 12 years ago

permalink.gmane.org

easytiger4• 12 years ago

I'm on 3.8.6. So will have most of the benefits. The temptation with sqlite is that it is so well maintained to only do cursory testing with it, so might drop this in.

Interestingly:

A full 10% of the performance gain has come since the previous release. There have been a lot of changes.

Shish2k• 12 years ago

I wonder what most of those optimisations were? In particular, are there any that are general enough to be worth knowing about, or even worth including at the compiler level?

easytiger4• 12 years ago

Everthing detailed:

http://www.sqlite.org/changes.html

rlpb• 12 years ago

How is this work funded? Are these developers working full time on SQLite, or is this work done in developers' spare time?

cowsandmilk2• 12 years ago

not going to say it is 100% of their time, but they are paid to work on it.

You can see five consortium members on the front page of sqlite, the cost of a consortium membership appears to $75,000/year[1]. That's enough to pay developers.

[1] http://sqlite.org/consortium_agreement-20071201.html

ghshephard• 12 years ago

And that doesn't even include Apple, who I have to believe has a big investment in sqlite, and probably has some form of support arrangement with them.

yincrash• 12 years ago

The full price list. [1]

[1] http://www.hwaci.com/sw/sqlite/prosupport.html

dagw• 12 years ago

The main SQLite developers have a company that has a few revenue streams. Mainly they offers support and maintenance contracts. There are also a couple of closed source extensions for SQLite for handling compressed and encrypted databases that they sell. Finally the complete test suite is closed source, so if you want to make closed source changes to SQLite and still make sure you don't break compatibility then you probably want to license that.

qwerta3• 12 years ago

I think people under-estimate incremental improvements a lot. My tai-chi teacher told me that eastern culture is more about "putting one sheet of paper on another", there is slow and steady improvement. Western is much more about "big bang approach", make it or break it.

SQLite has been around for 15 years, they never shifted their purpose and were constantly improving. You get almost perfect product at this point.

I work on in-memory db which was evolving similar way. Now after more than decade of small improvements, it is faster than java collections, despite doing serialization and its own memory management.

DanBC• 12 years ago

The business jargon word for this is "kaizen" - process of improvement through gradual change.

http://en.m.wikipedia.org/wiki/Kaizen

Too• 12 years ago

I think you misspelled buzzword.

italophil• 12 years ago

Not only eastern cultures have the concept. In German there is the phrase 'a steady water drop hollows a stone' and it actually traces back to the Roman author Ovid 'gutta cavat lapidem'. Perseverance is just so much harder than instant gratification.

bane2• 12 years ago

Just to show Yanks have it too, "slow and steady wins the race".

twsted• 12 years ago

Gutta cavat lapidem (Ovidium)

johnydepp• 12 years ago

In india the phrase is: "Continuous movement of rope can make mark on stone, like it makes on the wall of a well".

HINDI: "Rasari aavat javat re, sil par parat nishaan"

hyh1048576• 12 years ago

Interesting. The same phrase 'a steady water drop hollows a stone' also exists in Chinese: 水滴石穿, and it also traces back to 2000 years old classics.

SaulOfTheJungle2• 12 years ago

In (Brazilian) Portuguese: "Água mole em pedra dura, tanto bate até que fura".

thomasahle• 12 years ago

I'm not sure I understand the tradeoff. Given two tasks requiring an equal amount of work, are you saying that it's better to spend it on a 5% improvement than a 150% improvement? Sure, if all you have available are lots of 5% improvements, they'll also eventually add up. But won't you prefer to work on the biggest improvement/cost tasks first?

Perhaps it should be seen versus the "throw it all away and start over" tendency? Like in steady refactors vs. big rewrites.

regularfry3• 12 years ago

Here's one way to look at it.

Say you can either spend X amount of effort on a 50% improvement, or you can spend X/10 on each of 10 5% improvements. Sounds the same, right?

It's not. If the smaller increases compound, you get a (1.05^10=) 63% increase out of the latter situation. Plus you don't have to wait for everything to be done to get visible improvements.

qwerta3• 12 years ago

Imagine you like something and have 40 hours implement it. Now spread those hours over one week, or over several months. Second cas will be much better because people will contribute ideas, you spend some time thinking about this task outside your job and so on. There is some overhead with constantly restarting your work, but that is negleable with good methodology.

lazyant• 12 years ago

We underestimate the long-term improvements and overestimate the short-term improvements, not sure who said it first.

jules• 12 years ago

And yet, a big bang approach with a better data representation and code generation for queries can yield a 10x improvement http://infoscience.epfl.ch/record/198693/files/801-klonatos....

emiljbs3• 12 years ago

What does putting one sheet of paper on another give you? Death by a thousand paper cuts.*

* Yeah, this is a pretty bad post, I'm sorry.

erikb4• 12 years ago

What you say is called Kaizen. I practice software development, computer gaming, and several other things on these principles, and it really works. Always focus on something small, don't worry about how long it takes or how well you are doing, and when you feel it's internalized go to the next step.

alecco• 12 years ago

On the other hand, this one looks quite big bang for me. From a major version to the next 50%.

Also, natural selection tends to have periods of fast adaptation and periods of plateau quasi-equilibrium.

I like Tai-chi and all, but I'm still a skeptic :)

TheLoneWolfling2• 12 years ago

And this is one of the problems with "optimize later".

If you have a large project in Python, for example, far too often there isn't any one spot that has massive gains if you translate it to C. It's the death of a thousand cuts.

easytiger4• 12 years ago

What are you talking about?

to3m• 12 years ago

(At a guess...)

It's related, I think, to the standard piece of received wisdom, that you shouldn't optimize until some unspecified "later". This isn't a bad rule of thumb, but efficiency and convenience aren't always happy bedfellows, and, when coding, people tend to choose convenience. The result can be that when this later date arrives, and you go to make the code run more quickly, you're a bit stuck, because it would require changes more sweeping than can be accommodated with the resources available. But had performance been taken into account from the start, the changes would have been easy to make, or the whole thing might not even have been an issue.

An example might be writing a compute-heavy program in python rather than C.

Another standard piece of received wisdom is that once you get to optimize later, you'll just run the profiler and there'll be some hot spot due to poor choice of algorithm. So you optimize that bit and then you're done. But in practice this happens quite rarely; it's more common for the code to just be generally slow. A common reason it's generally slow is because performance wasn't considered during development, so the end result is the same.

This is what the reference to translating into C relates to, I suspect - the idea that in your slow python program there's this one bit that can be turned into C and suddenly your program is super fast.

(Of course, for many programs, python's speed is irrelevant. And any compute-heavy python program is likely only to be improved by translation into C - though your average python program would probably be much harder to make any quicker. Maybe that just goes to show that you actually have to think about this stuff on a case-by-case basis rather than relying on received wisdom. Anyway, I pick python only because it was mentioned.)

Belief in the improve-algorithm-later theory usually causes people to dismiss micro-optimizations as pointless, because rather than get 0.1% here and 0.1% there you can just put it off until later and then get 25% all in one go. But this attitude actually makes it more likely that the code will paint itself into a corner, leaving micro-optimizations the only option.

There isn't really any good quick easy solution to any of this, which is why I always liked it when computers got faster every year :(

ben0x539• 12 years ago

Optimizing later, after three major releases and a ton of minor ones, and after getting deployed basically everywhere, seems to be working out pretty well for them though?

TheLoneWolfling2• 12 years ago

Only because it is designed in such a way to allow for it.

The problem is architectural issues, which are far too often glossed over.

mikeash• 12 years ago

You will still typically achieve a better result (take less time building the software, produce software that works better and runs faster) if you "optimize later" than if you try to make everything fast from day one.

sauere• 12 years ago

Python might not be the fastest player on the block... but the increase in coding productivity will more than pay for extra equiptment to handle and speed-shortcomings.

ramidarigaz• 12 years ago

Except when it doesn't. I'm facing this problem with an embedded-ish project I'm working on where Python initially seemed like a good choice, but now we're regretting it for both performance and power usage reasons. If we'd gone with C or C++ initially we'd be having a much easier time optimizing.

Not that I'm implying Python is bad, but if your target use case doesn't allow for a desktop or server, think carefully about what tech you use.

pjmlp• 12 years ago

Also achievable in JVM, .NET, ML family, Lisp family, Julia, D, Rust, Dylan with the added benefit of performance.

I can't really envision using it for anything other than scripting.

sitkack4• 12 years ago

If the application is properly decomposed into distinct subsystems one could take an entire subsystem and move it into C/Rust or Julia. Esp so if those subsystems are functionally pure.

bsaul• 12 years ago

How long will we have to wait to get this version usable for our CoreData apps in ios ? Is it possible to include it manually into our app or are we dependent on ios releases ?

michaelmior• 12 years ago

If you want to use it with CoreData, you might have to wait. But you can always use sqlite directly and link with the latest library. It might well be possible to specify a different library version you can use with CoreData, but I've never tried this.

ponytech• 12 years ago

By "50% faster" what they actually mean is this new version requires 50% less CPU cycles than the former for doing the same task. In real world I think you will not get your program to run "50% faster": disk io is often the bottleneck in the database area.

qwerta3• 12 years ago

SQLIte mostly runs on slow CPU devices with relative fast SSDs. Reads are parallel, but writes are single threaded, so there is no advantage from multi core ARM CPUs.

bane2• 12 years ago

The in-memory mode is also the default for many APIs. You can basically just use it like a collection for your language of choice but use SQL for putting and fetching things from the collection.

onli• 12 years ago

Sqlite mostly runs everywhere, especially on normal desktop PCs, including all those old ones with slow HDDs and relatively powerful CPUs.

But I don't get the ARM CPU remark, did parent change his comment?

loudmax• 12 years ago

Not for the same database obviously, but SQLite databases are cheap. If your data can be split into different databases, you can pull off parallel writes.

eterm• 12 years ago

Percentages are multiplicative:

50% fewer cycles for the same work would be 100% faster.

50% more work for the same cycles would be 33% fewer CPU cycles for the same work.

qznc• 12 years ago

50% fewer cycles might also mean, not faster at all, but your CPU stalls/idles more.

dtech• 12 years ago

True, but as SQLite is heavily used in mobile applications it still has the advantage of consumes less CPU and thus less battery.

bildung2• 12 years ago

disk io is often the bottleneck in the database area.

SQLite usually gets used in situations where the data easily fits into memory (multiple times). It isn't really postgres ;)

ak39• 12 years ago

Right. But by no fault of Postgres either because disk-based io offers durability and in-memory classically does not. Apples and oranges.

In fact all the recent discussions of "performance" for RDBMSs has to be understood in terms of "what's being sacrificed for one or more aspects of ACID?". RDBMS technology is incredibly mature and optimisations that keep the ACID aspects intact are also incredibly difficult now. Marginal returns.

syncsynchalt• 12 years ago

Strictly speaking they only claim 10% improvement from the previous release, the 50% improvement is from a release 16 months ago.

zamalek4• 12 years ago

I'm glad to see something like this. People often take the typical optimization mantra too far. The minute you mention optimization on most internet forums (especially StackOverflow) you get slammed for promoting "premature optimization."

That's not to say premature optimization doesn't exist. Sometimes writing faster code is the difference between using a hash instead of a list. Sometimes it's spending an entire day optimizing a function that's called once. The former is performance aware, the later is premature. It seems as though most would have you believe that they both fall into the "premature" bucket.

It's as though people are using it as an excuse for their personal ignorance and would rather continue to revel in this ignorance than expand what they know. As far as I am concerned these "unimportant tidbits" about performance are gifts that keep giving.

/somewhat offtopic rant

Retric• 12 years ago

There are few absolutes in coding. Some times optimizing a function that's called once is worth it. People often perceive software that takes a long time to load is slow even if everything else is fast. Though even just changing the loading screen can do a lot even if the actual time something takes to load does not change.

PS: If all someone wants to do is rotate an image loading photoshop really is slow.

grokys• 12 years ago

Yes, but it's unlikely that someone asking "is it faster to concatenate strings in C# using the + operator or StringBuilder" on StackOverflow is someone that is writing a low-level database engine like SQLite. In my (totally anecdotal) experience the people who are more concerned about performance are beginners, because that is something that they can grasp, and this is where the "don't prematurely optimize" people are coming from. Obviously someone who is writing a low-level database engine knows that performance matters, and likewise, is likely to be running benchmarks rather than asking vague questions on SO.

zamalek4• 12 years ago

Indeed. However, keep in mind that as this beginner "ramps up" to the knowledge level of someone who is working on a SQL database he will need a source to find out where costs lie. He's done some profiling, finds an expensive method with massive amounts of string concats, heads to Google and all he can find is "don't prematurely optimize."

You can supplement an answer with "don't prematurely optimize," but the presumption that premature optimization is occurring is a premature assumption.

He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever. ~ Chinese Proverb

I would work with beginners that are not fools, than experts who are.

bunderbunder3• 12 years ago

And that's decent advice in response to the "is it faster to concatenate strings in C# using the + operator or StringBuilder" questions. But I've also asked Stack Overflow questions that drowned under a tsunami of premature optimization tut-tutting from people who I suspect didn't even fully understand the question, and then sighed and proceeded to take a couple days to figure it out myself and improve application performance in a critical section by 50%. Which is fine, but once upon a time I thought Stack Overflow might be a resource where people might occasionally save me a bunch of time by saying, "Hey, don't waste your time, I've been there and it's a blind alley."

I think part of what's going on there is that SO's gamification can create some kind of perverse incentives for answerers. If you want to get the upvotes, you have two good courses of action: You can either already have 5 digits' worth of karma, or you can answer first. The first option isn't available to the vast majority of SO users, so most go with the latter one. This drives down the quality of answers. It also creates perverse incentives for behaviors such as users getting in a quick low-quality response that's just good enough to get a few upvotes from people who already understand what you're talking about, and then (maybe) following it up with an edit that provides a high-quality response that would be useful to the asker.

nitrogen• 12 years ago

It's not a completely useless question. I wrote an API data validation gem for Ruby that needs to be as unobtrusive as possible, so it needs to be fast. I found that building strings by interpolation (e.g. "#{str1}#{str2}") is quite a bit faster than addition (e.g. str1 + str2).

teyfille• 12 years ago

To be fair, I once fixed a java application that completely crashed due to using string concatenation for data export, rather than a StringBuffer. It does matter sometimes even in workaday applications.

ownagefool• 12 years ago

The argument behind premature optimization only really stands when first developing a product. Once you have an established product, it's no longer premature, it's just optimization.

emodendroket3• 12 years ago

Well, only if you never add any new features to it.

Scuds• 12 years ago

With a full suite of automated regression tests you'll have a great deal of confidence that any changes you've made didn't break anything. Go nuts and experiment!

equoid• 12 years ago

Premature optimization is that which is done before the need for the optimization is clear. Thus debugged, feature complete (for now) ... 'Established' really has nothing to do with it, if that established product has bugs then those should be addressed before attempting to optimize. Unless of course, the lack of optimization is itself the bug.

dragonwriter4• 12 years ago

Once you have an established product, it's no longer premature, it's just optimization.

Its premature even if the product is completely feature complete and bug-free if the particular optimization being done is being done before more significant optimizations, which, if you aren't using some measurement more sophisticated than "this looks like it could be optimized" to choose what to optimize, it probably is.

steve-howard• 12 years ago

I'm late to the discussion, but I will say that when you have an established product you have a much better perspective on how the code is organized than when you're still cobbling together V1.0. A firm grasp of the code's organization makes it much easier to contain the optimized part and slap on a /* HERE BE DRAGONS */ header.

ceejayoz• 12 years ago

The minute you mention optimization on most internet forums (especially StackOverflow) you get slammed for promoting "premature optimization."

Typically, that's because the people asking are asking questions like "which is faster in PHP, print or echo?" to which the answer is generally "there's about a half a second difference if you run it a million times, but you shouldn't be doing that anyways".

jiggy2011• 12 years ago

SQLite has an advantage because it is a small, well defined program (for a DB) that as is much defined by what it doesn't do as what it does. The typical problem with premature optimisation is that you optimize something that is either subject to having functionality requirements altered in a significant way and the optimisations will make it brittle when you need to do this.

emodendroket3• 12 years ago

That's because so many people asking are focusing on micro-optimizations without having profiled and while ignoring bigger factors, or else don't have working code yet.

Shog92• 12 years ago

The other response you'll see all the time? "Measure it!"

Low-level optimization is not a task to be undertaken casually. If you don't know where to look, you can end up just wasting a lot of programmer time for zero real-world benefit. If you don't measure before and after, you may even make things worse...

The SQLite folks didn't make these changes with a hope and a prayer; they carefully profiled, changed, and profiled again... And they invested years in building a test suite to ensure that such micro-optimizations don't inadvertently break the logic. The results are impressive, but so was the time and effort invested in achieving them. If you're not willing to be so diligent, "premature optimization" is exactly what you're doing.

zamalek4• 12 years ago

Oh yeah, definitely. In some situations performance implications are obvious. E.g. Adding to a list of unique values in a loop? Use a hash, not a list. That's the kind of thing you do up-front, it sounds obvious but you would be amazed at the bad responses you get on the internet for pointing this kind of stuff out.

What I guess I'm trying to say is that you can go in a write obviously slow code "which is fine because you shouldn't prematurely optimize," or you can be informed and, with sometimes no extra development time, write something that's at least competent from the get-go.

For everything else? Profile, profile, profile, profile! And make sure you are using a competent profiler. I've wasted days because of inferior tools, switched to better ones (cough VTune cough) and had major improvements in mere hours.

cartoonfoxes• 12 years ago

For real, the SQLite codebase is some of the most well-tested open-source software out there.

dspillett2• 12 years ago

"Measure it!"

And for the love of all that you care about, measure it with realistic data and use cases.

I've seen people optimise code while working with small test databases and making changes that are more efficient over the smaller data-sets but are hideously bad on larger ones (usually due to memory use growth, for instance hitting the limits where SQL Server starts spooling temporary data to disk instead of just keeping it in RAM for the duration of its existence).

Or just as bad: working with data that has the wrong balance and (to give another SQL example) giving index use "hints" that shave a little off the time to run over their test data but hamper the query planner on real data by making it ignore what might be better options.

ultramancool• 12 years ago

Well, I think the imporatant part to note is that SQLite has been through basic performance review, it's been heavily tested and used at this point. It is a mature piece of software.

So optimizations like this can certainly no longer be considered "premature".

nodata• 12 years ago

They already optimised everything else, so this isn't premature.

TorKlingberg• 12 years ago

A common advice is to focus optimization in the few places where it matters, such as in the middle of tight loops. Sqlite is in an interesting situation in than almost all their code could be called in the middle of somebody's tight loop. The same applies to many libraries, operating systems, VMs, etc, but not to most applications.

snarfy• 12 years ago

Grace Hopper on inefficiency, etc

https://www.youtube.com/watch?v=JEpsKnWZrJ8

tieTYT• 12 years ago

Sometimes writing faster code is the difference between using a hash instead of a list. Sometimes it's spending an entire day optimizing a function that's called once. The former is performance aware, the later is premature.

I disagree, it depends on the context. If using your original list is so fast that nobody human can notice the difference, it's a waste of time to turn into a hash, not "performance aware".

Actually, whether it's a "waste of time" also depends on the context. It could be worse. It could make your code harder to understand and maintain.

ahoge• 12 years ago

"premature optimization."

Once a feature works as intended, optimizations aren't premature anymore. You're then in a position where you can profile, measure, and compare.

craigyk• 12 years ago

Premature optimization is also not a true waste of time (IMO) if it's part of the learning process.

Man, did I ever 'waste' a lot of time on optimization when I was first learning to code (in C), but:

a) I learned what the low-hanging fruit was, so that I was eventually able to make all my code fairly fast without explicit optimization.

b) Man, where some of those early programs I wrote blindingly fast... especially compared to the higher-level python/numpy/scipy stuff I write a lot today. It's also nice to repurpose some of that old code (ie as a library) for new applications since every potential bottleneck has already been ironed out.

nbevans• 12 years ago

On a mature project like SQLite, nothing is premature anymore - including optimisations.

The internet is full of dumbass developers unfortunately. Both on HN, StackOverflow, and well everywhere.

rbobby• 12 years ago

Small. Fast. Reliable. Choose any three.

Nice tag line.

notacoward5• 12 years ago

Cute, but inaccurate. Small? Not quite.

http://symas.com/mdb/microbench/

Fast? No again.

http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

Note that LevelDB itself isn't exactly the speed king here. It's just the clearest example. To its credit, SQLite does fare well when it comes to reliability.

https://www.usenix.org/system/files/conference/osdi14/osdi14...

So ... choose one?

(Downvotes from those who despise facts in ... oops, already happened.)

erikb4• 12 years ago

I disagree. You can be not the smallest and still be small, etc. Still I think people should not downvote because they disagree. Especially the links in that post are quite valuable!

zaphar2• 12 years ago

Within the space of sql databases they get all three.

Writing to /dev/null beats leveldb anyday, only problem is I can't get the data back out. A leveldb to sqlite comparison doesn't is unfair to both leveldb and sqlite.

kazinator• 12 years ago

Ah, the good old 100/100 rule: 100% of the execution time is somewhere in 100% of the code.

Sometimes when you profile code you're dismayed to find that there are no clumps where the time is spent. It's everywhere. This function here takes 3%, this one 5%, that one another 3%, ... you either have to make some radical change, or else make all of these a little faster.

erikb4• 12 years ago

And if you make the radical change, you'll see that it will also have these 3% there and 5% somewhere else.

scuba7183• 12 years ago

And it will probably break something along the way :-)

underlines2• 12 years ago

Is it possible to compile this version of sqlite for ARM and replace it on an Android device to improve performance? Android mainly uses SQLite for internal stuff and allmost all Apps use it...

As stated here: http://forum.xda-developers.com/android/software-hacking/sql...

personZ• 12 years ago

Most definitely. Download AOSP, overwrite ./external/sqlite/dist with the new version, and build libsqlite.so and deploy on a rooted device (note that Chromium, and the Chrome browser, use their own copy of sqlite).

Having said that, while Android uses sqlite copiously, I doubt this would make a perceptible difference in usage, performance or battery life. It is a 50% improvement by itself, but I doubt sqlite3 usage is even measurable compared to everything else going on in the system.

underlines2• 12 years ago

Yeah, thanks. That was the thing I was looking for.

About your doubts, I'd like to quote the sqlite improvement comment: "Each of these optimizations is unmeasurable on a real-world system (we have to use cachegrind to get repeatable run-times) but if you do enough of them, they add up."

Therefore, I will build this library for my ROM, even though it maybe only gives a 0.02% performance boost, but constantly improving my ROM with those micro-optimizations will probably lead to a 50% boost one day :P

c4pt0r• 12 years ago

very good news for android developer

nXqd• 12 years ago

This is lovely. I believe the most important thing that we learn about this is how to measure your effort correctly. If you do micro optimization, but you don't track it, people will say that " DON'T PREMATURE OPTIMIZATION "

pranayairan• 12 years ago

can we expect this version to be included in android L ??

sitkack4• 12 years ago

Just ship it in your own binary.

mrweasel• 12 years ago

I really like SQLite and posts like this always makes me wonder: Could I run a webshop on SQLite, if I did it right and how many users could I potentially support?

Of cause it's not that hard to deploy a MySQL or Postgresql server, but it's still pretty hard to beat SQLite in ease of deployment and management. Running a simply webshop could be a easy as having a database for inventory and one for orders. Even very large stores aren't going to have thousands or even hundreds of orders a minute, so I don't think that writes would be an issue and reads certainly isn't an issue.

timClicks• 12 years ago

Depends. For read-heavy sites, it's possible. If you have a relatively small working dataset, you can load everything into RAM and periodically sync it to disk.

Dealing with multiple writing threads with SQLite is really annoying.

If you need to scale beyond a single host to serve the workload, something else will probably be easier to manage.

eli2• 12 years ago

You certainly could, but it's not really built with concurrent reading and writing. http://www.sqlite.org/faq.html#q5

strcat• 12 years ago

That FAQ entry is out-of-date as it doesn't mention the write-ahead logging support.

https://www.sqlite.org/wal.html

eksith• 12 years ago

I ran a forum on SQLite for a few years before switching to Postgres fairly recently. This is not due to any shortcoming of SQLite, which I believe could have handled far more traffic than what we were already getting at the time (6K - 10K unique hits per day), but it allowed us to have nicer search features.

I honestly don't know the upper limit of users you can support concurrently, but with modern hard drives, I imagine it's an order of magnitude beyond this. It has more to do with your application code than the database in my experience.

I had an informal rule: Read early, write late. When the visitor first hits the page, I'll read for the first queries and only write to the database after I've sent the response back to the user. Writes are always the slowest, but no where near as slow as the network so the visitor never had to sit and wait for writes.

The thing is, a lot of the criticisms of SQLite didn't actually apply if you limit the number of writes to < 10 and queries < 50 per hit and keep everything on one server. It's only when you try to write across the network a lot of the locking issues and such come up. Also, by 3.7, there was Write Ahead Logging (WAL) which was perfect timing as that allowed us to skip the DIY write queue entirely.

nnethercote4• 12 years ago

We have achieved this by incorporating hundreds of micro-optimizations. Each micro-optimization might improve the performance by as little as 0.05%. If we get one that improves performance by 0.25%, that is considered a huge win. Each of these optimizations is unmeasurable on a real-world system (we have to use cachegrind to get repeatable run-times) but if you do enough of them, they add up.

Interesting! That reminds me of my own experience in a different context (from https://blog.mozilla.org/nnethercote/2011/07/01/faster-javas...):

Cachegrind [does] does event-based profiling, i.e. it counts instructions, memory accesses, etc, rather than time. When making a lot of very small improvements, noise variations often swamp the effects of the improvements, so being able to see that instruction counts are going down by 0.2% here, 0.3% there, is very helpful.

amaranth• 12 years ago

Of course since processors are out of order and superscalar reducing instructions isn't always a worthy goal either. You might end up with less instructions but less things happening in parallel. And then of course you have to balance this with the cache efficiency of having to look at less instructions to complete a task.

Basically, optimization at this level is really hard.

nnethercote4• 12 years ago

And yet, in practice, I have found that optimizing for instruction counts works really well. I've gotten way more mileage out of Cachegrind's instruction counts than I ever have out of its cache or branch prediction simulations.

tbrock• 12 years ago

Anyone using newer versions of SQLite out of homebrew on OSX? If so are you having any trouble with conflicts of any sort with the system version? OSX heavily relies on SQLite in many places and I'm curious if it causes trouble to try and use both (and have it in your path).

nkurz5• 12 years ago

Deep within another thread, I had a conversation with 'nnethercote' regarding Richard's use of Cachegrind to optimize performance. My position is that it is no longer a useful approach, as the actual behaviour of modern processors has diverged so much from Cachegrind's simple simulation. Instead, I think it is much more productive to use tools like 'perf' that use the CPU's built in performance counters to measure the actual performance.

My conviction inspired me to repeat Richard's test to see how the numbers compared when run on a current Haswell i7-4770 processor. Here's what I got for the versions he compared when compiled with GCC 4.8.1 and using 'perf stat speedtest1 --size 5'[1] (sizes are for the sqlite.o lib only):

  Cachegrind per Richard:
  3.8.7a -Os: 953,861,485 cycles
  3.7.17 -Os: 1,432,835,574 cycles

  Haswell:
  3.8.7 -O3: 469,956,519 cycles (870,767,673 instructions, 1,069,032 bytes)
  3.8.7 -Os: 542,228,740 cycles (884,541,860 instructions, 648,360 bytes)

  3.17.7 -O3: 651,680,545 cycles (1,320,867,830 instructions, 1,002,192 bytes)
  3.17.7 -Os: 771,304,471 cycles (1,406,527,795 instructions, 605,976 bytes)

One can make of the numbers what you will, but here are some conclusions I drew:

While the improvement is about the same magnitude that Richard sees, the actual number of cycles is off by about a factor of 2.

Despite having a larger binary, and thus theoretically worse instruction cache behavior, -O3 beats -Os by about 20% in speed in both cases, at a cost of 40% (400K/1MB) in size.

'perf record -F100000 speedtest1 --size 5; perf report' is a really slick and easy way to figure out where the program is spending it's time. If I were optimizing this, I remain fairly certain it would be more a more effective approach than using Cachegrind.

[1] I had to comment out the "sqlite3_rtree_geometry_callback" line in speedtest1.c to get it to compile, which might affect my numbers slightly, but from the source comments I don't think it was actually being used.

cliffbean• 12 years ago

I find perf a very useful tool, and use it frequently.

At the same time, I don't see how your data supports your conclusion that perf is more effective than cachegrind here. The absolute cycle count is of limited interest. The most important thing these tools do is tell programmers where to look, and cachegrind seems to have done a good job of that here.

In my experience, perf and cachegrind are two tools in the toolbox. perf is stronger at optimizing for the particular CPU I'm on, and it runs faster. Cachegrind collects more detailed information, and while the model it uses isn't perfect for the CPU I'm on today, it's usually good enough to be useful, and it's good for optimizing things likely to matter on other CPUs too.

Ono-Sendai• 12 years ago

One problem with perf counters, in my experience, is that the results vary a lot in a quasi-random way. Trying to get repeatable results is a painful experience.

SQLite• 12 years ago

The cycle-counts returned by cachegrind are repeatable, to 7 or 8 significant figures. That means that I can make a small change and rerun the test and know whether or not the change helped or hurt even if the difference is only 0.01%. I don't think perf is quite so repeatable, is it?

Also, the cg_annotate utility gives me a complete program listing showing me the cycle counts spent on each line of code, which is invaluable in tracking down hotspots in need of work. If perf provides such a tool, I am unaware of it.

Remember that I'm not trying to optimize for a specific CPU. SQLite is cross-platform. I want to do optimizations that help on all CPUs using all compilers. I'm measuring the performance on the "cachegrind virtual CPU" of a binary prepared using GCC and -Os because that combination gives repeatable measurements that are easy to map into specific lines of source code. But the optimizations themselves should usually apply across all CPUs and all compilers and all compiler optimization settings.

Nkruz is, of course, welcomed to use any tool he likes to optimize his projects. But, at least for the moment, I'm finding cachegrind to be a better tool to help with implementing micro-optimizations.

nkurz5• 12 years ago

The cycle-counts returned by cachegrind are repeatable, to 7 or 8 significant figures. ... I don't think perf is quite so repeatable

It can come close. I just tried, and for 'speedtest1' seemed to be getting 3 significant digits for the cycle counts, and 4 for the instruction count. You'd probably gain another one or two if you were to measure computation only and remove the printf() and other I/O statements. The underlying performance counters are pretty much cycle accurate.

cg_annotate utility gives me a complete program listing showing me the cycle counts spent on each line of code... If perf provides such a tool, I am unaware of it.

Yes, that record/report combination I quoted above does just this, with insignificant runtime overhead. Unlike the total counts, this one is sampled, so you might get a little more variation. It's definitely good enough for quickly finding hotspots, and there are other (harder to use) tools that can use the precise "PEBS" events you need complete counts. These even allow you to do nifty things like track the number of times each branch statement is mispredicted.

I'm measuring the performance on the "cachegrind virtual CPU" of a binary prepared using GCC and -Os because that combination gives repeatable measurements that are easy to map into specific lines of source code.

Absolutely, this is the right way to view cachegrind. My question would be whether it is the right generic CPU to be using, and whether optimizations made on it translate well to other modern CPUs. Many of them will, but I think you'd have faster turnaround time even better success with an approach that uses a real CPU and its performance counters.

I'm finding cachegrind to be a better tool to help with implementing micro-optimizations.

Please realize I have the utmost respect for your work on SQLite. It's my most frequent answer when asked for an example of C code to study, learn from, and pattern after. I'm certain you will manage to optimize it with any tool you choose, but having spent many hours with GProf and cachegrind myself, I (will the zeal of a recent convert) think you'll be amazed with some of the things that are now possible with performance counters.

SQLite 3.8.7 is 50% faster than 3.7.17

Related Stories