What to choose to store just one integer? Sqlite? or Text file?

Question

I've build a small web-service in PHP. I want to control the number of calls to a specific API method.

I thought at first of using a text file, because it's just an integer. But after taking a good look at SQLite it seemed much more convenient. So the code is just: get the "counter" from SQLite and increment it when that method is called, then store it back on SQLite db.

Is this a right way of doing it? Would it be simpler and more scalable to just use a file or... maybe something else?

Thanks in advance.

Answer 1

A database would typically scale better than the filesystem, but you are going to need to be careful and use appropriate locks with either mechanism.

Answer 2

Of course use flat file.

• It will be right thing to do
• Extremely simple
• No dependency on third-party library
• You will be able to view or update counter with just plain Notepad.
• Faster than SQLite (no SQL parsing, no other overhead)
• and as scalable as SQLite (SQLite are just more complex flat files)

If you will ever need something as fast as possible, you will have to make persistent process in memory to eliminate I/O bottleneck. But please... just don't use SQL database to store single integer.

Answer 3

Put it in a real database both a textfile, and a SQLite DB will have locking issues where if the file(s) is currently open and a new request to open it happens the second request will error out. Use a real DB like MySQL, MSSQL, or whatever is available.

Answer 4

a third possibility: memcachedb. it's compatible with memcached, but stores it's key-value store to a BDB file. it not only has read-write commands, but also atomic increment/decrement for numeric values.

one more alternative would be to write a 'counter server'. an independent process that gets 'read' and 'increment' commands over a socket. the advantage is that it's really easy to do 'atomic' increments. it stores it's counter(s) on a simple file, without concurrency problems since there's never more than one server.

it should be easy to write in less than a hundred lines of C. just a tight loop processing one command at a time, flushing to disk every few seconds. since the processing is so simple, the latency is minimal.

Answer 5

You could use shared memory to hold the counter, and locking to ensure safe increments.

Answer 6

According to SQLite it can handle sites that go up to "100K [sic] hits/day". If you're going over that, you may need to investigate a more robust database like MySQL. But if you're writing 100k records to the database every day for one website I think something is wrong with what you're doing, based on your example. This really sounds like a job for caching. Which I know nothing about in PHP, sorry.

They have this to say about files vs. SQLite: "Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files." (source)