store | blogs | forums | twitter | facebook | wiki | downloads | support portal
Atomic Secure Linux
It is currently Sat Oct 19, 2019 7:25 pm

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Mariadb how can we speed up insert
Unread postPosted: Sat Oct 17, 2015 5:54 am 
Offline
New Forum User
New Forum User

Joined: Sat Oct 17, 2015 5:52 am
Posts: 1
Location: Ankara
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Mariadb how can we speed up insert
Unread postPosted: Tue Oct 20, 2015 6:35 am 
Offline
Forum Regular
Forum Regular

Joined: Tue Aug 01, 2006 2:45 pm
Posts: 573
Location: Netherlands
At the least, disable the general query log!

Further optimisations really depend on the type of work load, hardware specs and storage engine.

_________________
Lemonbit Internet Dedicated Server Management


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group