
SQLite improves performance with memory-mapped I/O  - moonboots
http://www.sqlite.org/releaselog/3_7_17.html
======
techtalsky
I like to take every possible opportunity to sing the praises of SQLite, which
is one of the most widely deployed RDBMS's in the world. Not only is it open
source, but it's public domain, and it's an incredibly stably developed and
well-tested piece of software.

Please note that it's not for every application. It is not a client/server
architecture. It simply a tightly optimized piece of C code that interacts
with a file (or RAM) to create a lightning fast approximation of a SQL server
that is ACID compliant.

One thing I've used it for the most, is for small, ad-hoc internal web
applications. With the webserver as its "single user", it is incredibly speedy
and reliable and holds a shockingly large amount of data (still works pretty
well when data gets up into the Terabytes).

~~~
wslh
Yes, and I don't understand why Wordpress does not support it officially and
we need to use MySQL instead.

~~~
glurgh
The theoretical answer is because SQLite is not the right tool for the sort of
things Wordpress does. SQLite supports more concurrency than one might expect
from an embedded db - it's still an embedded db, though. Writing, for instance
involves a database-global lock.

The practical answer is - Wordpress is written in a MySQL-specific way. It
doesn't officially support, say, Postgres or Oracle either.

~~~
masklinn
> The theoretical answer is because SQLite is not the right tool for the sort
> of things Wordpress does.

Which probably isn't true.

SQLite is bad at concurrent writes. Your average wordpress install is almost
only reads. And with 3.7.0's WAL, readers and writers don't block one another
anymore.

~~~
glurgh
The trouble is that there are many, many Wordpress sites out there that are
not an average Wordpress install. People (disturbingly) write e-commerce sites
on top of Wordpress. SQLite is not the right tool for the sort of things
Wordpress does not because you couldn't write a blogging tool using SQLite but
because Wordpress is crazy.

~~~
jeltz
If one could write blogs and ecommerce sites using MyISAM (shrudders) then why
wouldn't SQLite work? MyISAM had -- at least in theory, I have not done any
benchmarks -- even worse concurrency than SQLite.

~~~
glurgh
I hope I've completely forgotten any details of the convoluted locking
behaviours and tuning options of MyISAM (which, in my head at least, is read
as 'miasma') but they were there to at least attempt to support certain types
of concurrency. And they were table-level whereas the SQLite write lock is db-
wide.

Could you reasonably write blogging software that uses SQLite? Sure. It's just
that Wordpress isn't that blogging software - it's written to be paired with
MySQL, for good or ill.

~~~
jeltz
MyISAM has reader/writer table locks[1] which meant that nobody could read
while someone was writing to a table.

SQLite can be ran in two modes 1) database reader/writer lock or 2) in WAL
mode[2] where readers do not block writers and writers do not block readers.
Writers block each other though so there can only be one concurrent writer.
WAL mode provides concurrency superior to what MyISAM did.

    
    
      1. The locks could sometimes be bypassed by inserts, but not reliably so.
      2. http://www.sqlite.org/wal.html

~~~
glurgh
Sure but WAL in SQLite is 3 years old. Wordpress is 10 years old. MySQL with
MyISAM is a lot older than that. I personally happen to think almost
everything about Wordpress is wrong. It's just hard to blame them they didn't
start or subsequently adopt SQLite. I doubt it's ever been as much as on their
radar.

~~~
emn13
I believe you can approximate table-level locks in sqlite by splitting your
database into multiple files. Transactions across multiple databases are
atomic (provided you don't use WAL), so you're still in a fairly safe place.
However, I'm not sure how practical it is to split a database into lots of
files; or whether the sqlite engine deals efficiently with queries that span
many databases, for instance - I've never actually done this.

But if you _really_ need a particular table to be writable without locking the
rest of the database, it's probably a fairly decent workaround.

------
bane
Fun SQLite story, I had a project that needed to do reasonably large scale
data processing (gigabytes of data) on a pretty bare boned machine in a pretty
bare boned environment at a customer location. I had a fairly up-to-date perl
install and notepad. For the process the data needed to look at any single
element in the data and find elements similar to it. I thought long and hard
about various complex data structures and caching bits to disk to make
portions of the problem set fit into memory, and various ways of searching the
data. It was okay if the process took a couple days to finish.

It suddenly hit me, I wonder if the sysadmins had installed DBD::SQLite...yes
they had!

Why mess with all that crap if I could just use SQLite. Reasonably fast
indexing, expressive searching tools, I could dump the data onto disk _and_
use an in memory SQLite db at the same time, caching solved. It turned weeks
of agonizing work (producing fragile code) and turned it into a quick 2 week
coding romp. The project was a huge success and spread to a couple other
departments.

One day they asked if I could advise a group to build a more "formal"
implementation of the system as they were finding the results valuable. A half
dozen developers and a year later they had succeeded in building a friendlier
web interface running on a proper RDBMS (Oracle 10something) with some hadoop
goodness running the processing stuff on 8 or 9 dedicated machines.

In the meantime, and largely because SQLite let me query into the data in more
and more useful ways that I hadn't foreseen, I had extended the original
prototype significantly and it was outperforming their larger scale solution
on a 3 year old, _very unimpressive_ , desktop with the same install of perl
and sqlite. On top of the raw calculations, it was also performing some
automated results analysis and spitting out ready to present artifacts that
could be stuck in your presentation tool of choice. Guess which ones the users
actually used?

Soon I had a users group, regular monthly releases and was running a semi-
regular training class on this cooky command line tool (since most users had
never seen a command line). I've since finished that job up and moved on, and
last I heard my little script using SQLite was in heavy use, the enterprise
dev team was still struggling getting the performance of their solution up to
the levels of my original script, and hadn't even touched the enhancements
yet.

SQLite was rock-iron solid the entire time. I had a few things I wished it
could do, but I usually found a way to manage. I wasn't using it for its
feature, it existing _was_ the feature. SQLite sprinkled magic on my one-off
hack job and turned into a _thing_ that people ended up using.

~~~
baudehlo
DBD::SQLite remains one of my greatest successes - so glad you like it.

So many projects I have prototyped using SQLite (although for most I later
upgrade to PostgreSQL). Such a great little tool, and building DBD::SQLite the
way I did (including all the source) made it so easy for anyone to install.

~~~
bane
(once again why HN is awesome)

Thank you thank you thank you thank you

from the bottom of my heart.

DBD::SQLite has been one of the most important libraries for perl that I've
ever used. Including the SQLite libraries instead of making it another
dependency to install someplace makes it so unbelievably useful.

Plus it's surprisingly easy to move code over to using a more grown up
database later.

------
mooism2
I'm intrigued by disadvantage 3 from <http://www.sqlite.org/mmap.html> :

 _The operating system must have a unified buffer cache in order for the
memory-mapped I/O extension to work correctly, especially in situations where
two processes are accessing the same database file and one process is using
memory-mapped I/O while the other is not. Not all operating systems have a
unified buffer cache. In some operating systems that claim to have a unified
buffer cache, the implementation is buggy and can lead to corrupt databases._

Which commonly-used operating systems lack a bug-free unified buffer cache?

~~~
lysium
Later on, the say mmapped I/O is always disabled on OpenBSD because of a buggy
unified buffer cache.

~~~
X-Istence
s/buggy/non-existent/

~~~
lysium
True, my bad!

------
raphaelj
My rule of thumb :

Does it can be build using SQLite ?

Yes => Use SQLite ;

No => Use PostegreSQL.

Using SQLite on small/medium projects is so better than alternatives : keeps
all the ease of text files while giving the full power and reliability of a
RDBMS.

~~~
qompiler
Also never say "we will move from X to PostgreSQL later on" where X is
something like SQLite or MySQL. It will NEVER happen.

------
mmastrac
When I was building the Stumbleupon toolbar for IE way back in the day
(2009ish?), we decided that we were going to use two things that made our life
extremely easy: 1) Javascript and 2) SQLite. We basically had some C++ code
that exposed SQLite to the embedded JS, and ended up writing the majority of
the code in JS rather than C++.

SQLite was a fantastic embedded DB that was trivial to integrate with the JS
engine in Win32. I don't recall ever having to deal with any support issues
around corrupted databases.

It looks like the IE toolbar is still using SQLite to this day - you can
explore stumbledata.db from any SQLite explorer.

------
mace
The SQLite site continues to offer a wealth of information about SQLite
internals. Most of the pages (ex. <http://www.sqlite.org/fileio.html>) are a
good read for anyone interested databases or creating good system software.

------
lysium
Cool! Though a bit harsh to have it always disabled on OpenBSD. If you're
running only one process or core, you could still benefit from mmaped I/O,
couldn't you?

------
fulafel
It would be nice to see some benchmarks showing these speedups. It's rare for
mmap to be a significant win over read/write.

------
yeukhon
Honestly, nobody should use SQLite even for testing your application. If you
are going to run PSQL or MySQL, please install that DB and run tests on them.
SQLite or other DBMS have a lot of incomparability. I would consider SQLite if
I just need a database for some really really small project, such as a CLI
that speaks to an API and I need them saved locally, then I would consider
shipping my CLI with SQLite setup.

~~~
coldtea
Notice how your advice is devoid of arguments, but full of opinion?

------
swiil
Congrats Richard on a great new release.

------
bifrost
I'm not much for using SQLite but using mmap() is a no-brainer for fast file
access and I'm surprised they hadn't used it before. I mean, its trivial to
use if you know how to use fopen()...

~~~
rdtsc
> but using mmap() is a no-brainer for fast file access and I'm surprised they
> hadn't used it before.

Is it always? I remember benchmark sequential file reads and writes and plain
old read and write were sometimes faster or there was no difference.

~~~
minimax
I think a lot of people get bit by small stdio buffers and switch to mmap, not
realizing that read() based solutions can be improved greatly by better
buffering strategies.

