Results 1 to 21 of 21
  1. #1
    Join Date
    Apr 2006
    Posts
    520

    High load using mysqlump utilty

    Hello,

    I have a big vBulletin forum, with about 4-5k users online average, reaching max peaks of 8k online - 3.000.000 messages, about 100k registered users, and growing... This is the server structure:

    1 Webserver (Xeon 3.2 x4 procs, 4 GB RAM, 2 x 250 GB SATA, plain RHEL 5.4)
    1 MySQL Server (Xeon 3.2 x4 procs 4GB RAM, 2 x 250 GB SATA, plain RHEL 5.4)
    1 Images/Template Server (Xeon 3.2 x4 procs 4GB RAM, 2 x 250 GB SATA, plain RHEL 5.4)

    MySQL handles 2 dbs:

    Code:
    4.6G    /var/lib/mysql/site_com
    1.7G    /var/lib/mysql/site_net
    However, we have a problem when we run daily night backups, this is the way I do the backups on the script:

    Code:
    for i in $(mysql -u root -p$MYSQLPASS -Bse 'show databases' | egrep -v 'test|information'); 
    do mysqldump --opt -p$MYSQLPASS $i -c> $TMPDIRSQL/mysql-dump-$i-$DATE.sql; done
    It takes about 20 minutes to do the backup.. while it's still serving request from the WebServer, anyway, from minute 5 until it finishes, the load average is getting higher and higer until it goes down.

    This is my my.cnf in case it helps:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    skip-bdb
    query_cache_limit=1M
    query_cache_size=48M
    query_cache_type=1
    max_connections=1200
    interactive_timeout=100
    wait_timeout=10
    connect_timeout=10
    thread_cache_size=128
    key_buffer=48M
    join_buffer=8M
    max_allowed_packet=16M
    table_cache=2036
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=2M
    max_connect_errors=10

    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=4
    myisam_sort_buffer_size=64M

    # Add
    max_heap_table_size = 48M
    tmp_table_size = 48M
    low_priority_updates=1
    concurrent_insert=2

    [mysql.server]
    user=mysql
    #basedir=/var/lib

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    log-slow-queries=/var/log/slow-queries.log
    Any ideas are appreciated!

    Thanks!
    Last edited by sh4ka; 10-28-2009 at 07:38 AM.

  2. #2
    Join Date
    Oct 2009
    Posts
    865
    Running mysqldump will usually cause high loads, since it makes table-wide locks in order to enforce consistency - at least on a table by table basis. If you want to make backups live, without causing any slowdowns, I'd recommend to set up a slave replica DB server and run the backups off that.

  3. #3
    Join Date
    Apr 2006
    Posts
    520
    Thanks Aeris!

    Any other suggestions?

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,431
    Hi

    You could try altering the nice setting on the cron, do a man nice on your box as (I can never remember if -19 or 19 is the lowest).

    From memory -19 will give it full priority where as 19 will give it the least. which should lower the load. Problem is then it will take longer.

    One other suggestion is to replicate it to a slave mysql server and do your back from there. That way it will not affect your production server.

    **note try and get the slave and master mysql server connected at 1 gig (on a private vlan) and it should replicate without issues.

    ***note running it at the highest priority will cause your box to stop doing anything else (be warned)

    Hope that helps

    Andrew
    Last edited by relichost; 10-28-2009 at 08:32 AM. Reason: warning re prio

  5. #5
    Join Date
    Apr 2006
    Posts
    520
    Thanks nowiresltd

    I'm already using /bin/nice -n +19, so, I think that won't help more than already did.

    Any other suggestions?

    Thanks!

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,431
    Hi

    Im guessing the only other solution is a slave mysql server to backup from, if budget allows.

    Thanks

    Andrew

  7. #7
    Join Date
    Sep 2006
    Location
    Dallas, TX
    Posts
    333
    Are you using software RAID1? That would cause high load during a dump. You need to use hardware RAID (Not host-based RAID. It has to be an actual physical RAID card).

  8. #8
    Join Date
    Apr 2006
    Posts
    520
    Hi Andrew, thanks for your answer!

    As you see, I don't have experience with replication master-slave, just managed stand alone mysql servers.. so, any suggestions or tutorials are really appreciated!

    A few questions:

    1.- Allright.. about the mysql replication, I found this tutorial, but seems to be a little bit old.. what do you think? Also found this second one, can it be useful?

    I'm using this MySQL Version:
    mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0

    2.- Should I upgrade to 5.1? or this master-slave replication can work in 5.0.x?

    3.- Because i wil have to setup new servers in a private lan with a 100 Mbits connection between them... What would be the best for this, 64bits or 32 bits O.S?

    Thanks.

  9. #9
    Join Date
    Oct 2009
    Posts
    865
    While the MySQL spec specifically says that mixed 32-bit and 64-bit master/slave setups are supported, I've had nothing but bad experiences with it. As in, buggy and horribly corrupted data on all slaves that didn't match the master's bititude. You should as a rule use the exact same build on both slaves and masters, using 5.0 shouldn't be a problem.

    Note that unless you plan on running other queries from your slave, virtually anything will do. An old box with a couple gigs RAM running at your home is plenty, unless you have massive write activity.

  10. #10
    Join Date
    Apr 2006
    Posts
    520
    Thanks!!

    If anyone know how to setup a master -slave mysql setup or have any handy tutorial it will be really appreciated!

  11. #11
    Join Date
    Feb 2006
    Location
    Buffalo, NY
    Posts
    1,501
    Try

    Code:
    mysqldump --quick --single-transaction
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Official Let's Encrypt Sponsor

  12. #12
    Join Date
    Apr 2006
    Posts
    520
    Thanks Cody,

    Can I add "--single-transaction" to "--opt" (--opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick) ???

    --single-transaction will limit the open number of files to 1, right?

    Thanks!

  13. #13
    Join Date
    Feb 2006
    Location
    Buffalo, NY
    Posts
    1,501
    Quote Originally Posted by sh4ka View Post
    Thanks Cody,

    Can I add "--single-transaction" to "--opt" (--opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick) ???

    --single-transaction will limit the open number of files to 1, right?

    Thanks!
    Should be fine, if I recall --opt is enabled by default?

    -Cody
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Official Let's Encrypt Sponsor

  14. #14
    Join Date
    Apr 2006
    Posts
    520
    I think "--opt" is not enabled bu default, I always do the dumps in this way:

    Code:
    for i in $(mysql -u root -p$MYSQLPASS -Bse 'show databases' | egrep -v 'test|information'); 
    do mysqldump --opt -p$MYSQLPASS $i -c> $TMPDIRSQL/mysql-dump-$i-$DATE.sql; done
    Thanks.

  15. #15
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,771
    Add the following to the my.cnf and try. Reduce the value of max_allowed_packet.
    Code:
    [mysqldump]
    quick
    max_allowed_packet=16M
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  16. #16
    Join Date
    Oct 2009
    Posts
    865
    Quote Originally Posted by sh4ka View Post
    Thanks!!

    If anyone know how to setup a master -slave mysql setup or have any handy tutorial it will be really appreciated!
    There is always the official one.

    http://dev.mysql.com/doc/refman/5.0/...ion-howto.html

  17. #17
    Join Date
    Apr 2006
    Posts
    520
    Thanks, will try that and let you know!

  18. #18
    Join Date
    Feb 2004
    Posts
    633
    I'll thow out a couple of other suggestions aside from replication.

    You may want to try Maatkit's parallel dump program:

    http://www.maatkit.org/doc/mk-parallel-dump.html

    It's not going to get around the fact that you'll need to lock the tables, but it can parallelize the dump somewhat and potentially reduce the backup window. I say potentially because it depends on the schema and data; it doesn't work real well if you have say one super large table, or hundreds of tiny tables, but it definitely works faster if you have a moderate number of tables containing a decent amount of data.

    http://www.paragon-cs.com/wordpress/...lel-dump-test/

    You have two other options, both of which would require substantially more effort.

    One is to re-partition your server using LVM, which would allow you to take nearly hot snapshot backups in a consistent state.

    http://www.mysqlperformanceblog.com/...ication-setup/

    The second option is to consider converting your tables to InnoDB from MyISAM (which you would really need to test first), which allows for hot backups. The company that originally designed InnoDB offers a commercial product that does hot backups of InnoDB tables (and MySQL is supposed to be also offering an open source one soon as well):

    http://www.innodb.com/products/hot-backup/
    Last edited by lockbull; 10-28-2009 at 03:04 PM. Reason: Typo

  19. #19
    Join Date
    Dec 2006
    Posts
    480
    I wouldn't recommend positive nice levels - it just means the tables will be locked for longer than necessary.

    Also, I pipe the output to the cstream program (http://www.cons.org/cracauer/cstream.html) rather than redirecting into a file - that allows me to use O_DIRECT which means it bypasses the linux disk cache. That prevents the OS flushing all the useful blocks that are held in your disk cache prior to the backup just to make room for backup files that aren't going to read again under normal circumstances.

  20. #20
    Join Date
    Apr 2006
    Posts
    520
    RBBOT

    Thanks for the info! Could you please paste the syntax you are using?

    Regards.

  21. #21
    Join Date
    Dec 2006
    Posts
    480
    mysqldump <usual args here> | cstream -b1048576 -OD -o <output path here>

Similar Threads

  1. Need help optimizing server, High load, high CPU usage
    By chasebug in forum Hosting Security and Technology
    Replies: 2
    Last Post: 10-17-2009, 08:38 AM
  2. CentOS 5.3,vsftpd, high iowait, high LOAD :(
    By ignitionservers in forum Hosting Security and Technology
    Replies: 25
    Last Post: 09-28-2009, 06:55 PM
  3. high server load / high swap / lots of httpd
    By hbhb in forum Hosting Security and Technology
    Replies: 12
    Last Post: 02-22-2008, 09:17 AM
  4. Optimize : High Load , Slow Websites , High Memory Using .
    By ^LinuX^ServeR^ in forum Dedicated Server
    Replies: 50
    Last Post: 06-19-2006, 12:25 PM
  5. Server load not very high but pages load slowly
    By singtel22 in forum Hosting Security and Technology
    Replies: 17
    Last Post: 01-27-2005, 02:43 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
  •