Topic: Anyone using SQLite?

Any of you running PunBB on SQLite? If so, is there any particular reason?

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Anyone using SQLite?

I've been thinking/trying to move my forum over from MySQL to SQLite because a) my host's MySQL is grossly outdated (3.something - though I suppose it doesn't really make much of a difference) and b) I enjoy the convenience of simply downloading the database to back it up (though I suppose neither is really important seeing as no one - including me - posts to my forums anymore).

I also use SQLite when playing around with things locally simply because it's so easy to set up.

In other words, SQLite support is neat to play with but not wholly necessary, for me at least.

Looking for a certain modification for your forum? Please take a look here before posting.

Re: Anyone using SQLite?

Rickard wrote:

Any of you running PunBB on SQLite? If so, is there any particular reason?

I am running PunBB on PHP5 w/SQLite, and Lighttpd. smile

Because SQLite's database is easy to backup w/o any plugins.
And I need not to install MySQL anymore.
MySQL is too fat now and I can only install Apache+PHP5 or Lighttpd+PHP5 w/ SQLite.
It can reduce my server loading.

Re: Anyone using SQLite?

I have just started to use sqlite because of hosting restrictions, though how far I get with it is another matter.

my mind is on a permanent tangent
byUsers forum

Re: Anyone using SQLite?

I am using PunBB for a support forum for a web application I designed. Actually, it's not officially live yet, but it will be soon. Anyway, I expect very little traffic (less than a few posts per day) so went with SQLite.  SQLite is also useful for testing, since it's easy to configure and the data is all in one file.

Re: Anyone using SQLite?

Just to update things, as I have spent most of the day importing data from Yabb into punBB the forum is now available via: http://www.by-users.co.uk/forum/

Not that much done theme wise, though things do change according to the season (header, logo and background only at this point).

Now to see if the server can cope with it!

my mind is on a permanent tangent
byUsers forum

Re: Anyone using SQLite?

I got converted smile
So far is going as fast as ever.

My forums have about 2500 users and 6000 posts. According on sqlite.org, sqlite would handle all punbb forums i have seen so far.

sqlite.org wrote:

SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than a 100000 hits/day should work fine with SQLite. The 100000 hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

My reasons:
It's simpler to back up, restore and carry arround
Sometimes the mysql server goes down or i reach the maximum allowded connections by my host.

lighttpd+php5(sqlite)+punbb
that souns really cool.

PS:my forums are not 100% swedish technology anymore, lol

Re: Anyone using SQLite?

pedrotuga wrote:

So far is going as fast as ever.

My forums have about 2500 users and 6000 posts. According on sqlite.org, sqlite would handle all punbb forums i have seen so far.

I assure you, it would not. The example you posted is valid, but only for a more or less static website. A forum is anything but static. SQLite would probably handle this forum, but as soon as the number of concurrent users increases, you will hit a wall with sqlite. It's very fast at doing your every day run-of-the-mill queries, but as soon as you write something to the database, every other thread that wants access to any table in the database will have to wait until that write is completed. That's the problem with what is essentially database level locking.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Anyone using SQLite?

I didnt know sqlite locked the whole database for writing. Well. at least you dont run into integrity insert errors big_smile

This is very relative i know, but.. how big and how visited would a forum be when a sqlite db would start to decrease performance.

Re: Anyone using SQLite?

pedrotuga wrote:

I didnt know sqlite locked the whole database for writing. Well. at least you dont run into integrity insert errors big_smile

It has no choice. The whole database is stored in one file smile

pedrotuga wrote:

This is very relative i know, but.. how big and how visited would a forum be when a sqlite db would start to decrease performance.

I couldn't say. I really don't know of any big forums that use sqlite.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Anyone using SQLite?

how big do you want it?
    Total number of registered users: 7237
    Total number of topics: 2457
    Total number of posts: 20321

converted from a YaBB installation (via YabbSE, via PunBB/MySQL)

my mind is on a permanent tangent
byUsers forum

Re: Anyone using SQLite?

Rich: It's more dependent on the number of concurrent users. I'd be interested in seeing an SQLite forum with an average of maybe 50 users online.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Anyone using SQLite?

:grin: I'll keep an eye on it, it's only been up 36 hours! Most so far is around 15, but I think I'll write a small mod to keep try and track how many (or adapt an existing one). I'm hoping it'll get busier, just hope the server doesn't break (I can't even VACUUM the db at the moment *sigh*).

my mind is on a permanent tangent
byUsers forum

Re: Anyone using SQLite?

and then more problems.

SQLite is fine, but the server I am hosting on isn't and scripts are failing, first it was edit, and now show all your posts *sigh*. I can't even perform a back up, FTP speeds are so slow the database, which is currently 60+Mb, would take all day to download *grrrrr*.

I'm now recommending we ditch our free hosting and go elsewhere, then I'll have MySQL *yay*

my mind is on a permanent tangent
byUsers forum

15

Re: Anyone using SQLite?

I'm currently running a very small PunBB forum (less than 10 users) on SQLite, and it seems to be working very well. I was running it on MySQL before, but switched it over because my host charges for MySQL usage - only $0.02 a day, but that adds up over time. Converting from MySQL to SQLite was pretty tricky, especially since there was something screwy with the database schema that the setup process installed, but after that everything went smoothly.

16 (edited by deadram 2007-02-08 18:20)

Re: Anyone using SQLite?

<-- Running lighttpd+sqlite3 (I coded the db layer to support sqlite3 myself, can't garantee it's 1.2.14 compatible, but it should work afaik)

Been a while since I last posted here... Anywhos, somewhere on these forums you'll probably run into "pdosqlite3 dblayer" or something along them lines. Download the tarball and follow the directions to add support for sqlite3 databases (which add a few improvements to sqlite 2 databases, the default with punbb 1.x). Converting from sqlite to sqlite3 is easy from a shell:

sqlite forum.db2 ".dump" | sqlite3 forum.db3

I've had a few problems with my computer and my time the past few months, and I am making the move to mysql (only because my e-mail server requires it; and because it's just as easy to backup the database iff you have shell access; and because of async requests - if sqlite3 supported async read/writing i'd probably stick with sqlite though tongue). So if anyone wants to take over the sqlite3 db layer "mod" your welcome to it.

EDIT --

Found it, here's a link to the topic:
http://punbb.org/forums/viewtopic.php?id=13123

And here's one to the tarball:
https://robshouse.no-ip.info/pdosqlite.tar.bz2

If the link doesn't work, I've probably buggered something up in my firewall config, so just post and tell me. I don't really have a computer outside the firewall to test with ;.;

EDIT --

Ohh, just to say, the reason I started to use punbb was because it supported SQLite. I started with lighttpd, then moved on to choosing a database, and finally choose a forum that supported my database (I think punbb is the only open source forum project that does, or at least the only one that will work ^.~).

SQLite(3, and 2 afaik) can do async read requests. It get's into trouble with major traffic if there is a write request. For the duration of that single write request, each read and/or write request will probably fail; From what I remeber both my version and the original version of the sqlite db layer set-up read/write access on every php page, and the translates into each single request being required to close before the next request for a page is allowed to be opened (or times out). Although, if you were crazy and felt like a quick hack, you ~could~ re-implement all the sqlite dblayer functions to retry on fail every 1 seconds for 15 seconds, and only open the db as read until a write statement is found. Since both 2 and 3 support the BEGIN TRANSACTION, COMMIT and ROLLBACK SQL statements this would be close to trivial, except for the parsing of each statement (which could be a SELECT or could be a UPDATE or any number of commands that may or may not require write access).

I personally feel that a more abstract DB layer should be made on top of the current layer. This would mean more CPU usage server side, but could facilitate database support for modules. http://punbb.org/forums/viewtopic.php?id=13125 for a fuller view of what I'm trying to say in to many mumbled words, and a few reasons why not to do it too ~.^ Also with this type of db layer databases could be opened and closed depending on which sql statement is about to be executed. Allowing a "only reading from db" php page to only open for reading. I think index.php, search.php and viewforum.php are the only pages that are read-only, but they are also probably the most used pages on any forum.

lol, sorry for the triple edit... seems my short post turned into a long one, but I have a few months of silence to make up for tongue

echo "deadram"; echo; fortune;

17

Re: Anyone using SQLite?

I'm using Omnisecure3 webserver with PHP 4.3.4 and SQLite 2.8.14 running on a Windows PC.
Choice is made because combination is very 'light' (less than 5MB), very easy to install and only used for local purposes with very few users.

Re: Anyone using SQLite?

I am having some performance issues and i suspect is the database taking to long to answer...
but i am not having that many concurrent users, usually there is between 6 and 10 users online, sometimes 12.

My database is 20 Mbyte big
3000 users
7000 posts
Could it be the database that is slowing things down?

Re: Anyone using SQLite?

It's difficult to say without doing some tests. Does the page just take long to load or does PunBB report long page generation times if you enabled DEBUG mode?

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Anyone using SQLite?

Rickard wrote:

It's difficult to say without doing some tests. Does the page just take long to load or does PunBB report long page generation times if you enabled DEBUG mode?

Ops... didn't saw this last reply the last time.
The pages are loading a bit slow. My forums have like 6 to 12 people online usually.

Sometime ago y database got locked... i didnt figure out how to unlock a sqlite database so i just

$cp mydb mydb.bak
$rm mydb
$cp mydb.bak mydb
$rm mydb.bak

Probably query got lost, i am still trying to figure out what made the database locked. The file permissions were ok.