Already installed support module, and did everything it showed me. Orts is getting slow when I do actions like, creating a new ticket, or when a click in the + ou - button in the sysconfig module, as to add a new value to freetext. Is it normal to take about 1 minute to create a ticket? Thanks 2011/8/25 Marius Vaitiekunas

On Thu, Aug 25, 2011 at 11:42 PM, Wagner wrote:

...

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

Hello,

Try to install support module ant do everything, what it suggests. It should help. I am not sure, that your otrs is slow because of apache. --------------------------------------------------------------------- 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

If creating a ticket is very slow it could also be caused by various other issues. Creating a ticket also usually sends mail notifications. If you have a slow SMTP server this can take a long time. To alleviate this issue I usually like to install a local postfix that sends its messages to your SMTP server. OTRS can now send the messages to the local postfix directly and postfix will take care of queueing and delivery. Also, slow nameservers can be a performance bottleneck. Please make sure your DNS resolution is fast. Apart from that, of course proper tuning to your database and web server help. The vast majority of OTRS users deploys on Apache + mod_perl. Yes, you CAN deploy on FastCGI, and I think it might be faster on comparable hardware, but I have not yet seen a reliable configuration in production yet, only in PoC setups. If anyone has a proven configuration to share, of course, that would be interesting. -- Mike On Fri, Aug 26, 2011 at 00:37, Wagner wrote:

Already installed support module, and did everything it showed me.

Orts is getting slow when I do actions like, creating a new ticket, or when a click in the + ou - button in the sysconfig module, as to add a new value to freetext. Is it normal to take about 1 minute to create a ticket?

Thanks

2011/8/25 Marius Vaitiekunas

...

On Thu, Aug 25, 2011 at 11:42 PM, Wagner wrote:

...

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

Hello,

Try to install support module ant do everything, what it  suggests. It should help. I am not sure, that your otrs is slow because of apache. --------------------------------------------------------------------- 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

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 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

Hi Rory, Thanks for posting your modifications. Regards Rudolf Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Rory Gesendet: Freitag, 26. August 2011 16:34 An: User questions and discussions about OTRS. Betreff: Re: [otrs] Otrs with lighttpd + fastcgi 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

