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
Mariadb how can we speed up insert
-
- New Forum User
- Posts: 1
- Joined: Sat Oct 17, 2015 5:52 am
- Location: Ankara
Re: Mariadb how can we speed up insert
At the least, disable the general query log!
Further optimisations really depend on the type of work load, hardware specs and storage engine.
Further optimisations really depend on the type of work load, hardware specs and storage engine.
Lemonbit Internet Dedicated Server Management