Announcement

Announcement Module
Collapse
No announcement yet.

optimize mysql apache for best performance

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

  • optimize mysql apache for best performance

    Hi,
    I have a dedicated box in which i run the app and the db server with 16GB memory and below server config
    HP DL 120 G7, Single Proc Quad Core
    - Intel Xeon E1220 (1 CPU x 4 Cores)
    - 500 GB x 2 SATA Hard Disks on Raid 1
    - 16 GB DDRII ECC Registered RAM

    I still am not able to spike up my visitors where as just 6 months back the same server was able to show me 15000+ visitors each day and today i am not able to get more then 5000 visitors...

    Mysql CPU load spikes at times to 200% and also the load average goes upto 80 at times

    i forgot to mention i have myisam and INNODB tables major myisam

    below is my.cnf entries
    [mysqld]

    local-infile=0
    skip-name-resolve
    max_connections=400
    low_priority_updates=1
    myisam-recover=backup,force
    thread_concurrency=8
    concurrent_insert=2
    thread_cache_size=48
    max_allowed_packet=8M

    innodb_buffer_pool_size=512M
    innodb_additional_mem_pool_size=10M
    innodb_flush_method=O_DIRECT

    symbolic-links=0
    socket=/var/lib/mysql/mysql.sock

    interactive_timeout = 100
    connect_timeout = 60
    wait_timeout = 60

    table_cache=2048
    table_definition_cache =1024
    tmp_table_size = 150M
    max_heap_table_size = 150M
    join_buffer_size=2M
    read_buffer_size=128K
    sort_buffer_size=2M
    table_open_cache=1024
    read_rnd_buffer_size=256K
    key_buffer =1G
    max_allowed_packet=8M
    max_connect_errors=10
    myisam_sort_buffer_size=512M
    query_cache_limit=200M
    query_cache_size=1G
    query_cache_type=1

    slow_query_log=1
    slow_query_log_file = mysql-slow.log
    long_query_time=10
    log-queries-not-using-indexes
    open_files_limit=4478
    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer=128M
    sort_buffer=128M
    read_buffer=4M
    write_buffer=2M

    [myisamchk]
    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer = 4M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout

  • #2
    query_cache_size=1G - way too big
    innodb_buffer_pool_size=512M - why so small?

    Try to investigate what the MySQL is doing during it's resource utilization is very high - watch processlist, show engine innodb status, etc.
    Which tables are most used ones, MyISAM or InnoDB? Any reason to have MyISAM at all?

    Comment

    Working...
    X