Hi Rudolf, Here's the quick answer. Copy the text of the script into a text file and save it as SQLOptimize.sh (or whatever you want to call it). Change the permissions to make it executable, something like 'chmod 755 ./SQLOptimize.sh" Execute the script by typing ./SQLOptimize.sh at the command prompt. (note: the ./ specifies that the file is in the same directory as you are working in). You will be prompted for the username and then the password. For the mysql part, the original line was this; TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_) I changed this to; TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$1 --password=$2 | grep -v Tables_in_) By removing the "USE $db" from the echo command and using the -D option in the mysql client it should now be running one less command on the database for every iteration of the for-loop. This is assuming the -D option is not using the USE $db command anyway. ################ ################ Here's the really really long answer to your questions which hopefully will help you better understand shell scripting in general. Forgive me for going over basic stuff that you probably already know. The original script has the following line; TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_) Breaking this apart we get the following; A variable is created called 'TABLES' and is assigned the value from the result of the commands to the right of the equals. The entire right hand side of the line is enclose by $( ). This groups everything together so that the result passed to the variable is whatever the contents between the brackets evaluates to. So the following line is executed by the shell; echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 | grep -v Tables_in_ Regarding the pipes, (the | character), this takes the output of the command on the left of the pipe and uses it as the input to the command on the right. The first part of the line; echo "USE $db; SHOW TABLES;" outputs the text between the quotations but substitues the content of the variable 'db' (variables are referenced using a '$'). Assuming the variable 'db' contains the string 'otrs' you would get the following output; USE otrs; SHOW TABLES; The output from the above text is 2 mysql commands, seperated by semi-colons. The first tells mysql to focus on a particular database and the second tells it to list all the tables in the database it is focused on. So the output of the echo command (the 2 mysql commands) is piped into the next command as input. The next part which is; mysql -u$1 --password=$2 Starts the mysql client (note: it doesn't use the absolute for mysql so it has to be in the PATH for this command to work). It uses the username which is stored in the variable $1 and the password stored in the variable $2. These numbered variable are special because they are gotten form the command used to initially start the script on the command line. If you're script was called SQLOptimize.sh and you used the following from the command line (I'm using $> to represent the shell prompt, you don't enter); $> ./SQLOptimize.sh dbuser dbpass dbname dbport blah The following variables would be available to your script $1 == dbuser $2 == dbpass $3 == dbname $4 == dbport $5 == blah In the script only $1 and $2 are used but you can see how you can get much more information from the user running the script just by separating the script parameters by a space. Back to the command at hand, the mysql client has been started and logged in using the username and password provided by the user. It then takes the input which has been piped through from the echo command, "USE otrs; SHOW TABLES;", and runs this in the mysql client. Note that this is 2 commands. Be cause this is in a loop within the script it must be run at every iteration of the loop. By changing the mysql command to include the -D option; mysql -D$db -u$1 --password=$2 The mysql client logs straight into the database specified in the $db variable. This means we don't need the "USE $db" command in the echo command from earlier which means that we have reduced them number of commands run by the mysql client in the loop by half. This should reduce the amount of resources used during the script. I also did the same thing in the nested for-loop as it also contains a call to the mysql client and had a USE $db entry. In my previous mail I had mentioned this might not make any difference because logging into the mysql client with the -D option may just run the USE $db command anyway. The output from the mysql portion of the line is as follows; Tables_in_otrs article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage This is piped into the next part of the command as input; grep -v Tables_in_ Grep is a pattern matching tool. You can use it to search line by line to find a string in a file or in this case a string in the output of another command. The '-v' option tells grep to invert the results. So if the string *is* found on a line then *do not* return the line and if the string *is not* found on the line then *do* return the line. So in the grep command from the script it will return every line that does not contain the string Tables_in_ So the output will be; article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage This results in only the list of tables being returned and no extra lines that are not tables. ######## For your question about how to call the script there are a few different elements to this to understand what's happening. Firstly, I'm assuming you're doing everything from a shell prompt/command line and not through a GUI. The script is a shell script i.e. you could write it out in the Linux shell and it'd work. It's just put in a script for convenience. A shell script will normally have the extension .sh, i.e. SQLOptimize.sh To make the script below usable you create a file using a regular editor such as 'vim' or 'nano', paste in the code and save with an appropriate name and extension, SQLOptimize.sh (you don't *need* the .sh extension, it'll work without it, but it helps to have it for when you are looking at a list of files) Before you can run the script you need to make it executable. This is a file permission and in Linux these can be edited using the command 'chmod'. It is used in the following format; chmod <options> <permission> <filename> The <options> are things like -R for recurrsive. The <filename> can use wildcards i.e. *.sh The <permission> is something that needs a bit more detail. Have a look at this webpage which gives some detail on how Unix permissions are written and how to set them using the chmod command; http://www.acm.uiuc.edu/webmonkeys/html_workshop/unix.html When calling a script often you won't need to do anything more than just enter the filename. So just entering SQLOptimize.sh at the shell prompt might work. This would work because there is an entry in you PATH for . (just a dot on its own). **** Aside: This is actually a security risk so hopefully it is not in your PATH. Consider if a malicious site downloaded a file called ifconfig to a folder on your machine. If your working directory was the same as the location the file was downloaded you would run the malicious ifconfig instead of the real one which would allow it to do whatever it likes as your user. Especially dangerous if you're logged in as root e.g. it could run "rm -rf /" deleting everything. **** In order to be specific about the file you wish to run you can use the relative or absolute path. The relative path uses your current working directory as the starting point to find the file you want to run. The absolute path is the full location of the file you want to run from the root directory. Your current working directory is specified by a dot on its own. So if you entered whats between the commas here 'cd .' you would be telling the system to change directory to your current working directory. If you entered 'cd ./scripts' then you're telling the system to change to the scripts directory which is a sub directory of your current working directory. Lets say your script is located at /home/user1/scripts/SQLOptimize.sh And your current working directory is /home/user1/scripts To run the script using a relative path you would use ./SQLOptimize.sh To run the script using the absolute path you would use /home/user1/scripts/SQLOptimize.sh If you run the script using the absolute path then it doesn't matter what your current working directory is. Because of this it is best to use absolute addressing when writing a script that references any outside files. ############################# #!/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 ############################# Sorry for the mammoth mail, I just thought I'd write all that out because it covers some fundamentals when working with Unix based systems and you did say you were just dabbling so I thought it might help. Rory On 29 August 2011 14:29, Rudolf Bargholz wrote:

