Mysql – InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST;

Basically, I keep a table of every logged in customer and update their location periodically. During busy times of the day, I will see this backup where the time to execute is well over 5 minutes. Eventually, they will all process through and the DB CPU and Connections will normalize (CPU will hover around high 90%s).

The queries I run on this table are INSERT and UPDATE only, and those are the ones that will randomly get backed up during a high traffic time of day. I can't seem to figure out why this happens, although it always happens between the 3 hour block of our busiest time.

Here is my database create statement:

CREATE TABLE `active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`public_post` tinyint(1) NOT NULL,
`customer_id` int(11) NOT NULL,
`order_type` varchar(75) NOT NULL,
`clocked_in` tinyint(1) DEFAULT NULL,
`auto_clock_out` datetime NOT NULL,
`clocked_out` datetime NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lat` varchar(60) NOT NULL,
`lng` varchar(60) NOT NULL,
`region_id` int(11) DEFAULT NULL,
`description` varchar(140) DEFAULT NULL,
`date` varchar(15) NOT NULL,
`time` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `clocked_in` (`clocked_in`),
KEY `lat_lng` (`lat`,`lng`),
KEY `customer_id` (`customer_id`),
KEY `region_id` (`region_id`),
KEY `public_post` (`public_post`),
KEY `auto_clock_out` (`auto_clock_out`),
KEY `order_type` (`order_type`),
KEY `clocked` (`clocked_out`,`clocked_in`)
) ENGINE=InnoDB AUTO_INCREMENT=222952 DEFAULT CHARSET=latin1;

Here are the results of SHOW PROCESSLIST;

104224  proddb  172.31.52.67:55238  api Query   848 update  INSERT INTO active (customer_id, clocked_in) VALUES('188878', 0)
105154  proddb  172.31.52.68:53930  api Query   829 update  INSERT INTO active (customer_id, clocked_in) VALUES('188879', 0)
107401  proddb  172.31.25.4:46032   api Query   784 update  INSERT INTO active (customer_id, clocked_in) VALUES('188880', 0)
108101  proddb  172.31.18.216:54461 api Query   771 update  INSERT INTO active (customer_id, clocked_in) VALUES('188881', 0)
108567  proddb  172.31.52.67:56045  api Query   763 update  INSERT INTO active (customer_id, clocked_in) VALUES('188882', 0)
109002  proddb  172.31.32.221:41667 api Query   755 update  INSERT INTO active (customer_id, clocked_in) VALUES('188883', 0)
114304  proddb  172.31.52.67:57048  api Query   659 update  INSERT INTO active (customer_id, clocked_in) VALUES('188884', 0)
114779  proddb  172.31.52.68:55762  api Query   651 update  INSERT INTO active (customer_id, clocked_in) VALUES('188885', 0)
118410  proddb  172.31.18.216:56459 api Query   588 update  INSERT INTO active (customer_id, clocked_in) VALUES('188886', 0)
119345  proddb  172.31.52.68:56702  api Query   567 update  INSERT INTO active (customer_id, clocked_in) VALUES('188887', 0)

Here are some statistics on the number of temporary tables and threads being created during a high traffic time I found while trying to diagnose.

| Created_tmp_disk_tables | 168686 |
| Created_tmp_tables      | 599149

| Threads_connected | 232 |
| Threads_created   | 21566 |
| Threads_running   | 107 |