New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL Optimization for cPanel [InnoDB Tables Defragmentation]
Mahfuz_SS_EHL
Host Rep, Veteran
in Help
Hi,
Recently, I noticed Load Spike on My Shared and that happened from MySQL. Later on, I ran mysqltuner & the result is as follwoing:
>> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.42-cll [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 2G (Tables: 3468) [--] Data in InnoDB tables: 693M (Tables: 3277) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 1023K (Tables: 49) [!!] Total fragmented tables: 3299 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 12m 24s (38K q [51.406 qps], 2K conn, TX: 337M, RX: 7M) [--] Reads / Writes: 66% / 34% [--] Total buffers: 1.9G global + 4.4M per thread (1000 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 6.2G (78% of installed RAM) [OK] Slow queries: 0% (0/38K) [OK] Highest usage of available connections: 0% (9/1000) [OK] Key buffer size / total MyISAM indexes: 1.0G/925.8M [OK] Key buffer hit rate: 97.7% (1M cached / 29K reads) [OK] Query cache efficiency: 41.0% (10K cached / 24K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 1K sorts) [!!] Joins performed without indexes: 289 [!!] Temporary tables created on disk: 40% (475 on disk / 1K total) [OK] Thread cache hit rate: 99% (9 created / 2K connections) [!!] Table cache hit rate: 14% (4K open / 27K opened) [OK] Open file limit used: 44% (3K/9K) [OK] Table locks acquired immediately: 99% (29K immediate / 29K locks) [OK] InnoDB buffer pool / data size: 800.0M/693.3M [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 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_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 4000)
It's My my.cnf File:
[mysqld] max_connections = 1000 key_buffer = 32M key_buffer_size= 1024M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 4000 thread_cache_size = 286 interactive_timeout = 60 wait_timeout = 120 connect_timeout = 10 max_allowed_packet = 64M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 32M max_connect_errors = 100000 query_cache_limit = 2M query_cache_size = 64M query_cache_type = 1 thread_concurrency=4 tmp_table_size = 16M local-infile=0 innodb_buffer_pool_size=800M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 256M sort_buffer = 64M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout
The VPS is virtualized with Xen-PV, 8 Threads of Core i7-2600, 8 GB Ram, 200 GB HDD Space on SW Raid 1 running cPanel Latest Version.
I tried to Optimize the Tables via "myqlcheck -A –optimize" but it showed "Table does not support optimize, doing recreate + analyze instead" for most of the Tables. I'm running InnoDB as Storage Engine. So, Is there any easy way to defragment these Tables in shortest time ??
Regards,
Mahfuz.
Comments
The primary reason for fragmentation on InnoDB is by not inserting data in primary key order. It may be possible for you to remedy that, perhaps not.
One way to deal with it is by partitioning the table. Again, it depends on how the data is used/updated/deleted. If records are permanently stored in there, then it makes sense to partition older from new. That way you can just reorganise the older partitions once and they'll be in order.
Test on a separate server. Sometimes partitioning can hurt performance, much depends on the query & data itself.
Going purely your my.cnf, I would say you need to increased you Innisbrook buffer pool. For Innodb memory is everything so give it as much as you can spare. it will also depend on what kind of applications you are running.
test it out and see if it helps.
First static caching on web applications to prevent excess MySQL queries, then optimize MySQL second. That's what I usually suggest. Deal with the initial problem first and then address any remaining issues.
Your max connections is huge. I would set that down to 100 or so, and up the other parameters. If you are using more than 100 connections, then something else is going on.
I'd also set this...
join_buffer_size = 8M
tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 10000
Your other major issue is that it looks like you are running 32 bit OS. I'd look into migrating to a 64 bit OS, as your server has a lot of RAM.
Also, if you are running your own sites, I'd look into adding memcache to reduce database load, as well as verify that your php is running xcache and has its thread set to the same number as
cat /proc/cpuinfo
Run mysqltuner once a day until you receive no more suggestions from it. After that, run it once a month on the first of the month.
I would dump your tables, change to one file-per-table structure, re-import the said tables, then
myqlcheck --optimisecould do a bit more work with the data in InnoDB (if you have the time). This will also clean up your fragmentation across the board.You might see better performance if you switch that 2GB from MyISAM to InnoDB (I bet that's where most of the fragmentation exists). Why do you need MyISAM anyway?
What is the bottleneck here? If it's IO than data compression might help.
I'm not aware how the data's went into MyISAM. Now, I need a script which will do everything to defragment these tables.
From an algorithmic perspective, I don't think defragmentation would help that much.
Check out this for an explanation (warning: formatting sucks). If you think it talks about your issue then use this tutorial. It might be useful to switch completely over to InnoDB. The defaults for the innodb_file_per_table option and the usage of MyISAM were changed in MySQL 5.6, it might be good to change those (requires a export, drop, import). Depending on the data you might see a huge improvement.
Innodb don't need defragmentation.
Have you looked at changing over to MariaDB? I'd recommend changing over to MariaDB and finding a server with Solid State Drives (SSD), then have it hosted remotely. If your using cPanel (as you stated) it's completely doable.
Please use cloudlinux and problem solved if this a shared hosting server.
I got spikes too by mysql. One user was the problem. But with CL dbtop solved instantly.