Hi Rory,****

** **

For those of us that are not so well versed on Linux, could you perhaps post an example how you call your script, with the appropriate parameters. This makes it easier for us Windows users (me) dabbling in Linux to actually get your script working.****

** **

The one sentence I do not understand is “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”. An example would probably help resolve my questions.****

** **

Regards****

** **

Rudolf****

Hi Rory, but all your script does is OPTIMIZE TABLE for all OTRS tables, right? Actually, typically the only tables that need OPTIMIZEing are the sessions table and maybe the web_upload_cache tables; but you can also use the FS backends for session and web upload cache; then you don't have this issue, right? Or, in other words, most sites never optimize and don't have (real) issues. Also a fair word of warning, if you have InnoDB as the storage backend on MySQL; if you have deleted a large number of tickets (which would be the main reason for OPTIMIZEing) the statement creates a lock on the database and it can take a long time to complete; I've seen +1 hour with total database sizes of 20-30 GB when I deleted half the number of tickets, on moderate hardware. So while there can be reasons to do an OPTIMIZE it's generally not so much needed, and IF you need it, for example when you deleted lots of tickets; it can lock up your database so you really want to do this inside a maintenance window. There's also this script that you could use (at your own risk) if you need to do it on-line: http://www.vosoft.nl/perl/reduce_table.pl.txt -- Mike On Tue, Aug 30, 2011 at 13:45, Rory wrote:

Hi Rudolf,

Here's the quick answer.

Copy the text of the script into a text file and save it as SQLOptimize.sh (or whatever you want to call it). Change the permissions to make it executable, something like 'chmod 755 ./SQLOptimize.sh" Execute the script by typing ./SQLOptimize.sh at the command prompt. (note: the ./ specifies that the file is in the same directory as you are working in). You will be prompted for the username and then the password.

For the mysql part, the original line was this;

TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 |  grep -v Tables_in_)

I changed this to;

TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$1 --password=$2 |  grep -v Tables_in_)

By removing the "USE $db" from the echo command and using the -D option in the mysql client it should now be running one less command on the database for every iteration of the for-loop. This is assuming the -D option is not using the USE $db command anyway.

################ ################

Here's the really really long answer to your questions which hopefully will help you better understand shell scripting in general. Forgive me for going over basic stuff that you probably already know. The original script has the following line;

TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 |  grep -v Tables_in_)

Breaking this apart we get the following; A variable is created called 'TABLES' and is assigned the value from the result of the commands to the right of the equals. The entire right hand side of the line is enclose by $( ). This groups everything together so that the result passed to the variable is whatever the contents between the brackets evaluates to. So the following line is executed by the shell;

echo "USE $db; SHOW TABLES;" | mysql -u$1 --password=$2 |  grep -v Tables_in_

Regarding the pipes, (the | character), this takes the output of the command on the left of the pipe and uses it as the input to the command on the right. The first part of the line;

echo "USE $db; SHOW TABLES;"

outputs the text between the quotations but substitues the content of the variable 'db' (variables are referenced using a '$'). Assuming the variable 'db' contains the string 'otrs' you would get the following output;

USE otrs; SHOW TABLES;

The output from the above text is 2 mysql commands, seperated by semi-colons. The first tells mysql to focus on a particular database and the second tells it to list all the tables in the database it is focused on. So the output of the echo command (the 2 mysql commands) is piped into the next command as input. The next part which is;

mysql -u$1 --password=$2

