increasing mysql memory usage

MySQL & MariaDB Other MySQL® Questions
1

Hi,

My mysql 4.1 is only using 4-5% of the 8gig memory. Could anyone please help me increase that to using maximum available memory. My DB is about 4gig now and some queries take long time to execute.

Does it matter if some tables in the DB are MyISAM and some are InnoDB? would that effect the speed of the DB since I have some tables MyISAM and some InnoDB.

Thanks in advance

My conf file looks something like this.

[mysqld]
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 8

old_passwords=1
skip-name-resolve
bind-address=123.123.123.1
log-slow-queries = mysql-slow-query.log
long_query_time = 10

Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2: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 = 384M
#innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 100M
#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 = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout

2

Its ok to use both MyISAM and InnoDB, as long as you don’t try to use MyISAM tables during a multi-statement transaction.

As far as memory goes, MyISAM only uses memory to cache indexes, while InnoDB will cache indexes and data. As it should be obvious from your config file, they also use different parameters for tuning their memory use. Without knowing what you use the InnoDB vs. MyISAM for, its hard to say where you should put the memory you have.

Since you’re posting on these forums, you should try looking at the Percona blog for some tips. Here are a few posts that might be useful:

http://www.mysqlperformanceblog.com/2006/05/17/mysql-server- memory-usage/

http://www.mysqlperformanceblog.com/2007/11/01/innodb-perfor mance-optimization-basics/