[imp] MySQL performance tuning.

[Christopher Crowley]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just a follow up for what worked for me

I added the following to my /etc/my.cnf file:

log-slow-queries
log-long-format

This logs the queries which exceed the "slow-query" time, and queries
which do not use an index.  

There were 7,000 unindexed queries to the 10,000 record
kronolith_events table over an 8 hour period  recorded in the log.
This was the slow, overnight 8 hour time period, too. So, I also
added an index to kronolith_events:

create index calendar_id_idx on kronolith_events (calendar_id(8));

The first 8 characters are the only significant ones (for my
installation), the rest of the field contains the domain name, ie.
@tulane.edu so the index only covers the first 8 characters.

Thanks to this index and the one recommended by Shawn, the mysql
statistics look a lot better, and mysql isn't in the top 20 processes
by CPU use on the server anymore.

| Handler_delete           | 0      |
| Handler_read_first       | 0      |
| Handler_read_key         | 1515   |
| Handler_read_next        | 1601   |
| Handler_read_prev        | 0      |
| Handler_read_rnd         | 0      |
| Handler_read_rnd_next    | 732    |
| Handler_update           | 82     |
| Handler_write            | 129    |


Christopher Crowley
TIS Network Services
Tulane University
ccrowley@tulane.edu
504.314.2535
PGP Key ID: 0x7CF18FDB



> Add an index on turba_objects owner_id.  If you have lots of users 
> searching the address book, it's killer to do a table scan even if
> it's cached. We've got about 500K addressbook entries, and the
> index was necessary at  around 150K.
> 
> Regards,
> Shawn
> >
> >I am seeing high load on my IMP server. It also runs MySQL.  I am
> >running MySQL 3.23.47.
> >

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

iQA/AwUBPW+Bz80q7cR88Y/bEQKdXACeOqSwWQh4JRiRcbA9kunGJ9jeLXUAoPfw
cQLQ/y6UJVfASkUn1Vshntik
=eN78
-----END PGP SIGNATURE-----