GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

increasing mysql memory usage

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • increasing mysql memory usage

    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/

    Comment

    Working...
    X