Toggle sidebar

Web Server Issues (WHM / cPanel)

Sort by date Sort by votes
DesignerNick

DesignerNick

Free Member
Apr 22, 2009
3,442
609
Coventry, UK
I have noticed that I was getting huge disk IO issues on my server, by chance when it was under load I restarted mySQL (it took an absolute age to close). I have been trying to optimise it and this is where I am so far.

This is a VM using Xenserver with 16GB Ram, Total processors: 4 (Intel(R) Xeon(R) CPU E3-1230 v3 @ 3.30GHz) and the storage is 2 x SATA3 drives using an Adaptec RAID card. When something like a backup or anything that requires mySQL to work happens it really hammers the disk IO. Memory is rarely over 30% usage and CPU is only ever really IO Wait.

This is the mysql tuner recommendations:

Code: 
 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 1007)
[--] Data in InnoDB tables: 385M (Tables: 547)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 624K (Tables: 11)
[!!] Total fragmented tables: 567

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 32m 9s (6M q [125.077 qps], 102K conn, TX: 31B, RX: 1B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 536.0M global + 131.4M per thread (300 max threads)
[!!] Maximum possible memory usage: 39.0G (257% of installed RAM)
[OK] Slow queries: 0% (589/6M)
[OK] Highest usage of available connections: 73% (221/300)
[OK] Key buffer size / total MyISAM indexes: 64.0M/257.0M
[OK] Key buffer hit rate: 100.0% (424M cached / 147K reads)
[OK] Query cache efficiency: 83.1% (3M cached / 4M selects)
[!!] Query cache prunes per day: 20487
[OK] Sorts requiring temporary tables: 0% (126 temp sorts / 209K sorts)
[!!] Joins performed without indexes: 9746
[!!] Temporary tables created on disk: 43% (37K on disk / 85K total)
[OK] Thread cache hit rate: 99% (535 created / 102K connections)
[OK] Table cache hit rate: 75% (3K open / 4K opened)
[OK] Open file limit used: 35% (3K/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/385.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    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
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 128.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 64M)
    innodb_buffer_pool_size (>= 385M)

This is my my.cnf

Code: 
[mysqld]
open_files_limit=10000
max_connections = 300
query-cache-type = 1
query-cache-size = 256M
query-cache-limit = 2M
thread_cache_size=40
wait_timeout=50
max_delayed_threads=1
delayed_insert_timeout=30
max_allowed_packet=16M
table_open_cache= 3000
innodb_buffer_pool_size=128M
tmp_table_size=128M
max_heap_table_size=64M
key_buffer_size=64M
join_buffer_size=128M
innodb_additional_mem_pool_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
log-slow-queries=/var/lib/mysql/slow.log
table_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 128K
read_rnd_buffer_size = 1M



[mysqldump]
quick
 
Not familiar with the Adaptec cards, but there might be some optimisation that can be done to improve I/O performance. There are various benchmarking tools like postmark or iozone, or you can do a simple I/O test with dd. Compare the results with another server to see if the performance is really bad or just normal.

On the MySQL side, can you convert the MyISAM tables to innodb? That would give you the opportunity to use a much larger innodb_buffer_pool_size, which would effectively put the entire db into memory - you have plenty. Also that would let you use the multi-threading capability of innodb, that won't help with any one task but will with overall performance.

As always with MySQL, go one step at a time and observe.
 
Upvote 0
KM-Tiger said:
Not familiar with the Adaptec cards, but there might be some optimisation that can be done to improve I/O performance. There are various benchmarking tools like postmark or iozone, or you can do a simple I/O test with dd. Compare the results with another server to see if the performance is really bad or just normal.

On the MySQL side, can you convert the MyISAM tables to innodb? That would give you the opportunity to use a much larger innodb_buffer_pool_size, which would effectively put the entire db into memory - you have plenty. Also that would let you use the multi-threading capability of innodb, that won't help with any one task but will with overall performance.

As always with MySQL, go one step at a time and observe.
Click to expand...

Thanks, I can have a look and see how much is using MyISAM, my main site is a VB forum which does use innodb I think.

I did a disk speed test on both host and guest:

Guest:

root@web [~]# dd bs=1M count=256 if=/dev/zero of=test conv=fdatasync
256+0 records in
256+0 records out
268435456 bytes (268 MB) copied, 7.38067 s, 36.4 MB/s


Host:

[root@xen ~]# dd bs=1M count=256 if=/dev/zero of=test conv=fdatasync
256+0 records in
256+0 records out
268435456 bytes (268 MB) copied, 7.50283 seconds, 35.8 MB/s

It looks like throughput is about the same for both. It looks quite slow??
 
Upvote 0
thanks, they are sata3 with 6gbps and the raid card shows them as 6gbps.

They are 3TB also, I have just done a test on the same server hardware, same raid card but 4 x 1tb in a raid10 and the speed was 110mb/ps

I am swapping to the other server :)
 
Upvote 0
OK so moved the VM to the new server and the same test gives:

root@web [~]# dd bs=1M count=1024 if=/dev/zero of=test conv=fdatasync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 8.10351 s, 133 MB/s
 
Upvote 0
DesignerNick said:
1073741824 bytes (1.1 GB) copied, 8.10351 s, 133 MB/s
Click to expand...
That's probably about normal for SATA discs, and certainly better!

Best I can see from here is:
dd bs=1M count=256 if=/dev/zero of=test conv=fdatasync
256+0 records in
256+0 records out
268435456 bytes (268 MB) copied, 0.439522 s, 700 MB/s

But that is SAS discs with a top notch LSI RAID controller.
 
Upvote 0
No problem.

It is hard sometimes to know what is best, particularly where MySQL is concerned. Whilst there are lots of tunable parameters, no amount of tuning will compensate for poor queries where the bottleneck may simply be the processor speed to deal with them.

If you have not come across it before, 'atop' is probably the best tool to identify bottlenecks (available as a package in most distros). Let it run for a while, and the display changes colour where bottlenecks occur. Useful, as sometimes it's not where you think.
 
Upvote 0
I had tried all sorts, xcache, using sphinx offsite servers so hopefully with them it will be even faster :)

I think it may be an issue with the 3TB drives, I had used brand new 3TB drives and swapped to a different server with a different RAID card and the issue carried on. I had spent hours playing around with mySQL.

I have now just run a full backup and the graphs didn't even flinch!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
mySQL - server (over)loading
Replies
14
Views
2K
IT & Internet
garyk
G

Latest Articles

Top Bottom
Back