Cool thanks! Some good stuff

greg — Sun, 10 Feb 2008 21:15:16 +0000

Cool thanks!

Some good stuff for me to read up on!

Cheers

I use ADOdb php library for

carloncho — Fri, 08 Feb 2008 22:57:04 +0000

I use ADOdb php library for a long time. Some time ago i start t use the PDO included object, that is better. But when i want efficiency, i use extension functions directly (mysql, mysqli, oci8, etc). Depending on your needs. ADOdb or PDO is easy ti use.

So what advice do you have

JeevesBond — Fri, 08 Feb 2008 01:47:53 +0000

So what advice do you have for best practice for a site that has a large number of multiple simultaneous connections?

Use InnoDB (where row level locking is needed, this is configurable per table);
make sure you've got the MySQL query cache setup;
generate and check your MySQL slow query log;
use the query log to make sure tables are indexed properly (put log-queries-not-using-indexes in your my.cnf;

There are a bunch of other variables you can play around with (see: http://dev.mysql.com/doc/refman/5.1/en/optimizing-the-server.html ), and the best tip is to test, then tweak, then test again. Keep doing that until you get the best performance. I'd also recommend siege for Web site stress testing. As an example, I made a file of all the URLs in TWF (seperated by new lines), then ran siege with these parameters:

siege -c 100 -f ~/code/siegetwf/urls.txt -r 5 -i

Setup your Web app on a home server and blast it using Siege. You'll soon find which optimisation settings work best.

Obviously the built in queries with php scripts are basic and not much can be done, do you think object based can help?

OOP won't really help with performance. But there are things you can do with PHP to improve DB performance: using memcached with PHP is a good idea though. This can greatly reduce the number of hits reaching your database. Facebook use it a great deal.

I recently saw a good presentation on MySQL performance, it's centred around Drupal but applies to any Web application. Slides and audio are available. It's the last item under 'documents', you might find some other items of interest in there, I haven't really read through the other docs though.

So what advice do you have

greg — Thu, 07 Feb 2008 18:27:48 +0000

So what advice do you have for best practice for a site that has a large number of multiple simultaneous connections?

Obviously the built in queries with php scripts are basic and not much can be done, do you think object based can help?
You mention the MyISAM, so is it essential to choose the absolute perfect field types and DB type for specific tasks? I mean is my researching the correct DB usage going to be worth it if I have a lot of active queries?
A combination of them all perhaps?

This is something that interests me greatly, as if a site becomes hugely popular, as we generally hope for, I would like to know I have done the best possible to keep it running smooth and as fast as it can possibly be.

This obviously all being best methods prior to the need for a second server when the connection numbers become too great for apache. Multiple server and load balancing is of course a whole new ball game, and I'll blow my mind when the need comes for that one

Again, it is becoming clear

JeevesBond — Thu, 07 Feb 2008 02:26:12 +0000

Again, it is becoming clear it's ot something major that will simply speed up your site on its own.

Well, that's something it'll never do, it's always going to have the overhead of being database system agnostic. The thing about multiple simultaneous connections is a good point, indeed that wasn't tested.

Think one of the big factors for the speed of simultaneous connections--in MySQL anyway--is the type of database backend in use. A big issue with the default MyISAM is the lack of row-level locks, any database with a large number of inserts will need to use InnoDB. So with MySQL it's not just the library used to access, but the database backend that are important. Just thought I'd throw this trivia into the conversation.

hmm, interesting

greg — Wed, 06 Feb 2008 14:46:53 +0000

hmm, interesting benchmark.
Doesn't tell all as it only tests a simple query and only one connection too. The main thing I hear about ADO is it's better for multiple simultanious connections.

Again, it is becoming clear it's ot something major that will simply speed up your site on its own.

Cheers

Seems to me that if you have

JeevesBond — Wed, 06 Feb 2008 05:25:06 +0000

Seems to me that if you have access to PHP5 (or up) PDO is the best choice. It's built-in to PHP, so it's going to be faster than ADOdb (with the possible exception of the ADOdb C library). Anyway, here's a benchmark: ADOdb vs. PEAR:DB vs. some other stuff.

As my findings show it isn't popular, I can only 'presume' that it isn't some new revolutionary thing we all should be learning to use.

Correct. Although, up until recently, it has been difficult to write PHP scripts that work across multiple database systems. Also this script has been around since 2000, and has been actively maintained all that time. It also has good documentation. If you look at the top of the page there's a list of CMS' (and what not) that use it for database abstraction. I don't think ADOdb has really been trumpeted loudly, but it does seem to have been quietly chugging along very successfully for a good few years. It isn't pretty, or sexy, but it fills a valuable niche. Bless its abstracted-cotton socks.

Personally, practically everything I do involves Drupal these days, and it already has a database abstraction layer. Any other projects I work on tend to be tied to MySQL (for better or worse).