Re: [rt-users] Problem with my.cnf, InnoDB and permissions

2007-10-24 Thread Mathew Snyder
Huw Selley wrote:
> Hi Mathew,
> 
> On 24/10/07 1:13, "Mathew Snyder" <[EMAIL PROTECTED]> wrote:
> 
>> We're having performance issues which I'm trying to nail down.  One thing
> 
> I would try find the bottleneck before changing anything ;)
> 
> What makes you believe the database is causing your performance issues?
> 

I'm really only guessing based on the length of time it takes to return tickets
sometimes.  We've found that if a ticket has multiple documents attached to it
or even just several transactions it takes a significantly long time to display.
 I've been thinking about turning on slow_query_logging but have been
sidetracked by other things since I've started looking at this problem.

>> which
>> I wonder might be the issue is the fact that the RT database is configured to
>> use InnoDB but the my.cnf file has all InnoDB-related options commented out.
> 
> That should have only mattered when you created the database (the table type
> is set then). If there was no Inno support iirc mysql will use the default
> storage engine (normally MyISAM unless you have changed it) when it creates
> the tables. You can check what your default storage engine is by running
> 'show engines'. One of them should fess up to being the default.
> 
> You can check the storage engine on a table by table basis by running 'show
> table status' (after selecting your rt database) e.g:
> 
> mysql> use rt;
> Database changed
> mysql> show table status\G
> *** 1. row ***
>Name: ACL
>  Engine: InnoDB
> Version: 10
>  Row_format: Compact
>Rows: 217
>  Avg_row_length: 226
> Data_length: 49152
> Max_data_length: 0
>Index_length: 16384
>   Data_free: 0
>  Auto_increment: 565
> Create_time: 2007-05-03 15:48:27
> Update_time: NULL
>  Check_time: NULL
>   Collation: latin1_swedish_ci
>Checksum: NULL
>  Create_options: 
> Comment: InnoDB free: 6144 kB
> 

Did that.  That's why I started looking at the InnoDB options.

> It should then dump you the status of all tables. You probably just want to
> check the value for 'Engine:' I suspect that (provided Inno was disabled
> when you created the database) they would be MyIsam.
> 
> If your tables are MyIsam then that could be causing your problems. MyIsam
> performs a table lock when trying to write a row, if you have multiple
> writes on the same table they have to wait for the table lock to be released
> before they can do their work. This could make things go slowly.
> 
> Hope this helps
> Huw
> 
> 
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:

If you sign up for a new RT support contract before December 31, we'll take
up to 20 percent off the price. This sale won't last long, so get in touch 
today. 
Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745.


Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com


Re: [rt-users] Problem with my.cnf, InnoDB and permissions

2007-10-24 Thread Huw Selley
Hi Mathew,

On 24/10/07 1:13, "Mathew Snyder" <[EMAIL PROTECTED]> wrote:

> We're having performance issues which I'm trying to nail down.  One thing

I would try find the bottleneck before changing anything ;)

What makes you believe the database is causing your performance issues?
  
> which
> I wonder might be the issue is the fact that the RT database is configured to
> use InnoDB but the my.cnf file has all InnoDB-related options commented out.

That should have only mattered when you created the database (the table type
is set then). If there was no Inno support iirc mysql will use the default
storage engine (normally MyISAM unless you have changed it) when it creates
the tables. You can check what your default storage engine is by running
'show engines'. One of them should fess up to being the default.

You can check the storage engine on a table by table basis by running 'show
table status' (after selecting your rt database) e.g:

mysql> use rt;
Database changed
mysql> show table status\G
*** 1. row ***
   Name: ACL
 Engine: InnoDB
Version: 10
 Row_format: Compact
   Rows: 217
 Avg_row_length: 226
Data_length: 49152
Max_data_length: 0
   Index_length: 16384
  Data_free: 0
 Auto_increment: 565
Create_time: 2007-05-03 15:48:27
Update_time: NULL
 Check_time: NULL
  Collation: latin1_swedish_ci
   Checksum: NULL
 Create_options: 
Comment: InnoDB free: 6144 kB

It should then dump you the status of all tables. You probably just want to
check the value for 'Engine:' I suspect that (provided Inno was disabled
when you created the database) they would be MyIsam.

If your tables are MyIsam then that could be causing your problems. MyIsam
performs a table lock when trying to write a row, if you have multiple
writes on the same table they have to wait for the table lock to be released
before they can do their work. This could make things go slowly.

Hope this helps
Huw



s2s company email disclaimer: 
http://www.s2s.ltd.uk/datasheets/email_disclaimer.pdf
s2s company registration number : 3952958
s2s VAT registration number : GB763132055
Business premises   : Ground Floor, Overline House, Crawley, West 
Sussex, RH10 1JA
Registered address  : 29 High Street, Crawley, West Sussex, RH10 1BQ
Place of registration   : England

___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:

If you sign up for a new RT support contract before December 31, we'll take
up to 20 percent off the price. This sale won't last long, so get in touch 
today.
Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745.


Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com


[rt-users] Problem with my.cnf, InnoDB and permissions

2007-10-24 Thread Mathew Snyder
We're having performance issues which I'm trying to nail down.  One thing which
I wonder might be the issue is the fact that the RT database is configured to
use InnoDB but the my.cnf file has all InnoDB-related options commented out.

I've uncommented these on our development server without issue.  I'm able to log
in and see everything that I was able to see before.  However, when doing so on
our production database I am only able to see the self-service page.

The development server has a different queue/rights/groups layout but I don't
know how that can have an affect.  The only thing I can think of is that the
InnoDB engine is seeing a different database for some reason.

Any thoughts?
-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:

If you sign up for a new RT support contract before December 31, we'll take
up to 20 percent off the price. This sale won't last long, so get in touch 
today. 
Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745.


Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com