On Tue, Aug 30, 2011 at 11:11 AM, Rory <rclerkin@gmail.com> 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 TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when 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 TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.
#####################
http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

Rory

On 30 August 2011 15:17, Michiel Beijen <michiel.beijen@gmail.com> 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