0

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 |
0

Many things can be done to speed things up...

  • Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.)
  • Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.)
  • Don't split DATE and TIME; use DATETIME instead of VARCHAR. (Shrink and ease of use.)
  • Don't have a table with lots of fields, then spend most of your time INSERTing only two columns. (Perhaps you meant UPDATE?) Perhaps have a separate table with the log of clock-in/out?
  • How many different values are there of order_type? (Consider using an ENUM or "Normalizing" that field.)

Was that the entire PROCESSLIST? Or was something other than 2-column INSERTs operating on that table?

How much RAM do you have? What is the value of innodb_buffer_pool_size?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.