To tweak, or not to tweak? (mysql)

Assuming you have a cluster of mysql servers serving your application, and you have configured them to share the load, yada yada, your next step is probably going to be asking 'how can I improve the design of database schema?'

Here are some links which cover that field (may I remind you to backup your database before starting, follow them at your own risk, and you cannot hold me responsible for any resulting issues!)

This one illustrates tweeking a Joomla CMS application on a VPS (shared hosting server):
http://www.joomlaperformance.com/articles/server_related/tweaking_mysql_server_23_16.html

This one discusses the differences between innodb and myisam db engines:
http://worthposting.wordpress.com/2008/06/22/innodb-or-myisam/

This appears to be the command to convert a myisam (the default) table to an innodb one, if you wanted to persue that, but beware the monolithic file may need more proactive maintenance and monitoring (for corruption etc, but not fragmentation issues). Again BACKUP!!

-- "ALTER TABLE foo ENGINE=InnoDB"

This one discusses locking, and mysql transactions - no transactions on myisam though! (hence you might need innodb for that)
http://forums.whirlpool.net.au/forum-replies-archive.cfm/498624.html

There's also the problem of race conditions: two select queries (two mysql sessions, webrowsers etc) return the same row, which the application wants to update. The first to 'accepted, the second to 'rejected' - row or table locking will prevent them from writing the new value at the same time, but what happens if both processes complete in lockstep, first 'accept' ing, then 'reject'ing the same row - if only one possible outcome was acceptable? (in other words, a row cannot be accepted after it has been rejected, and vice versa.) We could prevent accidental overwrites by increasing the criteria of the where clause:

UPDATE `orders`
SET `status`='accepted'
-- the first where clause identifies the row
WHERE `order_id`='".$this->DB->quotestring($order_id)."
-- and the second helps prevent 'logical' data collisions!
AND `status` != 'rejected'';

Lastly, this one illustrates a number of different ways one could design a schema
"including discussions on schema architecture, common data access patterns, and replication/scale-out guidelines" using web 'tagging' as an example.

http://forge.mysql.com/wiki/TagSchema

Make a redundant load balanced web or db server

OK, a clustered web server is not the same as a Beowulf (I never said it was).
However both projects can share practices such as diskless booting, BOOTP, DHCP (with fixed IPs for server MAC addresses) and so on. One thing I want to implement now is to have a service, such as mysqld or apache, on more than one node, with a load balancer (which also has a redundant node) serving requests on one incoming IP.

This can be acheived with UltraMonkey and heartbeat:

http://www.debianhelp.co.uk/ultramonkey.htm

Apache, MySQL and PHP (AMP Servers)

What is an AMP server? (also known as LAMP, WAMP, XAMPP...)
As the title suggests, Apache, MySQL and PHP (or sometimes PERL) running together make an AMP server. LAMP suggests that they are running on a Linux platform, WAMP denotes a Windows server and XAMPP is a package that can be used to setup an AMP server on Apple's OS X.

Apache is a web server
MySQL is a database engine
PHP & PERL are scripting languages typically used on web servers.

Whilst PERL (Practical Extraction and Reporting Language) is a very mature and powerful language, novice programmers may find it difficult to start with. PHP on the other hand, was intentionally written with the novice developer in mind, deliberately making it easy for novices to create web pages and applications. It has also matured, and supports Object Oriented Programming concepts, such as classes, Inheritance and so on, has a clean looking syntax resembling C/C++ (simmilar comments, and bracing styles).

It does upset some OO purists as in keeping with PHP's 'lets keep things simple for the webbie' approach meant it has done things in a way that might not be considered 'true'. For example, it is not a strongly typed language, and it recently changed its default behaviour for passing by value and passing by reference.

If none of that made any sense to you, don't worry!! You could start out with PHP writing things in a procedural style, and find that as your skills develop, you will discover that PHP can grow with you, and you can begin to adopt the OOP style.
(Object Oriented Programming, vs Procedural style.)

Now this post is deliberately short, and meant as an introduction to new users. There are other alternative databases, webservers, and scripting languages, and I am not suggesting they cannot be used instead. In fact, the concepts which apply to an AMP server could equally apply to IIS, PoorMan, Lighthttpd, PostgreSQL, Oracle, SQL server, and so on - feel free to experiment and find what works best for you! =)

The reason for covering AMP servers is that I use them myself, and they form the fundamental basis from which I create my sites and applications. When I cover the topics in more detail, I will most likely use AMP projects in the examples. By keeping to a common platform in this way, I only need to explain the basics once - and can focus on writing about the concepts, instead of the differences between all the competing technologies.