vicidial_manager as memory table - high query time

[okli]

Hi,

I am chasing a few issues and noticed at slow queries log file often occurrences of this:

Code: Select all

# Time: 151120 14:10:08
# User@Host: cron[cron] @  [10.2.1.13]
# Query_time: 6.867697  Lock_time: 0.001789 Rows_sent: 0  Rows_examined: 6904662
SET timestamp=1448028608;
delete from vicidial_manager where server_ip='10.2.1.13' and entry_date < '2015-11-20 13:10:01';

Query_time: 6.867697 and Rows_examined: 6904662 are worrying me.
vicidial_manager had been converted to memory table quite a while ago, before I started looking after this cluster and every time I've checked it has like 30-40000 entries in it, nothing like several millions.

Code: Select all

CREATE TABLE `vicidial_manager` (
   `man_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
   `uniqueid` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `entry_date` DATETIME NULL DEFAULT NULL,
   `status` ENUM('NEW','QUEUE','SENT','UPDATED','DEAD') NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `response` ENUM('Y','N') NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `server_ip` VARCHAR(15) NOT NULL COLLATE 'utf8_unicode_ci',
   `channel` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `action` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `callerid` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_b` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_c` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_d` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_e` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_f` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_g` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_h` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_i` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_j` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   `cmd_line_k` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
   PRIMARY KEY (`man_id`),
   INDEX `callerid` (`callerid`),
   INDEX `uniqueid` (`uniqueid`),
   INDEX `serverstat` (`server_ip`, `status`),
   INDEX `server_action_cmd` (`server_ip`, `action`, `entry_date`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=MEMORY
AUTO_INCREMENT=8370035
;

What could be the reason for a query to a memory table to take this long and show Rows_examined: 6904662 ?

Any hints?

[mflorell]

Converting a table to a MEMORY table will also convert all VARCHAR fields to CHAR.

That means that in a standard MyISAM table, if "x" is stored in a VARCHAR(100) table, it will take up 1 byte of data in the database. In a MEMORY table, that same "x" value will take up 100 bytes in a VARCHAR(100) field.

This is the problem with using MEMORY tables with tables that have a lot of VARCHAR fields that may not frequently use all of their possible data lengths.

[okli]

Thanks Matt for the speedy response.

In such case, is it advisible to convert it back to MyISAM table? Or is there any way to keep it as memory but improve the speed of such queries by tuning mysql settings or table indexes?

I've given a good reading of everything I could find on the forum, but haven't found a definitive answer whether this and vicidial_live_agents table should be memory or not.

[mflorell]

It a balancing act really, for the vicidial_manager table on large systems, it's usually better to leave it as a MyISAM table. Other things you can do to help are to:
- Add more RAM to your database server
- Use a LSI Logic MegaRAID card with SSD drives for database data file storage
- Use separate RAIDs for "operating system/applications" and "database file storage"

[okli]

Thanks Matt, points taken.

[williamconley]

Pardon my intrusion, but as you'll note I often follow along after some of these conversations and try to be sure "obvious" things are happening. Things you probably know, but if you did NOT happen to check them or know them ... well, now is a good time. LOL

For instance: If you are experiencing slow queries, have you verified that anything in the "where" clause is indexed? Note that Vicidial has many users with many different viewpoints. The Vicidial Group has assigned indexes for the most obvious/normal usage scenarios. But that in no way means that you don't have a special-case scenario where a fresh index would not help. Of course, if The Vicidial Group put ALL the "cool, good reason for them" indexes in place, the whole system would screech to a crawl from index overload.

Indexing can be crucial during the where clause and can bring a 60 second query down to under 1 second. Not to mention they are free and you can delete them at will. LOL

Also, if that query is locking the system, it's also possible that it was delayed in starting by another process which caused an overload and slowed it down. Not often, but I've seen it on heavy-usage systems where mysql is already overloading the CPU.

Next up, this particular query is a housekeeping query. It's not involved in any decisions at all. Thus it COULD be set to a low priority thus allowing it to be ignored until the mysql system has nothing better to do for a moment. I've never delved with this, but I've seen the option (forget its name, but actually I think there are several) and stored it for future use. May come in handy in this particular situation. Especially since this "write" command will cause all the "read" commands behind it to pause while it does its job.

You could also try adding a limiter to the query, or run it more often to reduce it's run time. Perhaps even break it out of the normal process and run it independently and very often to reduce its immediate cost (which will increase it's accumulated cost, but who cares?).

Rant over. Happy Hunting