sqlboost

Registered
Oct 14, 2012
1
0
1
cPanel Access Level
Website Owner
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
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
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:

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)
Thanks for the help!