Table of contents of the article:
One of the most frustrating aspects of being a system engineer is having to get your hands dirty in jobs that other professionals should do.
In fact, it can happen that after delivering a server that is well sized on the hardware and well configured at the software level the customer complains of absolutely inadequate performance of the application running on it.
Whether it can be a custom CMS, a custom CRM, or any other Web application commissioned and developed tailored to the customer's needs, the essence is that the software does not run as it should.
Often after the initial and calm complaints, as the situation worsens and continues, we get to exclaim with an annoyed tone that "Nothing worksOften with harbor bar epithets, tone and terminology.
After a normal and calm answer in which it is highlighted how that configuration is more than adequate for the customer's needs, we arrive at the customer's invitation to confront the software developer because something has perhaps got out of hand and being a custom application without specific and documented cases (as the software is neither known nor widespread) it could present critical issues obviously unknown to us and not attributable.
Obviously for the programmer, in 99% of cases of this type, the problem is never in his software, but always attributable to the system administrator who does not know how to configure a server.
And here it is, the most feared of the battles: Systems engineer versus developer.
The systems engineer is usually a XNUMXth dan black belt from UNIX, most likely has a background as a developer and DBA. It is the potential Bruce Lee able to knock you down with a stroke that ALWAYS prefers to evade the challenge, due to lack of time but above all skills.
And it is clear, that by competences, we do not mean the theoretical and practical or academic competences of being able to brilliantly analyze the problem and consequently to solve it, but it is simply not within its competence, or that that task has been entrusted to another esteemed figure of another company who is paid handsomely to design and develop functional and well-optimized software.
Too bad that in the sad life of the computer scientist and above all of the systems engineer, the blame game is always around the corner and therefore if you do not accept the challenge to the death you will always be labeled as the one who does not know how to do his job, as well as you will lose the customer who it will go to some other smoking company until some black belt systems engineer decides to get their hands dirty and prove once and for all that all the problems are derived from a bad software implementation.
Not only will you have saved the life of your customer who would have found himself with unusable software, but also of the programmer who, thanks to your advice and your interventions, will have enjoyed an improvement to his software that he can even resell to other customers.
The truth ? You don't know how to use MySQL and even less SQL in general.
One of the common aspects found in many LAMP (Linux Apache MySQL PHP) developers is that of not knowing how to use MySQL. Despite having arrived at version 8, with a set of features and functionalities now almost envied by Microsoft's SQL Server, PostgreSQL or Oracle, most developers continue to use it in the same way as they used to version 3.23, that is, without referential integrity constraints, without stored procedures, stored functions, triggers, views or transactions.
In fact, ignorant programmers should be reminded that although you remained in the Stone Age using MySQL in the same way as 3.23 currently the MyISAM engine of the time which was absolutely inadequate for professional and modern use has been replaced by InnoDB and the related forks of which we mention above all XtraDB by Percona Server which make MySQL an extremely powerful relational DBMS.
Personally we have nothing against MySQL or MariaDB, but currently we have standardized practically all our servers with Percona Server which also has accessory tools such as Percona Utilities or Percona Xtrabackup to make hot backups in a much faster, safer, more consistent and efficient way than usual trite mysqldump.
To give a small example, a programmer today is convinced that a DMBS like MySQL is a simple drawer in which to store and retrieve data. In other words, they use a very powerful (and therefore heavy) DBMS as if it were almost a NO-SQL DB and therefore light.
A DBMS like MySQL today is instead more comparable to an advanced industrial production line than to a very banal chest of drawers. In other words, those big candy production chains, where 3 main ingredients and a dye are put in, at the end of the carousel packets of gummy bears come out, cut, mixed, packed and ready to load on the delivery trucks.
For example, few LAMP developers know that MySQL is very powerful and allows you to manage most of the operations that are done at the application level, directly at the DB level since MySQL itself has an internal programming language.
Let's see some of the main shortcomings or bad practices of those who develop Web applications using MySQL.
The following reading can transform any former unemployed peracottaio recycled Web developer into a good developer and maybe even a good DBA if you continue on that path.
1. Foreign Keys and Referential Integrity
The programmer is convinced that only PHP exists, that if you want to delete the children of a node, you can delete it only via the application side, first querying the results to be deleted, then deleting them one by one and then subsequently going back to the parent table and deleting the element. Just as in the same way they are convinced that if you want to use a mathematical formula for a complex calculation this can only be performed by PHP which will produce a result which will then be written in the DB.
They do not know, for example, that to delete an artist and all his albums from a hypothetical database of music archives you can use the ON CASCADE DELETE action which allows you to perform a cancellation referenced to the cancellation of the corresponding foreign key without going to delete individually the individual values.
In the context of RDBMS, referential integrity is an interrelational integrity constraint that is a data property that, if satisfied, requires that each value of an attribute (column) of a relationship (table) exists as the value of another attribute in another relationship.
Less formally, in relational databases, in order for referential integrity to be respected, each field in a table that has been declared as a foreign key can only contain values of the primary key or candidate key of a related "parent" table.
For example, deleting a record that contains a value referenced by a foreign key from another table would violate relational integrity. Some RDBMS can guarantee relational integrity, either by deleting the respective lines of the foreign key, or by interrupting the operation and not carrying out the deletion.
2. Stored Procedures
Stored procedures are another feature whose absence has long been underlined by MySQL's detractors: with version 5.0 this absence has finally been remedied.
A stored procedure is a set of SQL statements that are stored on the server with a name that identifies them; this name allows you to re-execute the set of instructions simply by referring to it.
Each procedure can have one or more parameters, each of which consists of a name, a data type and an indication of whether it is an input or output parameter or both. If the indication is missing, the parameter is considered to be input.
3. Stored Functions
Stored functions are similar to stored procedures, but have a simpler purpose, which is to define real functions, such as those already provided by MySQL. They return a value, and therefore cannot return resultsets, unlike stored procedures. In versions of MySQL prior to 5.0 there were "user-defined functions", which were stored externally to the server. Now these functions are still supported, but we definitely recommend using the new stored functions.
4. Triggers
Triggers are objects associated with tables, which are activated when a certain event occurs in relation to that table. They were introduced starting with MySQL 5.0.2.
When we define a trigger, we establish for which event it must be activated (inserting rows, changes or deletions) and if it must be executed before or after that event; we will then have the following types of triggers:
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE
The trigger will establish a statement (or a series of statements) that will be executed for each row affected by the event. In other words, if I want to activate something when a defined event follows one another, the trigger will only activate something when the defined event occurs. For example, if we wanted to update the field of the total sold for a sales agent in the current month, we could activate a trigger that is triggered when a new value is entered in the orders table that makes the sum of all the orders of the month and write the value in the total table of the month. This would give the possibility of always having a pre-calculated data that is updated with each new order, rather than having select that in the agent's backend recalculates the value every time by adding up all the sales of the month.
5. Views
Views (term translated into Italian literally with the word "Views"), are temporary tables, which behave like real tables but which in reality do not contain "physically" data; just like any other table, a View is made up of rows and columns but in this case they are the result of a query that is stored as if it were an object.
By creating a View all you do is store the subset of an existing table through a DBMS query process, this subset allows you to refer to the properties and methods of the archived object in the same way as you can operate with a any other table.
6. ACID Transactions
The use of transactions allows you to "consolidate" the changes to the database only at a specific time: from the moment we start a transaction, the updates remain suspended (and invisible to other users) until we confirm them (commit) ; as an alternative to confirmation, it is possible to cancel them (rollback). Put simply, the concept is either all or nothing. If you have 100 records to enter perhaps in a restaurant order and at the 90th the entry is blocked because the Wifi does not work, then the system will roll back the entire transaction by canceling the 90 entries because they do not complete the total 100 entries. they should have been.
And let's not forget the performances.
If we wanted to talk about query execution speed, we can say that fewer and fewer LAMP developers know how to correctly design a database and implement a correct use of indexes which are absolutely vital if you want to achieve speed in the execution of the queries themselves.
In other words, very often slow applications that arrive at the connection timeout and even at the saturation of the maximum available connections have as their sole cause the lack of indexes on the tables.
A database index is a data structure that improves the speed of operations on a table.
Whenever a web application sends a query to a database, the database will search all rows of your table to find the one that matches your request. As your database tables grow, more rows need to be inspected each time and this can slow down the performance of the database and therefore of the application.
MySQL indexes solve this problem by taking data from a column in your table and storing it alphabetically in a separate point called the index.
Just that it works? No. It has to be fast.
In short, short of speeches, it seems that the main problem of those who develop LAMP is that they focus more on PHP programming than on database design and optimization. Without a clear academic background it seems common for the developer to simply test the software locally on very small datasets and little competition. It is okay to say that the software works and that the query returns the correct results, but it is one thing to test the software on 100 demonstration records with only one user logged in, another thing is to work on datasets of tens of millions of records with 100 operators connected in real time.
