AstRecipes » Optimizing QueueMetrics data access

If you run a very large call center and your queue_log table starts to get big (>1M lines), you can apply the following techniques to improve database performance.

1. Create a complete index

The default index used for access, called "partizione_b", is not very efficient for huge datasets because its granularity is aimed only at partition and time period. To make an idex such that all queries can be "solved" completely through the index, you should drop other indices and create this one:

Please note how we give a maximum size to each text field - make that as long as your largest entry on that field (see below).

2. Use short partition and queue names

The shorter names you use for your partitions and your queues, the more compact the index will be, so the faster will be the access. In no case make the queue index smaller than an used queue, or it wil have to go check on all rows if they match or not!

You can get information on how long are your fields by running the following queries:

and

Once you get this, I suggest using one or two characters for the partition (P1 or S3 or even A, B C...) and short names for the queues (withing 5-6 characters). Then you create the index as described in #1 using the new field lengths.

3. Make a log of slow queries
If your performace is suboptimal, you should make a log of slow queries. This is easily done in MySQL: see the article MySQL's Over-looked and Under-worked Slow Query Log. I suggest setting a limit of 5 seconds to tell a long query from a normal one.

You should then look for entries like:

This means that the query took 6 seconds to run, 12999 rows were sent to the client but 988943 were examined. Better indexing will make those divergences be smaller.

You can also run

To see a clearer report.

4. Optimize the table and its indices

The table should be optimized by partition and access time. You can do that by running:

This operation will take quite a while to perform and will lock the table while it's running, so do it only when the CC is not working.

5. Use a large key buffer size

On modern, multi-gigabyte servers there is no need to have a key_buffer_size of 8 megs! Make it at least twice of the key size for the queue_log table, like 128M or 256M

See: Tuning Server Parameters

This work is licensed under a Creative Commons License. AstRecipes is built upon Ow2wiki.