Skip to content

Instantly share code, notes, and snippets.

@fredbradley

fredbradley/gist:ce66f20e51d65a4b3bd1a406e5fe743b

Created August 10, 2023 08:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save fredbradley/ce66f20e51d65a4b3bd1a406e5fe743b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save fredbradley/ce66f20e51d65a4b3bd1a406e5fe743b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. fredbradley created this gist Aug 10, 2023.
    11 changes: 11 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    #!/bin/sh

    ## When the Senior School Database was overloading some tables and we weren't sure why we have to optimize the DB every day.
    ## This was the code I used.

    mysql -e "SELECT TABLE_NAME FROM (SELECT TABLE_SCHEMA,TABLE_NAME,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'TABLEsizeGB' FROM information_schema.tables WHERE TABLE_SCHEMA='seniorschool' GROUP BY TABLE_SCHEMA,TABLE_NAME HAVING Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) > 0.05 ORDER BY TABLEsizeGB DESC) as A;" | while read TABLE_NAME; do
    if [ $TABLE_NAME != 'TABLE_NAME' ]
    then
    mysqlcheck --optimize seniorschool $TABLE_NAME
    fi
    done