The most important argument against MySQL?
A recent series of articles (“The PostgreSQL Evangelist Memo: Criticize MySQL Wisely” 1 , 2 , 3 ) hooked on to the living.
It so happened that my team inherited, a system that was already fully developed, with 300+ objects, where MySQL is one of the key components of the system. Some sites also use replication. Software using MySQL from a third-party developer.
Most of the objects are located in regions remote from the “reasonable person” (mountains, steppe). Some objects are located in ~ 200km from the nearest settlement. Power outages at these facilities are common and regular. UPS is very helpful, but sometimes they are not able to save from prolonged blackout. And most often from a series of blackouts. Ie the UPS has not yet been charged, the equipment is already on and running, it writes data to the database, and then the EA begins to disappear and appear and disappear again. Systems are falling.
Before the MySQL upgrade to version 5.6.23, we had to manually restore two or three databases per month. Now it is necessary to restore less often, but still it is necessary. Since August, only two databases have been restored.
After one of the kaamos articles, we started testing 5.6.26 and testing showed that this version is even more tenacious. However, the conditions of the sites, we can not fully simulate (about 20 types of sites). The load profile on all these types is different, although the database model is the same.
')
So, the key condition of the problem:
We have tables with foreign keys and ON DELETE CASCADE ON UPDATE CASCADE, on some tables that are referenced to the above mentioned, triggers are installed.
Quite possibly, this is a bad data model design for MySQL. Why? Just today I came across the following statement:
The ACID engine is a standard definition of consistency, but it can be used as the standard definition of consistency when it comes to international standards. This is the result of the InnoDB Storage Engine level.
If this statement is true, then the letter 'C' from ACID, in our case, is absolutely not guaranteed.
And then you should not be surprised by the error messages of the form:
[ERROR] Table ./database/table has no primary data path in MySQL!
Also, it does not seem worth 100% to hope for consistency during replication.
Perhaps this problem is solved in 5.7, and as soon as it appears in the package base of our distribution, we will start updating the systems and happily forget about the problem.
However, how many more surprises does the multi-tier MySQL architecture contain?
It so happened that my team inherited, a system that was already fully developed, with 300+ objects, where MySQL is one of the key components of the system. Some sites also use replication. Software using MySQL from a third-party developer.
Most of the objects are located in regions remote from the “reasonable person” (mountains, steppe). Some objects are located in ~ 200km from the nearest settlement. Power outages at these facilities are common and regular. UPS is very helpful, but sometimes they are not able to save from prolonged blackout. And most often from a series of blackouts. Ie the UPS has not yet been charged, the equipment is already on and running, it writes data to the database, and then the EA begins to disappear and appear and disappear again. Systems are falling.
Before the MySQL upgrade to version 5.6.23, we had to manually restore two or three databases per month. Now it is necessary to restore less often, but still it is necessary. Since August, only two databases have been restored.
After one of the kaamos articles, we started testing 5.6.26 and testing showed that this version is even more tenacious. However, the conditions of the sites, we can not fully simulate (about 20 types of sites). The load profile on all these types is different, although the database model is the same.
')
So, the key condition of the problem:
We have tables with foreign keys and ON DELETE CASCADE ON UPDATE CASCADE, on some tables that are referenced to the above mentioned, triggers are installed.
Quite possibly, this is a bad data model design for MySQL. Why? Just today I came across the following statement:
The ACID engine is a standard definition of consistency, but it can be used as the standard definition of consistency when it comes to international standards. This is the result of the InnoDB Storage Engine level.
If this statement is true, then the letter 'C' from ACID, in our case, is absolutely not guaranteed.
And then you should not be surprised by the error messages of the form:
[ERROR] Table ./database/table has no primary data path in MySQL!
Also, it does not seem worth 100% to hope for consistency during replication.
Perhaps this problem is solved in 5.7, and as soon as it appears in the package base of our distribution, we will start updating the systems and happily forget about the problem.
However, how many more surprises does the multi-tier MySQL architecture contain?
Source: https://habr.com/ru/post/270179/
All Articles