How to limit MySQL ram and CPU usage?

[PRB]

When one of my users imports their MySQL database (either through phpMyAdmin or through any other method) the server load just jumps up a lot. I have had to warn my users to split their database dumpfiles in many files so importing doesnt overload the server.

I have searched all over the internet. Whenever someone starts importing their mysql database, the server load goes to near to 30.00

my users say they have never experienced such a thing with other hosts, where they can import their databases without any problems at all. I mysql think that MySQL is allowed to use too much ram, I read that its possible to limit MySQL ram and cpu usage but i just dont know how to do it.

It is a QuadCore Intel(R) Core(TM) Duo CPU T2450 @ 2.00GHz
2GB RAM

My my.cnf file (which should be the file editted to fix this problem i think):
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/XRD006.local.pid
skip-locking
skip-innodb
safe-show-database
key_buffer=128M
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=64M
thread_cache_size=128
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
max_user_connections=200
max_connections=100
bind-address=127.0.0.1
interactive_timeout=10
wait_timeout=10
connect_timeout=20
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[safe_mysqld]
err-log=/var/log/mysqld.log

 

[Rich-Boy]

I haven't experimented with my.cnf much but you may or may not know about the example my.cnf that mysql provides.

There is a default my.cnf that comes with mysql (4+5) that will make mysql run a bit quicker if you have 2+ gig of ram

cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf
There is also my-huge.cnf, or my-medium.cnf depending on your hardware setup. Check the contents of these my*.cnf files for the one that's right for you.

Taken from http://help.directadmin.com/item.php?id=44

I am wondering though why it is that your users are importing databases as often as you describe? Is it that your users cannot complete a particular database import because the server load gets too high?

Also, which OS are you running? Depending on your answer there are some techniques you can use to limit how much memory and/or the mysql process uses.

 

[Michael.Terence]

It also may be helpful to know what the typical load average is. Not overnight, or your 24 hour load average... real world, server is serving pages at its normal rate load average.

I see you have the binary log enabled... if you're not replicating to another server and you're using DA backups disable the bin logging as that's just more IO and will cause additional load (if importing a large amount of data the additional load can be substantial).

What kind of IO wait are you seeing during normal operation, and what does it go up to while importing?

 

[Michael.Terence]

The my.cnf you posted doesn't look bad - have you tuned it with mysqltuner? If not check it out: http://wiki.mysqltuner.com/

I wouldn't use my-large.cnf on a server with 2 gigs of ram, especially if you're running apache. Personally, I would start with the my.cnf you have and tune it for your system with mysqltuner. If you feel like you need to start over, you can try my-medium.cnf and hit it with mysqltuner to see what the max possible ram usage would be and go from there.

I tend to cache the hell out of mysql (well everything I can - lol) and by the time I'm done it eats up 4 gigs of ram. Sure makes things fast.