MariaDB choose storage engine

Question

MariaDB choose storage engine

Navigation

#1 by (0 votes)

1

I was reading about the different storage engines supported in Mysql. The most known ones seem to be InnoDB and MyISAM, but with MariaDB it seems that there are more options, the issue is that I do not know which one to use, so I am resorting to your experiences and knowledge to give me a hand in this, the database that I am using is generated from an external system that collects information from a SCADA system, this system is writing in the table "DATA" all the time and generates a large number of records, so much so that I could verify that in a month I generate approximately 7 million records, that data is used to generate reports through an external program written in C # and reportViewer the table "DATA" stores the process values In a single field called "values" this field stores all the records sent by the SCADA, so to show it in reports it is necessary to convert that data column in several rows according to the code. go of each data. this report is used on average 3 to 5 times a day. As you can see, although the application does not have many update or delete queries, it saves large amounts of information and should serve moderately complex queries.

Try to be as clear as possible, but if there is any question, please comment.

Greetings to all.

mariadb

asked by root2 12.03.2018 в 23:27

source

1 answer

check if an accessibility service is activated Create autocomplete in jQuery placeholder

score 0 · Answer 1

Supports many storage engines, but each has its advantages and disadvantages, I advise you to start by InnoDB, anyway I'll explain the most common so you have an idea:

MyISAM: It was the first and default for previous versions of MySQL 5.5: It is not transactional, it does not support foreign keys. It is optimized for high reading operations. Supports only blocking of tables and not records, so it is inefficient for operations INSERT, UPDATE and DELETE concurrently. It is not advised as the main engine. It is not safe in case of disaster.
InnoDB: It is the most widespread, safest, fully transactional ACID, it has practically the same performance as MyISAM. MariaDB has the exclusivity of having on this engine Virtual Columns.
TokoDB: It has the same features as InnoDB, but it is optimized for high typing operations.
CSV: It's the most basic thing there is, it saves the content in CSV, it has no advantage, it Use to save a log as the slow log or general log.
FederatedX: It allows to connect a table with another table hosted on another server, it is not optimal in performance. It is usually used for administrative issues and is considered a security hole.
Blackhole: As its name says, it's a black hole, everything that is recorded disappears. Usually used in Slaves for administrative matters.
Aria: It is an improved version of MyISAM to support only faults.
XtraDB: It is a solution that implements InnoDB with Galera to clusterize.

I strongly recommend that you start with InnoDB, and as a second option TokoDB. Anyway, Hardware requirements like; Disk, CPU and RAM are essential for good server performance.