WEB Anyone know how to optimize MySQL?

Discussion in 'OT Technology' started by Vatoloco, Dec 16, 2007.

  1. Vatoloco

    Vatoloco It's not irreversible? Really?

    Joined:
    Jul 16, 2001
    Messages:
    89,744
    Likes Received:
    975
    Location:
    @Vatoloco_OT
    My server bogs down during peak hours (about 4pm to 8pm) and mysqld is always the first process listed in top (both by cpu and memory usage). I don't really know what I'm doing when it comest to server optimization but I took some suggestions from an old post I found on a different forum and came up with the following my.cnf. I've been running it for about a week now and haven't noticed any major improvents.

    Code:
    [mysqld]
    tmpdir=/backup/mysqltmp
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    #old_passwords=1
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=500
    interactive_timeout=100
    wait_timeout=7200
    connect_timeout=10
    thread_cache_size=128
    key_buffer=150M
    join_buffer_size=1M
    max_allowed_packet=16M
    table_cache=1500
    read_buffer_size=1M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=768K
    max_connect_errors=10
    thread_concurrency=4
    myisam_sort_buffer_size=64M
    server-id=1
    
    [mysql.server]
    user=mysql
    basedir=/var/lib
    old-passwords = 1
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    MySQL client version: 4.1.22


    The server I'm running this on is:
    Kentsfield Xeon 3210 2.13GHz Quad Core
    4GB DDR2 667 ECC RAM
    Dual 73GB 15kRPM SCSI HDD's (/var/lib/mysql is partitioned on its own drive)


    Info from phpMyAdmin:

    [​IMG]



    Anyone notice any changes I could make or major issues I might have with the settings in my.cnf?
     
    Last edited: Dec 21, 2007
  2. Dnepr

    Dnepr Guest

    You should REALLY look into optimizing your select/update/delete/insert queries.


    Read this website, one of my favourites, for SQL performance improvements (it is mainly for SQL Server, but there are very good principles which can be applied to most SQL queries there):
    http://www.sql-server-performance.com/
     
    Last edited by a moderator: Dec 16, 2007
  3. Nakon

    Nakon Guest

    Take a look at your queries first and if performance doesn't improve then look into fudging with the server.
     
  4. Vatoloco

    Vatoloco It's not irreversible? Really?

    Joined:
    Jul 16, 2001
    Messages:
    89,744
    Likes Received:
    975
    Location:
    @Vatoloco_OT
    I'm just running forum software (Invision Power Board). The last update I did was to a version that was suppose to be much more optimized for performance, but who knows... maybe it is time to switch to vBulletin because I don't know enough about this to figure out how to change all the select/update/etc queries.


    I'm not even sure what change db is. :o All my table types are MyISAM with the exception of one which is HEAP. It was recommended I switch the forum sessions table to HEAP for performance gains. Could change db have something to do with that?
     
  5. Dnepr

    Dnepr Guest

    Sorry ignore my remark about change db. I'm not a mysql guru that could mean lots of things in that context.
     
  6. Vatoloco

    Vatoloco It's not irreversible? Really?

    Joined:
    Jul 16, 2001
    Messages:
    89,744
    Likes Received:
    975
    Location:
    @Vatoloco_OT
    Well, I found a mysql tuning script here: http://www.day32.com/MySQL/

    It has suggested this:

    -- MYSQL PERFORMANCE TUNING PRIMER --
    - By: Matthew Montgomery -

    MySQL Version 4.1.22-standard i686

    Uptime = 5 days 16 hrs 44 min 28 sec
    Avg. qps = 107
    Total Questions = 52708775
    Threads Connected = 9

    Server has been running for over 48hrs.
    It should be safe to follow these recommendations

    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service

    SLOW QUERIES
    Current long_query_time = 10 sec.
    You have 3529 out of 52708791 that take longer than 10 sec. to complete
    The slow query log is NOT enabled.
    Your long_query_time may be too high, I typically set this under 5 sec.

    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 119
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine

    MAX CONNECTIONS
    Current max_connections = 500
    Current threads_connected = 10
    Historic max_used_connections = 292
    The number of used connections is 58% of the configured maximum.
    Your max_connections variable seems to be fine.

    MEMORY USAGE
    Max Memory Ever Allocated : 1 G
    Configured Max Per-thread Buffers : 1 G
    Configured Max Global Buffers : 192 M
    Configured Max Memory Limit : 2 G
    Physical Memory : 3.95 G
    Max memory limit seem to be within acceptable norms

    KEY BUFFER
    Current MyISAM index space = 1 G
    Current key_buffer_size = 150 M
    Key cache miss rate is 1 : 1601
    Key buffer fill ratio = 100.00 %
    You could increase key_buffer_size
    It is safe to raise this up to 1/4 of total system memory;
    assuming this is a dedicated database server.

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 7 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 23.18 %
    Current query_cache_min_res_unit = 4 K
    Query Cache is 33 % fragmented
    Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
    If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size

    SORT OPERATIONS
    Current sort_buffer_size = 1 M
    Current record/read_rnd_buffer_size = 764 K
    Sort buffer seems to be fine

    JOINS
    Current join_buffer_size = 1.00 M
    You have had 1 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.

    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.

    OPEN FILES LIMIT
    Current open_files_limit = 3510 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine

    TABLE CACHE
    Current table_cache value = 1500 tables
    You have a total of 136 tables
    You have 1451 open tables.
    Current table_cache hit rate is 89%, while 96% of your table cache is in use
    You should probably increase your table_cache

    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 32 M
    Of 45062 temp tables, 5% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Created disk tmp tables ratio seems fine

    TABLE SCANS
    Current read_buffer_size = 1020 K
    Current table scan ratio = 127 : 1
    read_buffer_size seems to be fine

    TABLE LOCKING
    Current Lock Wait ratio = 1 : 38
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'


    I'm going to give those suggestions a try... once I figure out the exact variables, whether or not they'll conflict with existing ones and where to add them in my.cnf anyways. :)
     
  7. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    One quick thing I would do is up your thread_concurrency to 8, since you are on a quad core box.

    The other thing I would make sure is running is optimize and analyze on your tables possibly once a day depending on the amount of changes you are making to the tables.
     
  8. Vatoloco

    Vatoloco It's not irreversible? Really?

    Joined:
    Jul 16, 2001
    Messages:
    89,744
    Likes Received:
    975
    Location:
    @Vatoloco_OT
    Thanks. I didn't even realize thread concurrency was suppose to be double your cpu's.

    I optimize the tables about once a week when I do my offsite backups. I'm not sure how much that helps but maybe I do need to setup a script to automatically do it every night.


    I've made all these changes so far to my.cnf:
    Code:
    [mysqld]
    tmpdir=/backup/mysqltmp
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-bdb
    skip-innodb
    
    query_cache_limit=1M
    query_cache_size=16M
    query_cache_type=1
    
    max_connections=500
    max_user_connections=500
    interactive_timeout=100
    wait_timeout=100
    long_query_time=5
    connect_timeout=10
    
    thread_cache_size=128
    key_buffer=128M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=2048
    tmp_table_size=64M
    max_heap_table_size=32M
    record_buffer=1M
    sort_buffer_size=2M
    key_buffer_size=256M
    read_buffer_size=1M
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    read_rnd_buffer_size=2MB
    
    server-id=1
    #concurrent_insert=2
    low_priority_updates=1
    
    [mysql.server]
    user=mysql
    basedir=/var/lib
    old-passwords = 1
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit=8192
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    I'll let it run like this for another couple days and see how it does.
     
  9. crazybenf

    crazybenf Well-Known Member

    Joined:
    Nov 14, 2001
    Messages:
    15,616
    Likes Received:
    48
    bump your query cache up to 712MB+
     
  10. Vatoloco

    Vatoloco It's not irreversible? Really?

    Joined:
    Jul 16, 2001
    Messages:
    89,744
    Likes Received:
    975
    Location:
    @Vatoloco_OT
    Would that be query_cache_size? And if so, do I need to also change the query_cache_limit or query_cache_type?
     
  11. biawokauns

    biawokauns Active Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    optimize your queries to the db
     

Share This Page