MySQL Innodb - Performance

Discussion in 'Server Operation' started by guimnk, Mar 16, 2011.

  1. guimnk

    guimnk New Member

    Hi all!

    I've two machines running ubuntu 10.10 x64. The machines are very fast, HP Proliant DL380 G5 with 2 processors Xeon 2.66ghz and 16GB of RAM. They have 2 RAID's 1+0 (SAS disks). In the first raid I've installed the ubuntu and the second raid is a /var/lib/mysql directory.
    The replication is activated and ok, the informations are correct..
    My problem is the performance. I've a changed my.cnf and I want the opnion if this configuration is correct, because I've many slow queries.
    All of databases are tunned with index in all fields used in WHERE clauses. I'm using INNODB engine and only one database. The size of database is 7GB (.sql exported) and 12GB (/var/lib/mysql/ibdata1).
    I've tried to run mysqltunning.pl and tuning-primer.pl (I think), but those scripts only says "upgrade your memory / change the size of innodb_buffer_size". I've changed that, but the performance is still low.

    How can I do to setting up the correct changes to get more performance?

    This is my my.cnf:

    Code:
    
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    [mysqld]
    
    user = mysql
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    old_passwords=1
    
    log-bin
    binlog-do-db    = db1ERP
    binlog-do-db    = openfire
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    
    master-host = 192.168.1.38
    master-user = replication
    master-password = slave2
    master-port = 3306
    
    back_log = 50
    max_connections = 9000
    max_connect_errors = 10
    table_cache = 3072
    max_allowed_packet = 16M
    expire_logs_days = 2
    binlog_cache_size = 1M
    max_heap_table_size = 512M
    sort_buffer_size = 32M
    join_buffer_size = 2048M
    thread_cache_size = 8
    thread_concurrency = 10
    query_cache_size = 1024M
    query_cache_limit = 512M
    ft_min_word_len = 4
    default_table_type = INNODB
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 512M
    log-bin=mysql-bin
    general_log_file = /var/log/mysql.log
    log_warnings
    log_short_format
    server-id = 1
    key_buffer_size = 1024M
    read_buffer_size = 512M
    read_rnd_buffer_size = 16M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover
    innodb_autoextend_increment = 50
    innodb_additional_mem_pool_size = 32M
    innodb_buffer_pool_size = 8000M
    innodb_data_file_path = ibdata1:1024M:autoextend
    innodb_file_io_threads = 4
    innodb_thread_concurrency = 16
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 1000M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_flush_method=O_DIRECT
    innodb_lock_wait_timeout = 240
    
    
    [mysqldump]
    quick
    
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 512M
    sort_buffer_size = 512M
    read_buffer = 8M
    write_buffer = 8M
    
    [myisamchk]
    key_buffer = 512M
    sort_buffer_size = 512M
    read_buffer = 8M
    write_buffer = 8M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld_safe]
    open-files-limit = 8192
    
     
  2. falko

    falko Super Moderator

    Have you tried to increase query_cache_size?
     
  3. guimnk

    guimnk New Member

    To 4096M ?
     
  4. falko

    falko Super Moderator

    Just try it - I can't say if it helps.
     
  5. guimnk

    guimnk New Member

    I changed it today. I'll analyze and post here the result.

    Thanks falko
     

Share This Page