MySQL, high number of fragment tables. Safe to optimize all?

[jols]

Hi,

I am noticing that MySQL in Top regularly takes up between 160% and 400% CPU.

I recently ran mysqltuner.pl and it listed over 1000 fragmented tables on the server.

Question - Is is safe to run the following at shell? Does this have a chance of causing any corruption issues, etc?

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases


Thanks much!

 

[jols]

By the way, here's the contents of my.cnf

-----------------------------------------------------------------------

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
table_open_cache = 5096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
query_cache_limit = 16M
join_buffer_size = 384K
tmp_table_size = 32M
max_heap_table_size = 32M
table_cache = 6024
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 400
log-slow-queries
local-infile=0
innodb_file_per_table=1
tmpdir=/mysqltmp
server-id       = 1

innodb_buffer_pool_size = 2G
open_files_limit=50000
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
  
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

-----------------------------------------------------------------------
-----------------------------------------------------------------------



And here's the report from mysqltuner.pl

>>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 11G (Tables: 19935)
[--] Data in InnoDB tables: 1G (Tables: 10064)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 105)
[!!] Total fragmented tables: 1637

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22m 9s (53K q [40.251 qps], 2K conn, TX: 266M, RX: 15M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 2.5G global + 12.6M per thread (400 max threads)
[OK] Maximum possible memory usage: 7.5G (64% of installed RAM)
[OK] Slow queries: 0% (38/53K)
[OK] Highest usage of available connections: 2% (11/400)
[OK] Key buffer size / total MyISAM indexes: 384.0M/3.5G
[OK] Key buffer hit rate: 99.0% (1M cached / 11K reads)
[OK] Query cache efficiency: 32.2% (22K cached / 69K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 1134
[OK] Temporary tables created on disk: 11% (456 on disk / 4K total)
[OK] Thread cache hit rate: 99% (11 created / 2K connections)
[OK] Table cache hit rate: 97% (1K open / 1K opened)
[OK] Open file limit used: 3% (1K/50K)
[OK] Table locks acquired immediately: 99% (32K immediate / 32K locks)
[OK] InnoDB data size / buffer pool: 1.9G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 384.0K, or always use indexes with joins)

 

[24x7server]

Hello,

Yes, You can run the this command on your server through SHELL, It will repair and optimize your all database

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

 

[jols]

Hi. Thanks, yup, but is it totally safe to run on a highly active server chock full of databases?

 

[cPanelMichael]

Hello

I recommend using the MySQL tuner after MySQL has been running for at least 24 hours. The output you pasted shows it was only up for 22 minutes when the tuner was ran. As for your other question, repairing and optimizing database tables is generally a safe operation.

Thank you.

 

[Archmactrix]

You should probably omit --check from the command as the --auto-repair option uses check automatically.

 

[pdelteil]

You should probably omit --check from the command as the --auto-repair option uses check automatically.

You can't run the command with the --check and --auto-repair options at the same time.

It gives this error:

Error: mysqlcheck doesn't support multiple contradicting commands.

 

[Archmactrix]

You can't run the command with the --check and --auto-repair options at the same time.

It gives this error:

Error: mysqlcheck doesn't support multiple contradicting commands.

Yes, and you really don't need to specify the --check option as it is the default operator.