[imp] MySQL performance tuning.

[Shawn Robinson]

Add an index on turba_objects object_owner.  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


At 02:47 PM 2002/08/28 -0500, Christopher Crowley wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Hello -
>
>I am seeing high load on my IMP server. It also runs MySQL.  I am
>running MySQL 3.23.47.
>
>
>I apologize for the poor format of the tables in this email. I am
>trying to included all the relevant data.
>
>
>
>
>mysqladmin extended-status shows:
>
>| Handler_delete           | 2037       |
>| Handler_read_first       | 80         |
>| Handler_read_key         | 2568339    |
>| Handler_read_next        | 1954295    |
>| Handler_read_prev        | 0          |
>| Handler_read_rnd         | 0          |
>| Handler_read_rnd_next    | 3952263599 |     <<<<<<<<<<<<<<< wow!
>| Handler_update           | 293650     |
>
>
>
>
>Am I missing an index?
>
>
>
>
>mysql> show index from horde_prefs;
>+-------------+------------+----------+--------------+-------------+--
>- ---------+-------------+----------+--------+---------+
>| Table       | Non_unique | Key_name | Seq_in_index | Column_name |
>| Collation | Cardinality | Sub_part | Packed | Comment |
>+-------------+------------+----------+--------------+-------------+--
>- ---------+-------------+----------+--------+---------+
>| horde_prefs |          0 | PRIMARY  |            1 | pref_uid    |
>| A         |        NULL |     NULL | NULL   |         | horde_prefs
>| |          0 | PRIMARY  |            2 | pref_scope  | A         |
>|       NULL |     NULL | NULL   |         | horde_prefs |          0
>| | PRIMARY  |            3 | pref_name   | A         |       29349 |
>|     NULL | NULL   |         |
>+-------------+------------+----------+--------------+-------------+--
>- ---------+-------------+----------+--------+---------+
>3 rows in set (0.00 sec)
>
>mysql> show index from turba_objects;
>+---------------+------------+----------+--------------+-------------+
>- -----------+-------------+----------+--------+---------+
>| Table         | Non_unique | Key_name | Seq_in_index | Column_name
>| | Collation | Cardinality | Sub_part | Packed | Comment |
>+---------------+------------+----------+--------------+-------------+
>- -----------+-------------+----------+--------+---------+
>| turba_objects |          0 | PRIMARY  |            1 | object_id
>| | A         |       80311 |     NULL | NULL   |         |
>+---------------+------------+----------+--------------+-------------+
>- -----------+-------------+----------+--------+---------+
>1 row in set (0.00 sec)
>
>
>
>
>
>mysqlshow --status -p horde
>Enter password:
>
>
>+--------------------------+--------+------------+-------+------------
>- ----+-------------+-----------------+--------------+-----------+------
>- ----------+---------------------+---------------------+---------------
>- ------+----------------+---------+
>| Name                     | Type   | Row_format | Rows  |
>| Avg_row_length | Data_length | Max_data_length | Index_length |
>| Data_free | Auto_increment | Create_time         | Update_time
>|    | Check_time          | Create_options | Comment |
>+--------------------------+--------+------------+-------+------------
>- ----+-------------+-----------------+--------------+-----------+------
>- ----------+---------------------+---------------------+---------------
>- ------+----------------+---------+
>| horde_prefs              | MyISAM | Dynamic    | 29346 | 167
>|     | 4903008     | 4294967295      | 1215488      | 0         |
>|             | 2002-03-08 10:26:11 | 2002-08-28 14:20:07 |
>|           | pack_keys=1    |         | kronolith_events_new     |
>| MyISAM | Dynamic    | 9362  | 76             | 718876      |
>| 4294967295      | 135168       | 0         |                |
>| 2002-05-30 16:52:12 | 2002-08-28 14:16:46 |                     |
>|              |         | kronolith_events_new_seq | MyISAM | Fixed
>|     | 1     | 5              | 5           | 21474836479     | 2048
>|         | 0         | 9828           | 2002-05-30 16:55:26 |
>| 2002-08-28 14:11:52 |                     |                |
>|  | turba_objects            | MyISAM | Dynamic    | 80307 | 111
>|        | 8937140     | 4294967295      | 4404224      | 0         |
>|                | 2002-03-08 10:12:24 | 2002-08-28 14:18:56 |
>| 2002-03-08 10:13:36 | pack_keys=1    |         |
>+--------------------------+--------+------------+-------+------------
>- ----+-------------+-----------------+--------------+-----------+------
>- ----------+---------------------+---------------------+---------------
>- ------+----------------+---------+
>
>
>
>I looked in the mysql_create.sql script, and I see three tables that
>aren't in my horde DB: horde_user, horde_categories,
>horde_categories_categories.  How would the absence of these tables
>affect the performance?
>
>Thanks for reading through this very cluttered email.
>
>Christopher Crowley
>TIS Network Services
>Tulane University
>ccrowley@tulane.edu
>504.314.2535
>PGP Key ID: 0x7CF18FDB
>
>
>
>
>-----BEGIN PGP SIGNATURE-----
>Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>
>
>iQA/AwUBPW0ouc0q7cR88Y/bEQL78gCeKI4VdPygmWjSfF9lL+7YT1ZvTH0AoN6W
>LOo9zfP9qQWBGz7u1ryEkyix
>=Pzgi
>-----END PGP SIGNATURE-----
>
>
>
>--
>IMP mailing list
>Frequently Asked Questions: http://horde.org/faq/
>To unsubscribe, mail: imp-unsubscribe@lists.horde.org