These diagrams are from a tailored Mysql course ... see here for details of the public course alternatives we offer, and get in touch if you need a tailored course, or training for a group of delegates.


mysql database structure

The MysQL database engine looks after a whole series of databases through a single daemon. There are three standard databases upon installation - the one called mysql which contains the login account, details of who can do what, help files, timezones, etc - mysql's own data if you like. There is once called test which is for testing (and you are recommended to delete on production servers) and there is one called information-schema which contains things like your stored procedures which are (of course) help in tables. Then you can add your own ... such as twcrp in my example.




types and choices of mysql locking

To avoid two users making conflicting changes at the same time, or a series of selects getting inconsistent data due to an update that happened between them, Mysql offers you various forms of locking. You can lock (depending on whether you have myisam or innodb tables) at a row or table level, and you can get a read lock (which will NOT prevent others reading at the same time) or a write lock (which will give you full unique access to the table until you release the lock). This lock-for-write is also known as a blocking lock, and as an exclusive lock.




packaged with Mysql

Rather curiously, a Mysql download includes both server side and client side elements. You don't download a browser when you download apache https, and yet you get both from Mysql. Why? Probably to avoid confusion as to which download you need ... and the scheme works well with many people not even realising what they've downloaded, and using just the server or just the client code.




mysql permissions

User accounts in Mysql get permissions given to them throughout that database enfine via entries in the usertable in the mysql database, plus permissions on a per database level in the db table (there can be several rows per user), plus permissions on a per table and even per column basis given in the table_priv table. It can all get very complicated, but the recommendation is KISS - Keep it simple, stupid! - and give each user full access to his/her own database, and no other permissions.




making your mysql queries efficient

I've cut a query that took over a minute down to one that took a fraction of a second on several occasions. And it can often be so easy to do. Three things for you to look at if you have slow queries .... (1) are your indexes right? (2) Have you got complex conditions on your join and (3) are you grouping records within your mysqld or passing them back to a program that does the grouping later for you, resulting in a huge and unneccessary transfer of data?



The following series of diagrams shows a set of alternative schemes for load sharing / clustering / balancing where you have two servers and two databases. Each differs depending on robustness needs, the metrics of the traffic on offer, etc. We cover Mysql on our Mysql course and web server issues on our Deploying LAMP course.


Two separate httpd and mysql servers and a load balancer

Two httpd and mysql servers, running master / client to allow updates

splitting httpd and mysql across servers for robustness

Using httpd as a load balanacer

A scheme for control server and video content server


Complex mysql join conditions effect mysql performance

If you're joining two tables with around 10,000 records in each, you'll expect a resultant combined table with a similar number of records. Not a huge task in computing terms - until you add a complex condition to the join which means that your computer has to try out every incoming record from each table with the corresponding record from the otehr table - suddenly your task has grown to a hundred million records. Ouch. If you can keep your Mysql join conditions simple, then the engine in mysqld won't have to make and reject huge numbers of excess records and will run fast. Get it wrong and it'll run like treacle!




do not store calculated results in mysql tables

Database design principles tell you not to store the result of calculations. Not only does this take up more space, and lead to more updating work, but it also means that the data you hold can become inconsistent.

---