How to optimize Mysql Insert speed?

Asked

Viewed 584 times

2

I created a test table and added a script that randomly generates characters and an Insert in the a, b and c fields. For the realization of 1000 INSERTS took the time of 36s and running the same script but without writing in the bank the result was 1 second. I can improve that time somehow?

Setup:

Server type: Mariadb
Operating System: Windows 7
RAM: 4G
Processor: Pentium E5700 3Ghz

Bench:

CREATE TABLE `teste` (
  `id` int(11) NOT NULL,
  `a` varchar(15) NOT NULL,
  `b` varchar(15) NOT NULL,
  `c` varchar(15) NOT NULL
) 
ALTER TABLE `teste`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `teste`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
mysql

  • I understand your script generates an INSERT command for each tuple to be included. Try to join multiple tuples in a single INSERT command. Also evaluate if using the LOAD DATA command does not speed up your load.

1 answer

1

I change these directives in mysql to improve the performance and speed of the whole operation of my mysql. It may be useful for you.

Search for your my.ini file and change these settings:

Search for: [mysqld]

[mysqld]    

skip-name-resolve #acrescente esta linha

Change all values below significantly:

key_buffer = 1024M
max_allowed_packet = 1024M
sort_buffer_size = 2048M
net_buffer_length = 500K
read_buffer_size = 2048M
read_rnd_buffer_size = 2048M
myisam_sort_buffer_size = 1024M

And the most important thing in all of this is that you create indexes on your table.

In the Mysql database indexes can be created with considerable ease, both at the time of table design and in an existing table.

CREATE TABLE CLIENTES
(
    Codigo  INT,
    Nome    VARCHAR(50),
    INDEX (Codigo)
);

Here you note that the index was raised in the field code.

  • 1

    Leandro, can explain what these settings do and how they improve the performance of the Insert?

  • 1

    @Ricardopunctual the above directives does not only improve Insert, but rather the performance gain of at least 30% of performance in all queries, Insert, update. That is mysql performance itself. In my experience, because you increase, mainly, the use of the main memory to help the execution of mysql. But the big performance gain. WITH ALL CERTAINTY, is the use of index. After we optimized our index tables we gained a performance of at least 60%.

  • Leandro, a table without index inserts much, much faster than a table with index, I think there is a misunderstanding ai... the index goes better the performance in the query, in select... an index makes the database engine, in addition to insert data and manage pagination, you have to update the index, and even more, validate the constraints, the way you explained it doesn’t seem right

  • 1

    @Ricardopunctual I understand you, but the performance of my customers (+200) has improved and MUCH. The use of the processor has dropped to 2%. Anyway, I use these settings on all my servers and the gain is visibly great. I believe that practice + usage experience is more useful than theory.

  • 1

    I cannot give an opinion on the settings because I do not know the majority, but I would like to know more concretely how each one influences the performance, but of the few that I know, such as read and Sort buffer impact the queries, so I was curious to know how they impact the Inserts tb, it would be nice to talk more of them, tunning is always good :)

  • And how much would it be to increase significantly? Could I give an example?

  • Leandro, the question is not the settings, but this statement in your question: "And the most important thing about all this is that you create indexes in your table", as I said before, for what asked, for Insert, this does not guarantee improvement in performance, on the contrary, only this

Show 2 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.