Why not ask Config.pm for the credentials? On Tue, Aug 30, 2011 at 11:11 AM, Rory <[email protected]> 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<http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html>is 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 > TABLE<http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html>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<http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html>work on > other storage engines by starting > *mysqld* <http://dev.mysql.com/doc/refman/5.6/en/mysqld.html> with the > --skip-new or > --safe-mode<http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_safe-mode>option. > In this case, OPTIMIZE > TABLE <http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html> is just > mapped to ALTER TABLE<http://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 <[email protected]> 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 >
--------------------------------------------------------------------- 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
