Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2015
    Posts
    1

    Unanswered: Mariadb how can we speed up insert

    hi. we are try to insert 6 million data and we want to finish that insert quickly. in a day. How can we do this? we started to insert datas but it insert 12000 data per hour. what should we do in configuration page? 250000 per day

    my config

    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address = 127.0.0.1
    #
    # * Fine Tuning
    #
    max_connections = 1000
    connect_timeout = 5
    wait_timeout = 600
    max_allowed_packet = 16M
    thread_cache_size = 128
    sort_buffer_size = 4M
    bulk_insert_buffer_size = 16M
    tmp_table_size = 32M
    max_heap_table_size = 32M


    #
    # * MyISAM
    #
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched. On error, make copy and try a repair.
    myisam_recover = BACKUP
    key_buffer_size = 128M
    open-files-limit = 65535
    table_open_cache = 2048
    table_definition_cache = 1024
    myisam_sort_buffer_size = 512M
    concurrent_insert = 2
    read_buffer_size = 2M
    read_rnd_buffer_size = 1M
    skip_name_resolve

    # Cache only tiny result sets, so we can fit more in the query cache.
    query_cache_limit = 256K
    query_cache_size = 1M
    # for more write intensive setups, set to DEMAND or OFF
    #query_cache_type = DEMAND
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    general_log_file = /mnt/mariadb/kohadata/mysql.log
    general_log = 1
    #
    # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
    #
    # we do want to know about network errors and such
    log_warnings = 2
    #
    # Enable the slow query log to see queries with especially long duration
    slow_query_log=1
    slow_query_log_file = /var/log/mysql/mariadb-slow.log
    long_query_time = 10
    #log_slow_rate_limit = 1000
    log_slow_verbosity = query_plan
    character-set-server = utf8
    log_bin = /var/log/mysql/mariadb-bin
    log_bin_index = /var/log/mysql/mariadb-bin.index
    # not fab for performance, but safer
    #sync_binlog = 1
    expire_logs_days = 10
    max_binlog_size = 100M
    # slaves
    #relay_log = /var/log/mysql/relay-bin
    #relay_log_index = /var/log/mysql/relay-bin.index
    #relay_log_info_file = /var/log/mysql/relay-bin.info
    #log_slave_updates
    #read_only
    #
    # If applications support it, this stricter sql_mode prevents some
    # mistakes like inserting invalid dates etc.
    #sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    default_storage_engine = InnoDB
    # you can't just change log file size, requires special procedure
    innodb_log_file_size = 2G
    innodb_buffer_pool_size = 75G
    innodb_log_files_in_group = 2
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 256M
    innodb_file_per_table = 1
    innodb_open_files = 400
    innodb_io_capacity = 400
    innodb_flush_method = O_DIRECT
    innodb_thread_concurrency = 8
    innodb_read_io_threads = 32
    innodb_write_io_threads = 16
    innodb_purge_threads = 4
    innodb_buffer_pool_instances = 3

    log-queries-not-using-indexes = 1
    log-error = /mnt/mariadb/kohadata/mysql-error.log

    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem



    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 1G
    max-connect-errors = 1000000

    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition

    [isamchk]
    key_buffer = 16M

  2. #2
    Join Date
    Oct 2015
    Posts
    9

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •