Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    346

    Question mysql optimization, xenon quad core, 4GB ram...I need help...

    I trying to move my sites from old server with this spec:
    dual core D 930 3GHz
    2GB RAM
    mysql 4.1.22

    to new server:
    Xenon quad core X3230 2.66GHz
    4GB RAM
    mysql 5.0.81

    but I have problem, after I moved two largest sites, one of them is with mysql table with 1600000 rows and 300MB (with index) look like all working better ON OLD SERVER, now I have load 2-3+ but on old server with all sites (few other smaller and dns server etc. and with bad HD) all worked 2 years with load most time under 1...on new server mysql killing all with high cpu usage...I don`t know how this can be possible, all is same, same DB setup (copy), same php scripts and mysql queries...all is same and only new server with better cpu (need to be), 2xRAM, better hd (2x faster on hdparm -t test), new mysql version, new red hat 5, new php, new apache, all must be faster but that is not case, mysql killing server...


    What you thinking, why?

    This is my.cnf on new server:

    [mysqld]
    set-variable = max_connections=5000
    safe-show-database
    skip-innodb
    key_buffer_size = 500M
    myisam_sort_buffer_size = 128M
    join_buffer_size = 4M
    read_buffer_size = 4M
    sort_buffer_size = 8M
    table_cache = 4000
    thread_cache_size = 200
    wait_timeout = 20
    connect_timeout = 10
    tmp_table_size = 500M
    max_allowed_packet = 16M
    max_connect_errors = 10000
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    query_prealloc_size = 16384
    query_alloc_block_size = 16384
    skip-name-resolv
    skip-external-locking
    skip-locking
    read_rnd_buffer_size=256M
    max_heap_table_size=256M
    thread_concurrency=8


    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 256M
    sort_buffer = 256M
    read_buffer = 64M
    write_buffer = 64M

    First try was with same my.cnf like on old server:

    [mysqld]
    set-variable = max_connections=5000
    safe-show-database
    skip-innodb
    key_buffer_size = 384M
    myisam_sort_buffer_size = 128M
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 4M
    table_cache = 3672
    thread_cache_size = 200
    wait_timeout = 20
    connect_timeout = 10
    tmp_table_size = 356M
    max_allowed_packet = 16M
    max_connect_errors = 10000
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    query_prealloc_size = 16384
    query_alloc_block_size = 16384
    skip-name-resolv
    read_rnd_buffer_size=2M
    max_heap_table_size=256M
    old-passwords = 1

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer = 128M
    sort_buffer = 128M
    read_buffer = 16M
    write_buffer = 16M


    but all changing (many versions) look like don`t changing a lot...mysql using 100-200-300% cpu (all threads) and load most time is 2+....

    I have few main queries for this DB with 1600000 rows:
    get last 10 inserts
    get between (return 100 rows)
    update one row - one column +1


    I hope that somebody can help...


    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,681
    are you using 32bit or 64bit?
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  3. #3
    Join Date
    Feb 2006
    Location
    Buffalo, NY
    Posts
    1,501
    Also is the huge table MyISAM or InnoDB (Steven knows where I'm going with this ).
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Official Let's Encrypt Sponsor

  4. #4
    Join Date
    Feb 2008
    Location
    Houston, Texas, USA
    Posts
    3,262
    Quote Originally Posted by CodyRo View Post
    Also is the huge table MyISAM or InnoDB (Steven knows where I'm going with this ).
    Having MyISAM doesn't mean high CPU. It just means an artificially high load average because of a deep run queue. Although it can help lower the load average, again artificially in no meaningful way. Plus, OP mentioned that the legacy DB wasn't loaded with the innodb engine.

    OP, try rebuilding your indexes. Simply run a quick repair against your table. See if this helps.

    Regards
    UNIXy - Fully Managed Servers and Clusters - Established in 2006
    Server Management - Unlimited Servers. Unlimited Requests. One Plan!
    cPanel Varnish Plugin -- Seamless SSL Caching (Let's Encrypt, AutoSSL, etc)
    Slow Site or Server? Unable to handle traffic? Same day performance fix: joe@unixy

  5. #5
    Join Date
    Feb 2006
    Location
    Buffalo, NY
    Posts
    1,501
    Quote Originally Posted by UNIXy View Post
    Having MyISAM doesn't mean high CPU. It just means an artificially high load average because of a deep run queue. Although it can help lower the load average, again artificially in no meaningful way. Plus, OP mentioned that the legacy DB wasn't loaded with the innodb engine.

    OP, try rebuilding your indexes. Simply run a quick repair against your table. See if this helps.

    Regards
    I was referencing InnoDB based on the original topic (tweaking my.cnf) .

    I didn't notice the InnoDB reference though, whoops!
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Official Let's Encrypt Sponsor

  6. #6
    Join Date
    Aug 2004
    Posts
    346
    32 bit, MyISAM....look like quick repair don`t helping...

    Simple query:
    SELECT * FROM table WHERE ID = '2094842' LIMIT 1

    now using over 1 second up to 2 seconds...ID is primary key...very bad...I know this is because all working and load is ~2 but...
    Last edited by gurika; 10-13-2009 at 05:47 AM.

  7. #7
    Join Date
    Dec 2006
    Posts
    480
    Is the query slow because of high cpu or because of locking from other threads simultaneously accessing the tables. If you have updates and selects running off the same myisam table, the entire table is locked while an update runs and you can often get a performance gain from using innodb.

    If its not locking, check if your table row format is fixed or dynamic (show table status will tell you). If its dynamic and you have a lot of queries that perform filters or joins on the fixed sized fields but not the variable, sometimes you can make the overall application faster by splitting it into 2 tables - one with the ID and the variable sized fields and one with the ID and the fixed sized fields and then adding joins to the queries that need both sets. Although it sounds like adding more work to the db, the fixed field table is usually a lot smaller and can be scanned quickly to isolate which rows are to be returned and then only those few rows need to be hit on the slower dynamic row table. Also if the fields being updated are in the fixed field table, updates will run faster.

  8. #8
    Join Date
    Aug 2004
    Posts
    346
    After testing now I know what is main problem, what killing whole server, main problem is simple update query:

    update table set number=number+1 where ID=some_number

    Look like something is not good with table update/write, when I disabled this query server load is now 0.1 ...

    Is this problem on mysql 5 or can be problem something else? This worked solid on old server and now I have better cpu, better HD, 2x ram etc. and also now I have problem with mysql update/write...somebody know what can be problem!?

    Thanks.

  9. #9
    Join Date
    Aug 2004
    Posts
    346
    After all testing look like main problem is table locking on write/update like RBBOT after I converted that DB to innodb all working 10x better but I don`t know how all worked on old server, maybe mysql 4.1 working better in this case or something else...

    Also, one other DB I split to two, one myisam (for full text search) and one for writing/update and all working better, size of this DB is over GB....

    Thanks.

Similar Threads

  1. Replies: 10
    Last Post: 11-12-2008, 10:33 AM
  2. Replies: 0
    Last Post: 08-13-2008, 10:49 PM
  3. Replies: 0
    Last Post: 07-21-2008, 02:37 PM
  4. Replies: 2
    Last Post: 02-04-2008, 06:53 AM
  5. Replies: 8
    Last Post: 12-19-2007, 07:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •