Hi guys,
I'm importing 50 large .csv files (200MB/each).
I Would like to dedicate all resources and utilize ALL available memory / CPU on my server to get this task done ASAP!
Processor Information: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 8 cores
Memory: 7GB
#top
mysqld memory usage is currently at around 23.4%. I would like to increase this to close to 100%.
Here is my "my.cnf" file:
MySQLtuner
Thanks for the help!
I'm importing 50 large .csv files (200MB/each).
I Would like to dedicate all resources and utilize ALL available memory / CPU on my server to get this task done ASAP!
Processor Information: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 8 cores
Memory: 7GB
#top
Code:
top - 23:32:33 up 2 days, 19:35, 1 user, load average: 0.32, 0.33, 0.17
Tasks: 30 total, 1 running, 26 sleeping, 3 stopped, 0 zombie
Cpu0 : 1.1%us, 0.0%sy, 0.0%ni, 98.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 52.8%us, 9.9%sy, 0.0%ni, 36.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.7%st
Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 7340032k total, 5180964k used, 2159068k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 3250248k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2241 mysql 20 0 8313m 1.6g 6324 S 57.8 23.4 0:09.45 mysqld
7243 apache 20 0 317m 23m 3960 S 0.0 0.3 1:00.84 httpd
Here is my "my.cnf" file:
Code:
#1300 / minute
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
skip-external-locking
skip-networking
skip-name-resolve
default-table-type=innodb
symbolic-links=0
#########################
# max connections
#########################
max_connections = 80
#########################
# Increase until memory usage is at 90%
#########################
tmp_table_size = 128M
max_heap_table_size = 128M
#########################
# Table Cache - Set to 20% higher than the number of tables on server (>64)
#########################
table_cache = 100
table_definition_cache = 400
#3x the table_cache
open_files_limit = 300
#########################
# Query Cache
#########################
query_cache_type=1
query_cache_size = 128M
query_cache_limit = 148M
##########################
# Thread Cache
##########################
thread_cache_size = 16K
max_allowed_packet = 24M
##########################
# Buffer - 20% of _all_ of RAM, but not more than 3G
##########################
key_buffer=64M
key_buffer_size=512M
##########################
# InnoDB Settings
##########################
thread_concurrency = 16
low_priority_updates=1
concurrent_insert=1
# buffer_pool_size to 80% of your total RAM
innodb_file_per_table
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 512M
# Set _log_file_size to 25% of buffer pool size
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
# Write logging is set to "Flush every second"
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
##########################
# Slow query log
##########################
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
MySQLtuner
Code:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 12s (16 q [1.333 qps], 10 conn, TX: 9K, RX: 896)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 6.2G global + 2.7M per thread (80 max threads)
[!!] Maximum possible memory usage: 6.5G (92% of installed RAM)
[OK] Slow queries: 0% (0/16)
[OK] Highest usage of available connections: 1% (1/80)
[OK] Key buffer size / total MyISAM indexes: 512.0M/4.5M
[!!] Query cache efficiency: 0.0% (0 cached / 7 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 3 total)
[OK] Thread cache hit rate: 90% (1 created / 10 connections)
[OK] Table cache hit rate: 53% (8 open / 15 opened)
[OK] Open file limit used: 3% (17/490)
[OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)
[!!] Connections aborted: 20%
[OK] InnoDB data size / buffer pool: 301.3M/4.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 148M, or use smaller result sets)