The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Optimization...

Discussion in 'General Discussion' started by Nhojohl, Jan 17, 2007.

  1. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    I'm running around 19 total sites on one of my servers, only one of which is extremely resource intensive...

    The one site gets a large number of hits and is CONSTANTLY making long queries and updating data, the server load usually goes up above 2 with over 30 users online at a time.

    How would I go about optimizing my.cnf to help keep it down?

    Here's the contents of my current my.cnf
    Code:
    [mysqld]
    skip-locking
    skip-bdb
    skip-innodb
    query_cache_limit=8M
    query_cache_size=8M
    query_cache_type=1
    max_connections=500
    max_user_connections=500
    interactive_timeout=20
    wait_timeout=6
    long_query_time = 5
    connect_timeout=6
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=2048
    tmp_table_size=256M
    record_buffer=1M
    sort_buffer_size=2M
    key_buffer_size=8M
    read_buffer_size=2M
    max_connect_errors=10
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    server-id=1
    log-slow-queries = /var/log/mysql/mysql-slow.log
    concurrent_insert=2
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    After an hour of running MySQL and running a cool script to get your info I got this...

    Code:
    SLOW QUERIES
    Current long_query_time = 5 sec.
    You have 0 out of 11313288 that take longer than 5 sec. to complete
    The slow query log is NOT enabled.
    Your long_query_time seems to be fine
    
    MAX CONNECTIONS
    Current max_connections = 500
    Current threads_connected = 1
    Historic max_used_connections = 6
    The number of used connections is 1% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 5
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MEMORY USAGE
    Max Memory Ever Allocated : 51 M
    Configured Max Memory Limit : 2117 M
    Total System Memory : 3065 M
    
    KEY BUFFER
    Current MyISAM index space = 10 M
    Current key_buffer_size = 8 M
    Key cache miss rate is 1 / 8264
    Key buffer fill ratio = 47.00 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 8 M
    Current query_cache_used = 2 M
    Current Query cache fill ratio = 27.91 %
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current record/read_rnd_buffer_size = 256.00 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 1020.00 K
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly
    
    TABLE CACHE
    Current table_cache value = 2048 tables
    You have a total of 872 tables
    You have 884 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current tmp_table_size = 256 M
    57% of tmp tables created were disk based
    Perhaps you should increase your tmp_table_size
    
    TABLE SCANS
    Current read_buffer_size = 1 M
    Current table scan ratio = 58 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 1991
    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'
    If you have a high concurrentcy of inserts on Dynamic row-lenght tables consider setting 'concurrent_insert=2'
    Thanks!:cool:

    EDIT: Should have added, server is

    Processor #1 Vendor: GenuineIntel
    Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #1 speed: 2814.860 MHz
    Processor #1 cache size: 512 KB

    with 1GB of ram, spamAssassin is disabled along with Box trapper, ASSP is installed. CSF firewall and LFD. The security check also shows that it's a 68/70. The only things not done are phpSuEXEC and no disabled functions in PHP.

    Also, eaccelerator:
    Code:
    eAccelerator support
    enabled	Version 	0.9.5 
    Caching Enabled 	true 
    Optimizer Enabled 	true 
    Memory Size 	 	16,777,180 Bytes 
    Memory Available 	9,887,112 Bytes 
    Memory Allocated 	6,890,068 Bytes 
    Cached Scripts  	109 
    Removed Scripts 	0
    Cached Keys 	 	0 
     
    #1 Nhojohl, Jan 17, 2007
    Last edited: Jan 17, 2007
  2. dempsey

    dempsey Active Member

    Joined:
    Jul 17, 2003
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    A bit offtopic, but what was the script you used to get that info about your mysql connections, it looks very useful :)
     
  3. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    I put it in one of my sites directories for you... I also did a lot of browsing and tweaking and got my server running fast as it ever has...For anyone who cares its info is after the script...

    Code:
    wget http://www.day32.com/MySQL/tuning-primer.sh
    chmod 0644 tuning-primer.sh
    ./tuning-primer.sh
    Code:
    Processor #1 Vendor: GenuineIntel
    Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.80GHz
    Processor #1 speed: 2814.860 MHz
    Processor #1 cache size: 512 KB
    
    1024 MB RAM
    my.cnf contents...
    Code:
    [mysqld]
    skip-locking
    skip-bdb
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=500
    max_user_connections=500
    interactive_timeout=20
    wait_timeout=6
    long_query_time = 5
    connect_timeout=6
    thread_cache_size=80
    key_buffer=128M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=2048
    tmp_table_size=512M
    record_buffer=1M
    sort_buffer_size=1M
    key_buffer_size=8M
    read_buffer_size=1M
    max_connect_errors=10
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    read_rnd_buffer_size=768K
    server-id=1
    log-slow-queries = /var/log/mysql/mysql-slow.log
    concurrent_insert=2
    low_priority_updates=1
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
     
    #3 Nhojohl, Jan 18, 2007
    Last edited: May 30, 2007
  4. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
  5. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    Thanks:) - Appreciate it:D
     
  6. procam

    procam Well-Known Member

    Joined:
    Nov 24, 2003
    Messages:
    123
    Likes Received:
    0
    Trophy Points:
    16
  7. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
    regardless of where one gets a script, one should review the code and what it does before running it.

    While someone may have a poor server configuration, it doesn't mean they didn't come up with a gem of a script.

    I wonder when they'll realize their sales are slipping? :P

    Keith
     
  8. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    I like the script, it's honestly helped. I'm now running my Pentium 4 2.8Ghz with a 1024k cache and 1024 MB of ram with the following my.cnf:

    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    safe-show-database
    bind-address=127.0.0.1
    log-slow-queries=/var/log/mysql_slow_query.log
    skip-locking
    skip-bdb
    skip-innodb
    
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    
    max_connections=500
    max_user_connections=500
    interactive_timeout=20
    wait_timeout=6
    long_query_time=5
    connect_timeout=6
    
    thread_cache_size=80
    key_buffer=128M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=2048
    tmp_table_size=512M
    record_buffer=1M
    sort_buffer_size=1M
    key_buffer_size=6M
    read_buffer_size=1M
    max_connect_errors=10
    thread_concurrency=2
    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
    
    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    [mysql]
    no-auto-rehash
    
    [myisamchk]
    key_buffer=32M
    sort_buffer=32M
    read_buffer=16M
    write_buffer=16M
    
    [mysqldump]
    quick
    max_allowed_packet=16M
     
  9. katmai

    katmai Well-Known Member

    Joined:
    Mar 13, 2006
    Messages:
    526
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Brno, Czech Republic
    im gonna give it a try as well.
     
  10. TodoInTX

    TodoInTX Registered

    Joined:
    Feb 7, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Response from script writer

    Hello Folks... I'm glad to see my script getting some play out and about. There have been some bugfixes in the past couple of days and I recently have added support for FreeBSD and MacOS.

    I'm very open to feedback/patches to improve this script if anyone has some ideas.

    The problems mentioned earlier about about my pricing page are largely because I've given up on whole the virtual hosting thing. I just let that part of the site die since joining MySQL. That page been taken down now.

    For other community projects that you may consider helpful see:
    http://forge.mysql.com/

    Thanks!!!

    Matt
     
  11. oulzac

    oulzac Well-Known Member

    Joined:
    Aug 7, 2005
    Messages:
    131
    Likes Received:
    0
    Trophy Points:
    16
    I have a couple questions about this, I am getting this when running your script:

    Code:
    MEMORY USAGE
    Max Memory Ever Allocated : 67 M
    Configured Max Memory Limit : 4 G
    Total System Memory : 7 G
    I have it set for 1G why is it only saying 67M

    Code:
    KEY BUFFER
    Current MyISAM index space = 758 M
    Current key_buffer_size = 16 M
    Key cache miss rate is 1 : 9
    Key buffer fill ratio = 1.00 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 1 M
    Current Query cache fill ratio = 5.75 %
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    How is that too high? should I lower them, or just leave them?

    Code:
    TEMP TABLES
    Current tmp_table_size = 1.00 G
    53% of tmp tables created were disk based
    Perhaps you should increase your tmp_table_size
    How in the heck is that 53% used?
     
  12. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    Max Memory Ever Allocated is the maximum memory it has ever actually used, it's not the cap.

    Well, key buffer, you're currently only using 1% of the memory you allocated to it...
    The query cache, this just means that of 32M you've allocated you've only used 1M, basically you're allocating 32 times the amount of memory you actually need.

    Since these values are so small you probably wouldn't need to modify them, however, I'd try setting the query cache size to something like 4M or 8M.

    Code:
    TEMP TABLES
    Current tmp_table_size = 1.00 G
    53% of tmp tables created were disk based
    Perhaps you should increase your tmp_table_size
    How in the heck is that 53% used?[/QUOTE]
    I'm not 100% sure on this one, I just leave mine at 2048M and it gives me a green light. (My box only has 1GB so I'm not sure how that one works out)


    Hope this helps... I've been running the last config I posted for a week or more now and it's like a frigging rocket.


    EDIT: Forgot to mention, be sure you are running this after mySQL has been running for a day or two... otherwise the values are more than likely going to be way off of what you actually use.
     
  13. oulzac

    oulzac Well-Known Member

    Joined:
    Aug 7, 2005
    Messages:
    131
    Likes Received:
    0
    Trophy Points:
    16
    I think this is my problem, I rebooted right after making my.cnf chagnes.
     
  14. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
    you're not reading the output of the script!!!
    first section.. MAX EVER ALLOCATED is different from MAX MEMORY LIMIT!

    as I read this, your total mysql memory usage at any moment in time maxed out at 67M
    you're allowing mysql to allocate 4G of ram.. this would seem to indicate you're overallocating memory. mysql only needs 67M and you're tying up 4G for it.

    secton 2.. key buffer:
    you have 758M of indexes, you're allocating 16M to buffer the index keys
    you're using 1% of the key buffer size.. therefor it might be you're allocating too much since you're only using 1%
    same with query cache.

    the recommendation to lower them, I'm guessing, is that you're overallocating which is uneccessary. save the ram for something else. however, given how much memory you have, you're not killing yourself here.. but all these numbers factor into the MAX MEMORY LIMIT number... which you're definitely not making use of.

    I'm not sure what causes temp tables to be used or disgarded, but it appears however your db's are being utilized, it's causing mysql to generate a LOT of temp tables.. so much so it exceeds the 1G you've allocated for them..

    if you're after an overall performance improvement on your server in general, I'd look to use less memory for mysql and use that memory as tmpfs for /tmp

    since most webservers generate a lot of /tmp files (especially if you're using mod_gzip), this alone will make a HUGE improvement in webserver throughput.
    your db seems way overallocated.

    my .02

     
  15. TodoInTX

    TodoInTX Registered

    Joined:
    Feb 7, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    This is a bug in the script, I have recognized there is a problem with the TEMP TABLE checks. Before version 1.3.0 the tuning script did not take into consideration that in memory tmp tables are also limited by max_heap_table_size which is 16M by default. Raising tmp_table_size without also raising max_heap_table_size is ineffective. Also any tmp table that contains BLOB/TEXT data types will always be created on disk.
     
  16. erick_paper

    erick_paper Well-Known Member

    Joined:
    Apr 19, 2005
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    I actually found your script quite handy as it doesn't actually change anything, just advises. My output said "You may consider using InnoDB tables selectively". Which is exactly what I have been thinking for a table that is updated very frequently.

    My questions:

    1. How to turn InnoDB on? The following code does not work for me (I just picked it off the web) --

    Code:
    #skip-innodb
    innodb_buffer_pool_size=1G
    # Set the log file size to about 25% of the buffer pool size
    innodb_log_file_size=250M
    innodb_log_buffer_size=8M
    innodb_data_file_path=idbdata1:200M:autoextend
    innodb_additional_mem_pool_size=20M
    innodb_log_group_home_dir = /iblogs
    innodb_log_files_in_group = 2
    innodb_flush_log_at_trx_commit=1
    innodb_lock_wait_timeout=24
    
    2. Will your script also analyze the performance of my InnoDB settings?
     
    #16 erick_paper, Jun 5, 2007
    Last edited: Jun 5, 2007
  17. Nhojohl

    Nhojohl Well-Known Member

    Joined:
    Nov 28, 2006
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    I'm honestly unfamiliar with InnoDB but have been thinking the same things as you... I can however point out that the code you pasted:

    Code:
    #skip-innodb
    innodb_buffer_pool_size=1G
    # Set the log file size to about 25% of the buffer pool size
    innodb_log_file_size=250M
    innodb_log_buffer_size=8M
    innodb_data_file_path=idbdata1:200M:autoextend
    innodb_additional_mem_pool_size=20M
    innodb_log_group_home_dir = /iblogs
    innodb_log_files_in_group = 2
    innodb_flush_log_at_trx_commit=1
    innodb_lock_wait_timeout=24
    Possibly isn't working because you need to start and /or compile MySQL with the '--with-innodb' command.

    Also, I'm not sure if your hints about "my" script were pointed at me but the script is actually not mine its Matthew Montgomery's, you can find the newest version here:
    http://www.day32.com/MySQL/tuning-primer.sh
     
    #17 Nhojohl, Jun 5, 2007
    Last edited: Jun 5, 2007
  18. foussa

    foussa Member

    Joined:
    Jun 1, 2006
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
     
  19. nabuhonodozor

    nabuhonodozor Member

    Joined:
    Jun 22, 2007
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    1
    Thanks Nhojohl for great script.
    best regards,
    Piotr
     
  20. abdoh2010

    abdoh2010 Active Member

    Joined:
    Jan 9, 2005
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    1
    great script and for me it's initial

    for the people who had problem with the script i advice them to try to change chmod from 0644 to 0777 and it will work
     
Loading...

Share This Page