Problem with my.cnf, InnoDB and permissions

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

Hi Mathew,

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

I would try find the bottleneck before changing anything :wink:

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

Huw Selley wrote:

Hi Mathew,

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

I would try find the bottleneck before changing anything :wink:

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.