Starts the mysql client (note: it doesn't use the absolute for mysql so it has to be in the PATH for this command to work). It uses the username which is stored in the variable $1 and the password stored in the variable $2. These numbered variable are special because they are gotten form the command used to initially start the script on the command line. If you're script was called SQLOptimize.sh and you used the following from the command line (I'm using $> to represent the shell prompt, you don't enter);

$> ./SQLOptimize.sh dbuser dbpass dbname dbport blah

The following variables would be available to your script $1 == dbuser $2 == dbpass $3 == dbname $4 == dbport $5 == blah In the script only $1 and $2 are used but you can see how you can get much more information from the user running the script just by separating the script parameters by a space.

Back to the command at hand, the mysql client has been started and logged in using the username and password provided by the user. It then takes the input which has been piped through from the echo command, "USE otrs; SHOW TABLES;", and runs this in the mysql client. Note that this is 2 commands. Be cause this is in a loop within the script it must be run at every iteration of the loop. By changing the mysql command to include the -D option;

mysql -D$db -u$1 --password=$2

The mysql client logs straight into the database specified in the $db variable. This means we don't need the "USE $db" command in the echo command from earlier which means that we have reduced them number of commands run by the mysql client in the loop by half. This should reduce the amount of resources used during the script. I also did the same thing in the nested for-loop as it also contains a call to the mysql client and had a USE $db entry. In my previous mail I had mentioned this might not make any difference because logging into the mysql client with the -D option may just run the USE $db command anyway.

The output from the mysql portion of the line is as follows;

Tables_in_otrs article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage

This is piped into the next part of the command as input;

grep -v Tables_in_

Grep is a pattern matching tool. You can use it to search line by line to find a string in a file or in this case a string in the output of another command. The '-v' option tells grep to invert the results. So if the string *is* found on a line then *do not* return the line and if the string *is not* found on the line then *do* return the line. So in the  grep command from the script it will return every line that does not contain the string Tables_in_ So the output will be;

article article_attachment article_flag article_plain article_search ..<SNIP>.. web_upload_cache xml_storage

This results in only the list of tables being returned and no extra lines that are not tables.

########

For your question about how to call the script there are a few different elements to this to understand what's happening.

Firstly, I'm assuming you're doing everything from a shell prompt/command line and not through a GUI. The script is a shell script i.e. you could write it out in the Linux shell and it'd work. It's just put in a script for convenience. A shell script will normally have the extension .sh, i.e. SQLOptimize.sh To make the script below usable you create a file using a regular editor such as 'vim' or 'nano', paste in the code and save with an appropriate name and extension, SQLOptimize.sh (you don't *need* the .sh extension, it'll work without it, but it helps to have it for when you are looking at a list of files) Before you can run the script you need to make it executable. This is a file permission and in Linux these can be edited using the command 'chmod'. It is used in the following format;

chmod <options> <permission> <filename>

The <options> are things like -R for recurrsive. The <filename> can use wildcards i.e. *.sh The <permission> is something that needs a bit more detail. Have a look at this webpage which gives some detail on how Unix permissions are written and how to set them using the chmod command; http://www.acm.uiuc.edu/webmonkeys/html_workshop/unix.html

When calling a script often you won't need to do anything more than just enter the filename. So just entering SQLOptimize.sh at the shell prompt might work. This would work because there is an entry in you PATH for . (just a dot on its own).

**** Aside: This is actually a security risk so hopefully it is not in your PATH. Consider if a malicious site downloaded a file called ifconfig to a folder on your machine. If your working directory was the same as the location the file was downloaded you would run the malicious ifconfig instead of the real one which would allow it to do whatever it likes as your user. Especially dangerous if you're logged in as root e.g. it could run "rm -rf /" deleting everything. ****

In order to be specific about the file you wish to run you can use the relative or absolute path. The relative path uses your current working directory as the starting point to find the file you want to run. The absolute path is the full location of the file you want to run from the root directory.

Your current working directory is specified by a dot on its own. So if you entered whats between the commas here 'cd .' you would be telling the system to change directory to your current working directory. If you entered 'cd ./scripts' then you're telling the system to change to the scripts directory which is a sub directory of your current working directory.

Lets say your script is located at /home/user1/scripts/SQLOptimize.sh And your current working directory is /home/user1/scripts

To run the script using a relative path you would use ./SQLOptimize.sh

To run the script using the absolute path you would use /home/user1/scripts/SQLOptimize.sh

If you run the script using the absolute path then it doesn't matter what your current working directory is. Because of this it is best to use absolute addressing when writing a script that references any outside files.

#############################

#!/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

#############################

Sorry for the mammoth mail, I just thought I'd write all that out because it covers some fundamentals when working with Unix based systems and you did say you were just dabbling so I thought it might help.

Rory

On 29 August 2011 14:29, Rudolf Bargholz wrote:

...

Hi Rory,

For those of us that are not so well versed on Linux, could you perhaps post an example how you call your script, with the appropriate parameters. This makes it easier for us Windows users (me) dabbling in Linux to actually get your script working.

The one sentence I do not understand is “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”. An example would probably help resolve my questions.

Regards

Rudolf

--------------------------------------------------------------------- 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

Why not ask Config.pm for the credentials? On Tue, Aug 30, 2011 at 11:11 AM, Rory wrote:

Rudolf, You're welcome. I kept thinking of extra bits of information that I felt were useful and after a while it turned out the message was huge and needed a summary! I hope some of it was useful.

Michiel, The script will iterate through all the tables in each database of the mysql server. It could easily be modified to ask for a database name and only optimize for that database. I'm sure you're right about the tables generally not needing to be optimized, I'm only good for pretty basic DBA tasks and the OPTIMIZE TABLE task seems like a good idea. I don't know too much about the database activity other than the obvious tables which will continue to grow. I wasn't aware of the performance issue with large databases. It might be best to hardcode the username and password into the script and run it as an out of hours cron job. I also came across the following when reading about the OPTIMIZE TABLE command which may, or may not, be useful.

##################### For InnoDB tables, OPTIMIZE TABLEhttp://dev.mysql.com/doc/refman/5.6/en/optimize-table.htmlis mapped to ALTER TABLE http://dev.mysql.com/doc/refman/5.6/en/alter-table.html, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLEhttp://dev.mysql.com/doc/refman/5.6/en/optimize-table.htmlwhen you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo; +----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+-------------------------------------------------------------------+ | test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.foo | optimize | status | OK | +----------+----------+----------+-------------------------------------------------------------------+

You can make OPTIMIZE TABLEhttp://dev.mysql.com/doc/refman/5.6/en/optimize-table.htmlwork on other storage engines by starting *mysqld* http://dev.mysql.com/doc/refman/5.6/en/mysqld.html with the --skip-new or --safe-modehttp://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_saf...option. In this case, OPTIMIZE TABLE http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html is just mapped to ALTER TABLEhttp://dev.mysql.com/doc/refman/5.6/en/alter-table.html . ##################### http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

Rory

On 30 August 2011 15:17, Michiel Beijen wrote:

...

Hi Rory, but all your script does is OPTIMIZE TABLE for all OTRS tables, right?

Actually, typically the only tables that need OPTIMIZEing are the sessions table and maybe the web_upload_cache tables; but you can also use the FS backends for session and web upload cache; then you don't have this issue, right? Or, in other words, most sites never optimize and don't have (real) issues.

Also a fair word of warning, if you have InnoDB as the storage backend on MySQL; if you have deleted a large number of tickets (which would be the main reason for OPTIMIZEing) the statement creates a lock on the database and it can take a long time to complete; I've seen +1 hour with total database sizes of 20-30 GB when I deleted half the number of tickets, on moderate hardware. So while there can be reasons to do an OPTIMIZE it's generally not so much needed, and IF you need it, for example when you deleted lots of tickets; it can lock up your database so you really want to do this inside a maintenance window.

There's also this script that you could use (at your own risk) if you need to do it on-line: http://www.vosoft.nl/perl/reduce_table.pl.txt

-- Mike

--------------------------------------------------------------------- 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