Databases
Submitted by chayak on Thu, 12/27/2007 - 18:33.
Well I don't know why because databases usually put me to sleep or create urges to gnaw off a limb to escape but I did some research on them the other night. It kind of surprised me to as SQLite caught my eye and I really looked into it. MySQL uses a lot of memory for larger databases and though it is a great database and a staple in the linux community it's real strength is larger databases on seperate servers. It turns out for a low to medium volume site such as most blogs and forums SQLite may actually be the better alternative because it doesn't sit in memory. It runs as it's needed. This can save sites some overhead on the ram side of things and slim the requirements overall. When does it become more efficent to use something like MySQL? I don't know but from what I read if your site is getting so many hits you're considering moving the database to another machine then is the time to switch to MySQL and since SQLite both understand SQL so moving a database is as easy as an import command. Databases might not be the most exciting aspect of websites but they are the backbone of dynamic content so it pays for an admin to learn a bit about them.
-------------------------
Brandon www.linuxresonance.com
Databases
SQLite is light-weight, but as far as I can tell, the record-locking mechanism is the weak point.
If you don't have tons of users accessing the same records at the same time, then yes, SQLite is a nice lite database. I myself prefer PostgreSQL over MySQL. I tend to root for the underdog.
Verbal
I tend to agree with you
I tend to agree with you about PostgreSQL as it has been proven to be a more reliable database. MySQL just seems to be the juggernaught in most open source installs so there tends to be more support documentation, and more integration support. So in short, if you screw something up its easier to fix, and there is usually more community support if you use MySQL.
In Drupal 6 they are trying to make sure PostgreSQL is supported to the fullest extent.
Chayak I do not know much about SQLite. I usually end up going with Mysql for the reasons mentioned above. There is also the othre paradox of having your site become popular, and then having to switch databases, which can be really messy. So why not just start with MySQL.
There is such a thing as mysql query caching, but I have limited experience in it. I have however, installed eaccelerator, which dramatically increased performance on my web servers that handle high traffic scenarios. However, this is a PHP caching mechanism. If you do a search on my blog you can see how to install it on Ubuntu.
Hows that for a long crappy answer to a good question?
Well, looking at it SQLite will handle most jobs as if you really get that popular to need a seperate database machine you're going to have to switch and reconfigure databases anyway. I'm am interested in PostgreSQL but I'm kinda looking at a solution for a database that could span TB and PB in the theoretical area. Though at that size you'd need a database to index the database and the 5 "9"s would mean a datacenter just for that database with reduntant connections and routing. I've been playing with the design of a system I call Rabid Coyote which is a wifi survey and collection system though instead of regular kismet dumps I wanted a system to output those into a database tagging the header information, addressses, etc. Then set it up at a site and come in later and use the system to look for rogue connections that don't fit the pattern of normal operations. Though it could easily have other uses in the black hat arena if you use your imagination.
--------------------------------------------------------------------------------------------------------
Linux Resonance Podcast
Linux, hardware, securty, and audio/video production
That would be very cool.
That would be very cool. Cisco's wireless lan solutions has a box for 20k that does something similar. I know that load balancing in mysql is well documented for database centers, and if I'm not mistaken, it is what google uses (I could be very wrong there). Actually TWIT Floss weekly had the load balancing manager from google on this week, but she didn't go into detail about her job. Very good interview though. I digress.