Speeding Up Inserts in MariaDB: Balancing Performance and Data Integrity

• MariaDB, like MySQL, uses InnoDB as the default storage engine. InnoDB prioritizes data integrity over raw speed. It employs row-level locking, meaning when one process inserts data, others attempting to insert into the same row (identified by unique key) have to wait.

Approaches for Faster Writes:

1. Connection Pooling:

2. Batch Inserts:

3. ** memanfaatkan 'concurrent_insert' (Utilizing concurrent_insert):**

Additional Considerations:

• Analyze your data. If inserts rarely conflict on the same rows, parallel processing with techniques like thread pools might be more beneficial.
• Consider alternative database engines. MyISAM, another MariaDB engine, allows for truly concurrent inserts, but it comes with trade-offs in data integrity features compared to InnoDB.

import mysqlclient

# Configure connection pool
connection_pool = mysqlclient.pool.MySQLConnectionPool(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

def insert_data(data):
  connection = connection_pool.getconn()
  cursor = connection.cursor()
  # Your insert logic using the cursor object
  # ...
  connection.commit()
  cursor.close()
  connection_pool.putconn(connection)

# Example usage (replace with your actual data)
data_list = [("value1", "value2"), ("value3", "value4")]
for row in data_list:
  insert_data(row)

# Close connections after use
connection_pool.close()

INSERT INTO your_table (column1, column2)
VALUES ("value1", "value2"),
       ("value3", "value4"),
       ("value5", "value6");

Note: You can adjust the number of rows per batch based on your performance needs.

**3. Utilizing concurrent_insert (Not recommended without proper understanding):

Disclaimer: This approach requires specific table conditions and can have drawbacks. Refer to MariaDB documentation for details before implementing [invalid URL removed].

This part is for informational purposes only, avoid using this code directly.

-- Assuming your table meets the requirements for concurrent inserts
SET GLOBAL concurrent_insert=2;

INSERT INTO your_table (column1, column2)
VALUES ("value1", "value2"),
       ("value3", "value4");

1. を活用する 'mariadb-import' (Utilizing mariadb-import):

   • MariaDB provides a command-line tool mariadb-import (previously known as mysqlimport) for bulk data loading. This tool bypasses the standard SQL parser and directly interacts with the storage engine, offering significant performance improvements for large datasets stored in text files.

   Example Usage:

   mariadb-import --use-threads=4 database_name data.txt
   

   This command imports data from "data.txt" into the specified database using 4 threads for parallel processing.

2. Partitioning:

3. Alternative Database Engines:

4. Out-of-Database Staging:

Choosing the Right Method:

The best method for your scenario depends on several factors like:

• Data size and format: Bulk loading tools like mariadb-import shine with large text files.
• Insert patterns: Partitioning benefits from predictable insert patterns targeting specific table segments.
• Data integrity requirements: MyISAM offers speed but with weaker data integrity compared to InnoDB.
• Workload complexity: Out-of-database staging might be suitable for very high-volume data pipelines.