That's a great little optimisation script.
I hope you don't mind that I made some changes to it.
I've added code to ask the user for the DB username and password rather than
include them in the command line. I don't want my passwords to be readable
in plain text just by looking at the shell history. Also the password is not
echoed to the terminal in this version. If either username or password is
blank the script exits.
I added the database to the mysql command where appropriate to reduce the
number of commands run once the mysql client was started as an optimization.
This probably makes no difference.
I also added in a success or failed keyword depending on whether or not the
term "ERROR" is returned from the OPTIMIZE TABLE mysql command which then
displays the Error returned.
#############################
#!/bin/bash
echo "Enter DB User: "
read user
echo "Enter DB Password: "
read -s pass
if [ -z "${user}" ];
then
echo "Username is blank. Exiting script"
exit
elif [ -z "${pass}" ];
then
echo "Password is blank. Exiting script"
exit
fi
for db in $(echo "SHOW DATABASES;" | mysql -u$user --password=$pass | grep
-v -e "Database" -e "information_schema")
do
echo "Switching to database $db"
TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$user --password=$pass
| grep -v Tables_in_)
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
result=$(echo "OPTIMIZE TABLE $table" | mysql -D$db -u$user
--password=$pass 2>&1)
if [[ $result == *ERROR* ]]
then
echo "FAILED"
echo ".... $result"
else
echo "Success"
fi
done
done
#############################
Rory
On 26 August 2011 10:39, Rudolf Bargholz <bargh...@onlinetravel.ch> wrote:
> Hi,****
>
> ** **
>
> You do not mention if you use MySQL. For our setup it was not apache that
> was the bottleneck, rather MySQL. There were a few things we did to speed up
> OTRS dramatically:****
>
> ** **
>
> **1) **Follow through all the steps mentioned in the OTRS support
> module****
>
> **2) **Restart all services (MySQL, Apache and OTRS)****
>
> **3) **Next optimize all tables in your MySQL database:****
>
> ** **
>
> We have a script “optimizetable” with the following contents:****
>
> ** **
>
> #!/bin/bash****
>
> for db in $(echo "SHOW DATABASES;" | mysql -u$1 --password=$2 | grep -v -e
> "Database" -e "information_schema")****
>
> do****
>
> TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 |
> grep -v Tables_in_)****
>
> echo "Switching to database $db"****
>
> for table in $TABLES****
>
> do****
>
> echo -n " * Optimizing table $table ... "****
>
> echo "USE $db; OPTIMIZE TABLE $table" | mysql -u$1
> --password=$2 >/dev/null****
>
> echo "done."****
>
> done****
>
> done****
>
> ** **
>
> This is called using the following call:****
>
> cd /root/Desktop****
>
> ./optimizetable root EnterPasswordHere****
>
> ** **
>
> ** **
>
> **4) **Another useful tool I found was “mysqltuner.pl”:
> http://mysqltuner.pl/mysqltuner.pl . Google for the name, and download the
> perl script to your OTRS MySQL server. In essence it reads the log files of
> MySQL and makes recommendations how to improve the performance of MySQL.
> Your MySQL needs to be running quite some time in production for the
> recommendations to be accurate. We normally run the script, make appropriate
> modifications to the MySQL config, restart MySQL, reorganize all tables,
> then restart all services (OTRS, Apache, MySQL) and then let the server run
> in production for one or two weeks. Then we follow the same steps as above.
> After two weeks in normal production you have enough info in the log for the
> tool to make decent recommendations.****
>
> ** **
>
> The perl script is called using:****
>
> ** **
>
> cd /root/Desktop****
>
> perl mysqltuner.pl --user root --pass EnterPasswordHere****
>
> ** **
>
> The above steps dramatically improved the performance of OTRS.****
>
> ** **
>
> Hope this helps.****
>
> ** **
>
> Regards****
>
> ** **
>
> Rudolf Bargholz****
>
> ** **
>
> *Von:* otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] *Im Auftrag
> von *Wagner
> *Gesendet:* Donnerstag, 25. August 2011 22:43
> *An:* User questions and discussions about OTRS.
> *Betreff:* [otrs] Otrs with lighttpd + fastcgi****
>
> ** **
>
> Hello,
>
> has anyone tried using lighttpd + fascgi instead of apache + mod_perl?
>
> My otrs is very slow, so I was wondering if this could help
>
> Thanks****
>
> ---------------------------------------------------------------------
> OTRS mailing list: otrs - Webpage: http://otrs.org/
> Archive: http://lists.otrs.org/pipermail/otrs
> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
>
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
