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 <bargholz@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-bounces@otrs.org [mailto:otrs-bounces@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