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

jols

• September 05, 2013 04:49

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

  • September 05, 2013 10:15

  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 >> Bug reports, feature requests, and downloads at

  0
• 

  24x7server

  • September 05, 2013 10:33

  Hello, Yes, You can run the this command on your server through SHELL, It will repair and optimize your all database [QUOTE]mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
  

  0
• 

  jols

  • September 05, 2013 11:04

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

  0
• 

  cPanelMichael

  • September 05, 2013 13:52

  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.

  0
• 

  Archmactrix

  • September 05, 2013 16:04

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

  0
• 

  pdelteil

  • October 27, 2014 03:33

  [quote="Archmactrix, post: 1457042">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.

  0
• 

  Archmactrix

  • October 27, 2014 06:48

  [quote="pdelteil, post: 1756811">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.

  0

Please sign in to leave a comment.