Results 1 to 21 of 21
Thread: High load using mysqlump utilty
-
10-28-2009, 07:34 AM #1Web Hosting Evangelist
- 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
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
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
Thanks!Last edited by sh4ka; 10-28-2009 at 07:38 AM.
-
10-28-2009, 07:42 AM #2Web Hosting Master
- 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.
-
10-28-2009, 08:00 AM #3Web Hosting Evangelist
- Join Date
- Apr 2006
- Posts
- 520
Thanks Aeris!
Any other suggestions?
-
10-28-2009, 08:29 AM #4Temporarily Suspended
- 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
AndrewLast edited by relichost; 10-28-2009 at 08:32 AM. Reason: warning re prio
-
10-28-2009, 08:46 AM #5Web Hosting Evangelist
- 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!
-
10-28-2009, 08:51 AM #6Temporarily Suspended
- 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
-
10-28-2009, 09:16 AM #7Web Hosting Guru
- 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).
-
10-28-2009, 09:24 AM #8Web Hosting Evangelist
- 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.
-
10-28-2009, 11:27 AM #9Web Hosting Master
- 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-28-2009, 11:34 AM #10Web Hosting Evangelist
- 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!
-
10-28-2009, 11:40 AM #11Web Hosting Master
- 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
-
10-28-2009, 12:06 PM #12Web Hosting Evangelist
- 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!
-
10-28-2009, 12:14 PM #13Web Hosting Master
- Join Date
- Feb 2006
- Location
- Buffalo, NY
- Posts
- 1,501
-
10-28-2009, 12:22 PM #14Web Hosting Evangelist
- 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
-
10-28-2009, 12:27 PM #15Web Hosting Master
- 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
-
10-28-2009, 12:39 PM #16Web Hosting Master
- Join Date
- Oct 2009
- Posts
- 865
There is always the official one.
http://dev.mysql.com/doc/refman/5.0/...ion-howto.html
-
10-28-2009, 12:44 PM #17Web Hosting Evangelist
- Join Date
- Apr 2006
- Posts
- 520
Thanks, will try that and let you know!
-
10-28-2009, 03:03 PM #18Web Hosting Master
- 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
-
10-30-2009, 01:12 PM #19Web Hosting Evangelist
- 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.
-
10-30-2009, 01:32 PM #20Web Hosting Evangelist
- Join Date
- Apr 2006
- Posts
- 520
RBBOT
Thanks for the info! Could you please paste the syntax you are using?
Regards.
-
10-30-2009, 01:34 PM #21Web Hosting Evangelist
- Join Date
- Dec 2006
- Posts
- 480
mysqldump <usual args here> | cstream -b1048576 -OD -o <output path here>
Similar Threads
-
Need help optimizing server, High load, high CPU usage
By chasebug in forum Hosting Security and TechnologyReplies: 2Last Post: 10-17-2009, 08:38 AM -
CentOS 5.3,vsftpd, high iowait, high LOAD :(
By ignitionservers in forum Hosting Security and TechnologyReplies: 25Last Post: 09-28-2009, 06:55 PM -
high server load / high swap / lots of httpd
By hbhb in forum Hosting Security and TechnologyReplies: 12Last Post: 02-22-2008, 09:17 AM -
Optimize : High Load , Slow Websites , High Memory Using .
By ^LinuX^ServeR^ in forum Dedicated ServerReplies: 50Last Post: 06-19-2006, 12:25 PM -
Server load not very high but pages load slowly
By singtel22 in forum Hosting Security and TechnologyReplies: 17Last Post: 01-27-2005, 02:43 PM