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.
[imgr=mysql_09]mysql database structure[/img] 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.
[imgl=mysql_10]types and choices of mysql locking[/img] 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.
[imgr=mysql_o1]packaged with Mysql[/img]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.
[imgl=mysql_o11]mysql permissions[/img]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.
[imgr=mysql_o12]making your mysql queries efficient[/img] 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
[imgl=mysql_o8]Complex mysql join conditions effect mysql performance[/img] 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!
[imgr=mysql_o9]do not store calculated results in mysql tables[/img]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.