将数据库保留在内存中，即使客户端脚本已断开连接

Question

I need to preprocess some statistics data before writing it to the main database. My php-cli script retrieves data every 10 minutes and should store it somewhere. Every hour all saved data are preprocessed again and then writted to the main db.

I thought that sqlite should be nice solution if I would keep it in memory. I have not very big amounts of data (I am able to keep it in my RAM).

Actually, I am new to sqlite (previously I was working only with mySQL). I found here that I can use :memory: instead of file name to work with memory only. But after client is disconnected database is destroyed, and if I will try to connect again to :memory: from my script - it will be different (new, empty) database.

Is it right? If it is, how can I work with same database using different php script calls, if sqlite is stored in memory?

P.S. perhaps it is a solution to put sqlite file to some "magic" directory? (I am using linux)

Answer 1

First, linux is pretty smart about using a filesystem cache. Thus, reading data from a disk is often surprisingly fast, and you should measure if the performance gain is worth it.

If you want to go ahead, one method you might consider is using a ramdisk. Linux provides a way to create a filesystem in memory, and you can place your sqlite file in there.

# mkdir -i /mnt/ram
# mount -t ramfs -o size=20m ramfs /mnt/ram
# touch mydb.sql
# chown apache:apache mydb.sql

... or something similar.