The website and online store "Eldorado" is about 40 thousand purchases every day. There is probably no need to explain what this means for the company's business.
Historically, the store runs on the Bitrix engine with a huge amount of custom code and add-ons. The storage is a MySQL cluster with four master servers.
A significant number of companies have monolithic applications, and many have to work with them. There are plenty of ways to deal with the monolith, but, unfortunately, few people write about successful ones. I hope that the story of how we prop up our monolith (until we saw it) will be of interest to you.
We are well aware that massive architecture can bring a lot of problems. But it is easy to destroy it, by a simple strong-willed decision, it is impossible: sales are going, the site should work, changes for users should not be radical. Therefore, the transition from a monolith to a set of microservices takes time, which we need to hold out: to ensure the system is operational and its resistance to stress.
Sql means structured query language, which is the standard language for accessing and manipulating databases. mysql is a database management system like sql Server, oracle, informix, postgres, etc. mysql is an rdms (relational database management system).
When considering data management utilities, the two most popular options are mysql and sql server. Both effectively keep data organized and easily available through the user interface. Both techniques have the concept of a schema for data storage (i.e. table storage).
To better understand the differences in mysql and sql server, mysql is more inclined to select data so you can display, update and save again. mysql is weak in terms of inserting and deleting data. But it's a good choice for data storage and data reference.
Here are some specific technical differences between mysql and sql servers, when it comes to the ANSI sql standard: functions like stored procedures, triggers, views and cursors become part of the mysql database server in mysql 5.0 version 5.0 and you still won't find a rich set of functions and features in terms of development. For more depth analysys click sql vs mysql.
I think that each of you tried to find the own way to solve tasks in the learning process of a relational database management system, and not knowing that out there are various helpful features, which could speed up the queries at times and reduce the code size. In this article I want to share with you my experience, namely how to work with MySQL comfortably, often allowing the programmer to do the things that other databases would not be able to do. This material would be useful rather for those who just decided to delve into the wonderful world of queries, but maybe the experienced programmers will find something interesting for themselves here.
We all remember the classical explanation about the indexes in the database and how they make the task easier to find the right lines. I'm sure most of you visualizes something like this:
It becomes clear right away that it takes much less effort to find two or three right lines throughout the data. It is brilliant, easy, and clear.
Personally, I always thought that there is no room for any improvement regarding this method until I got familiar with the clustered indexes. It turned out that the non-clustered indexes are not that perfect as I though.
So here are some questions: What is exactly a clustered index? Why is it better than the non-clustered one? What’s going on with it in MySQL?
The database backup always has to be adjusted for the existing projects directly to the live production servers.
This situation is easily explained. At the beginning any project is empty and there is simply nothing to copy. In the phase of rapid development the heads of a few developers are busy working on the project and fixing critical bugs with the deadline a day before yesterday. And only when the project has reached its ultimate point, there comes the awareness that the main value of the system is accumulated database, and its failure would be a disaster.
This article is for people whose projects have already reached this point, but they haven’t been dumped on enough.
Somehow, I found a reference in the depths of documentation MySQL on dev.mysql.com/ about the fact that if MyISAM is used, we can get an increase in 5-7 times in reading speed from the table, if we read the data from the table ourselves. I wanted to verify this fact, so I did it. Let us see what came out of it.
What type of data will we read from MyISAM?
Format MyISAM has several formats (namely they are multiple storage formats of indexes and several types of data storage for the most common data, which are fixed and dynamic). I was interested to consider the very simple case: when a table has a fixed length of record, but it does not contain NULL fields and the type of queries that we are going to test. This is a simple SELECT * FROM tbl WHERE (condition) is without sharing the indexes and other tables (it is called full scan).
There is a special mode in MySQL that is designed for inputting the invalid data in a database. For example, in order to input in INT-field 2147483647 instead of 20000000000 or to fill the database with non-existent dates and cut lines. Well, you never know what fro this mode might be useful.
This mode is called a "traditional mode"
Although, at first glance it seems crazy and such a behavior is a historical reason.
The engine of MyISAM was a former standard engine for MySQL recently; it does not support the database transactions. If one thing falls off from a set of queries, then the database loses its integrity. In comparison with such risk an entry into the database of incorrect values was lesser evil.