I agree with rsk - you should open a ticket for this. Either the host itself is having trouble, or it's just an ugly set of neighbors to be with. If it's not something that Linode can directly correct, in my experience they've been willing to offer a migration to a new host. While that won't really guarantee anything, I can't really think how you could be any worse off.

-- David

I'll query linode about it but until I hear back from them I'm going to assume it's my fault and keep tuning.

I am -sure- my mysql conf is whacked but I think there is further issues as well. 60MB folder copy using cp -a ~otheruser/folder ./folder took 10 minutes and thrashed the system into 500 errors the entire time... 60mb. that should take seconds in my book.

I grabbed the mysqltuner and got this:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.6
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 409M (Tables: 444)
[--] Data in InnoDB tables: 16K (Tables: 1)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 17h 28m 45s (4M q [17.377 qps], 6K conn, TX: 3B, RX: 1B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 42.0M global + 2.6M per thread (151 max threads)
[!!] Maximum possible memory usage: 438.4M (87% of installed RAM)
[OK] Slow queries: 0% (40/4M)
[OK] Highest usage of available connections: 15% (24/151)
[!!] Key buffer size / total MyISAM indexes: 16.0K/125.6M
[!!] Key buffer hit rate: 89.4% (371M cached / 39M reads)
[OK] Query cache efficiency: 61.7% (1M cached / 2M selects)
[!!] Query cache prunes per day: 5982
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
[!!] Joins performed without indexes: 4292
[!!] Temporary tables created on disk: 44% (11K on disk / 26K total)
[OK] Thread cache hit rate: 98% (121 created / 6K connections)
[!!] Table cache hit rate: 0% (64 open / 6K opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 16.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 125.6M)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 64)

I futzed with a couple mysql settings, restarted and ran the optimize tables on stuff for my forum and a test copy board. It trashed for about 45 seconds each while doing this and I ran it again to get:

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.6
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 408M (Tables: 444)
[--] Data in InnoDB tables: 16K (Tables: 1)
[!!] Total fragmented tables: 16

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 59s (5K q [9.929 qps], 527 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 106.0M global + 2.6M per thread (50 max threads)
[OK] Maximum possible memory usage: 237.2M (47% of installed RAM)
[OK] Slow queries: 0% (2/5K)
[OK] Highest usage of available connections: 28% (14/50)
[!!] Key buffer size / total MyISAM indexes: 64.0M/118.9M
[!!] Key buffer hit rate: 78.7% (585K cached / 124K reads)
[OK] Query cache efficiency: 37.4% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 29 sorts)
[OK] Temporary tables created on disk: 16% (57 on disk / 349 total)
[OK] Thread cache hit rate: 97% (14 created / 527 connections)
[!!] Table cache hit rate: 1% (64 open / 5K opened)
[OK] Open file limit used: 8% (91/1K)
[OK] Table locks acquired immediately: 98% (3K immediate / 3K locks)
[OK] InnoDB data size / buffer pool: 16.0K/8.0M

-------- 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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 118.9M)
    table_cache (> 64)

Settings I had in my.cnf where something I had found around the linode forums or library I can't remember.

Here is current my.cnf:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

bind-address            = 127.0.0.1

max_connections = 50

key_buffer              = 64M
max_allowed_packet      = 1M
thread_stack            = 64K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

log_error                = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size         = 100M

#innodb_file_per_table = 1


[mysqldump]
quick
quote-names
max_allowed_packet      = 16M


[mysql]


[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

Thanks again for all of your help, I really appreciate it. Hope I can pay it forward at some point!

No harm in that (other than diminishing returns past a certain point), but I suspect what's been happening, at least most recently, is that your tuning is helping to prevent you from having to hit the disks much at all, so basically avoiding the I/O contention or minimizing it's impact.

But if copying 10s of MB (like your 60MB example) is thrashing things for 10-15 minutes, there's not a lot of tuning you'll be able to do to address that. Eventually you'll need to be able to do a reasonable amount of I/O every so often.

A quick test on one of my Linode 512s for a 60MB copy took about 2 seconds, and I managed to catch an I/O wait status of 10% in one of those seconds.

It might be interesting to check an "hdparm -t" test on your Linode. It's a read test, but I'm pretty sure will still suffer similarly under contention. Numbers can vary widely, but I think even in poor circumstances I've seen decent double digit MB/s on my Linodes, and most often it's 100+.

-- David

Just heard back from linode, they tweaked some settings and I reran the disc copy. It completed in under a second. Fiddles with a couple mysql things and they completed reasonably quickly as well.

If there are anythings in my mysql conf you see that should be tweaked I'd love to hear about it though. No reason not to fix things just because they haven't caused a problem yet.

I'm probably going to spend some time intentionally trying to make it thrash but I'm hopeful at this point.

Okay there was definitely something up on that node.

I just did a full backup copy and update to 2.0 in 15 minutes. It trashed a bit and did some serious disk IO(it is 250+MB of data and I was changing column types, migrating data, adding indexes...) however I was able to browse the primary forum during it, It did slow down at times and I did have a couple of 500 errors but it worked.

Last time I tried this I had the entire place in 500 errors for an hour before I gave up.

You mean your (Li)node, or the physical "host"?

_________________
rsk, providing useless advice on the Internet since 2005.

I guess the physical host, they said they "made some changes". I don't know what that means exactly but I'm not having nearly the problems I was

*nods!*
Thanks for clarification!

_________________
rsk, providing useless advice on the Internet since 2005.

Excellent. That sounds like more typical performance.


Not sure if the "you" here was aimed at my post or just more in general, but just in case it's the former and lest you feel ignored, I'm a PostgreSQL guy, so my mysql experience is too limited to be of much help.

-- David

It was general, but I appreciate the concern.

Right now I'm feeling good about my server once again, but plan to keep on this MySQL thing until I'm sure I've tuned it as good as I can.