Which is quicker ...MySQL vs Flat file?

Anon

Hello all,

Was wondering if anyone knows which scenerio would be faster and why.

I want to store some session info such as a randomly generated alpha-numeric string 8 characters long, the userid and the date/time.

When each page of the website is loaded, I want to verify that the person has logged in (which was stored in the session info with successful login).

I know the sql SELECT could be specific in just looking for the session id (passed through the url)...but a connection, clearing of results and closing of the connection is needed each time. Wouldn't spinning through the records in a flat file just be faster? (I would have a cron job that would clear that flat file/DB every 12 hours or so).

I don't want to use cookies because many of my clients have systems that don't support them.

Thanks in advance!

gary

saloon12yrd

I tend to believe that any database would be faster. The time needed for connecting the DB, selecting, closing should be equal to opening the file, reading, closing

Anyways which one is faster, the time needed is just irrelevant. You won't notice a performance hit unless you're using very old hardware on a very big site.

I prefer database-based sessions for another reason: You can write a script that makes statistics from db-based sessions far easier than with file-based sessions.
Example: Number of users currently on the site (count() from sessions) vs. number of logged in users (count() from sessions where login=1)

Last thought: You don't need a garbage collection cronjob, session_hc already does that.

Greets,

Dominique

[deleted]

"I tend to believe that any database would be faster. The time needed for connecting the DB, selecting, closing should be equal to opening the file, reading, closing"

You'd be suprised how much time opening a database connection can take. This is usually the bottleneck (that is why they invented Pconnect).

If you don't need statistics like "number of people currently online" (and be honest, who does?) then flatfiles will probably be faster.

saloon12yrd

Hm maybe flatfiles are faster (depends on filesystem, kernel, cache vs. cache, maybe more), but who will get to experience the difference?
The "number of ppl being online" was an example. Kept simple. Intended to show what can be done. Obviously you can do statistics/whatever inside a DB easier (and OS independent) than to open many, many files and work on their content?

The whole question ("what's faster?") only makes sense on very big sites. I experienced those sites like to do certain "things". Number of ppl inside a application (residing in a single .php page, info stored indirectly in session) to give another example.
For small sites without special need it is just a decision in design without any 'real' difference.
Hence my preference.

Greets,

Dominique

P.S.: Now I'm gonna check the ENUM issue 😉

Anon

Ok,
but what is:

session_hc?

saloon12yrd

session_gc() is the garbage collecting routine used internally by PHP.
You can define a custom one if you decide to use database sessions.

Dominique

[deleted]

"Hm maybe flatfiles are faster (depends on filesystem, kernel, cache vs. cache, maybe more)"

The main point is that opening database connection requires socket functions, remote authentication (lookups by the database to see who you are etc) which takes much longer than opening a local file (which is cached anyway)

"The whole question ("what's faster?") only makes sense on very big sites."

very busy sites :-)

All these speed issues are relative, but good to know should you ever run into a very busy script. At 100 request/second you're going to need those extra 30ms per script :-)

"than to open many, many files and work on their content?"

And why would you have to work with lots of files? 🙂

The fact that the user's session data comes from a flatfile does not mean that you can't store his 'logging-on' in a database for easy stats. When a user logs on, enter that happy fact into the DB once. Then load his profile from the DB into the flatfile-based sessiondata, and you have the speed of flatfiles and the comfort of DB-based statistics.

saloon12yrd

Sockets can be circumvented, won't help you on speed though. I never tried this but on *ix systems the kernel would have to check permissions on the file and stuff just like the database would. Dunno what's faster.
"Cache": MySQL caches as well...

"And why would you have to work with lots of files? 🙂"
Because PHP stores every session in a single file and you might want to get information about all of them. I thought I made a simple example?

"The fact that the user's session data comes from a flatfile does not mean that you can't store his 'logging-on' in a database for easy stats. When a user logs on, enter that happy fact into the DB once. Then load his profile from the DB into the flatfile-based sessiondata, and you have the speed of flatfiles and the comfort of DB-based statistics."

Okay, that's a one time solution. It works just for the "logging on" thing. For another thing you'd do the same slightly different.
BTW: You store userdata in flatfile AND database in this scenario. Where's the speedgain in that?

Whatever let's cut it, it's getting hypothetical again. Must be the moon today or something 😉 Oh no, wait, it's Monday.

Dominique

Anon

I haven't noticed any difference speed-wise between regular connects and pconnects on a 225K page/day site. Using pconnects gobbles up all the memory.

[deleted]

"Cache": MySQL caches as well...

Yes, but the raw database speed is not the problem, it's making the connection that slows you down a lot. The filesystems doesn't have to connect, it can just open the file. That would cause a disk IO once, and from then on the file is opened and read/written in cache, hence it is faster.
You could get close to that speed using pconnect() for databases, but that is too messy/dangerous.

"BTW: You store userdata in flatfile AND database in this scenario. Where's the speedgain in that?"

The speed gain is that at logon you do a 'slow' database query to get the data you need, and you copy that to the fast flatfiles. All the subsequent pagerequests can get the data from the flatfile, saving you a database connection and a query.

The 'proper' way would be to always use a database, but this is an 'acceptable' solution if you need raw speed.

But enough of this, it's only 9:30 and I have only had two cups of coffee :-)

[deleted]

The speed change is only 30-40ms, at 225k/86400=2.6, give it three times that during peak hours=8 page views/second I'm not suprised you don't see it. (saves about 8*30ms 240, or 0.24 seconds of processing time on all pages combined)

You will begin to notice it if the database server gets busy. Then creating connections will start to take longer and longer, and then you'll definately notice the difference between connect and pconnect.

It's really a shame that pconnect's implementation is such utter crap in PHP.

Anon

It's really a shame that pconnect's implementation is such utter crap in PHP.<

How so? (and how do you know?)

[deleted]

For instance, if you use pconnect, and insert a record into a table that has an auto_increment column, the value of the 'last_insert_id' remains available to all subsequent scipts that also use pconnect.

Luckily this doesn't work for transactions, or you could be in serious doodie. (imagine if one script started a transaction on a pconnect and then ended without committing or rolling back, then all subsequent queries on that pconnection would be lost when the connection times out.)

Also, in a multithreaded environment such as apache webserver, there is one instance of PHP for each instance of the webserver,if you run 50 threads of apache, and you run three scripts that all use a different database, you get 50*3=150 database connections (even though you can only ever use 50 at the same time)

smarlowe

If this information will be such that each record if only accessed for an individual, and not shared or you don't need statistics, I'd just use the built in session variables in PHP. They are stored as float files in the /tmp directory, and you can write a cron job to delete all session files more than a day old to keep /tmp clean.

Then you don't have the data consistency / flock problems a flat file can cause, or the speed issues of a database.

For most sites (<10 hits a second) it doesnt' really matter, but hey, you never know when you're site will become the "next big thing" and get slashdotted, so you may as well use the most efficient algorhythm you can right up front...