Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Material from a MySQL course

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 separate httpd and mysql servers and a load balancer

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

splitting httpd and mysql across servers for robustness
splitting httpd and mysql across servers for robustness

Using httpd as a load balanacer
Using httpd as a load balanacer

A scheme for control server and video content server
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.

OPTIONS AVAILABLE TO YOU
You may follow links in the text to explore other topics.
Extra link - Return to the HomePage of this share system

STATUS AND CONTROL
You are a guest
Enter name and password to


This is a page from Well House Consultant's shared information directory. Learn about our shared information system.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/share/mysql.html • PAGE BUILT: Fri Apr 5 18:43:09 2013 • BUILD SYSTEM: wizard