mysqldump (Backup using ssh) not completed, & please advice my.cnf

[basketmen]

Hi guys i just move to new server


in previous server i can running mysql backup using mysqldump with no problem

mysqldump --opt -u user -p dbname > backup.sql

but in this new server, its running not completed, stopped in the middle

the mysql size total are 2.2Gb, but its only get 650Mb



please advice what should we do, current server's specs are bigger from the previous servers





here is current server's specs

Core i7 860 Quad Core - SINGLE CPU QUAD CORE 2.8GHZ FSB 2.5 GT/s (8MB CACHE) w/HT
Control Panel: CPanel/WHM - Centos 5 64 Bit
RAM/Memory: 8GB DDR3
Primary Hard Drive: 1TB SATA

here is the my.cnf content, from /http://www.pctipsbox.com/a-real-production-mycnf-file-for-mysql-server/, and please advice do you think below configuration are good for above server's specs, the server are running vbulletin big enough

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

[mysql.server]
user=mysql

[RCraft]

It really doesn't help you to use someone else's mysql config file since your server is going to be completely different. Every server needs to be individually tuned.

Copy and paste this:

wget mysqltuner.pl
chmod755 mysqltuner.pl
./mysqltuner.pl

Copy the entire output and paste it here.

Also, how is the CPU load on this server? There's really nothing in your MySQL config file that would prevent you from doing a dump.

You might want to try running the MySQL dump at a lower priority to reduce I/O. Try adding the following in front of the command:

ionice -c3 nice -n19

[basketmen]

yes i just trying his my.cnf configuration since he said in that blog its running good in some server that using vbulletin, i am still dont know how to define the my.cnf configuration, my server's specs are above in first post, please give advice





ok here is the output MySQLTuner


>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-community
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 998)
[--] Data in MEMORY tables: 1M (Tables: 4)
[!!] Total fragmented tables: 15

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 13m 44s (326K q [73.884 qps], 17K conn, TX: 12B, RX: 77M)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 74.0M global + 4.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.3G (29% of installed RAM)
[OK] Slow queries: 0% (0/326K)
[OK] Highest usage of available connections: 2% (12/500)
[OK] Key buffer size / total MyISAM indexes: 16.0M/492.0M
[OK] Key buffer hit rate: 97.6% (25M cached / 608K reads)
[OK] Query cache efficiency: 55.0% (129K cached / 234K selects)
[!!] Query cache prunes per day: 128154
[OK] Sorts requiring temporary tables: 2% (608 temp sorts / 27K sorts)
[!!] Joins performed without indexes: 106
[OK] Temporary tables created on disk: 8% (551 on disk / 6K total)
[OK] Thread cache hit rate: 99% (12 created / 17K connections)
[OK] Table cache hit rate: 97% (683 open / 697 opened)
[OK] Open file limit used: 51% (1K/2K)
[OK] Table locks acquired immediately: 99% (248K immediate / 248K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 1.0M, or always use indexes with joins)








here is top result in little peak time

[RCraft]

Leave your MySQL service up for 24 hours and then run it again as per the scripts recommendation. It needs those 24 hours to get an accurate reading.