0

I've got a mysql database with about 4.2m rows in a particular table of interest. The data consists mostly of and id, latitude, longitude, as well as some other metadata.

Since really only a very small percentage (the most recent) of the data is relevant; most of the data is really just for record. It's up to 1.7gb in size and more recently I've been watching mysql spike in CPU usage up to 400%.

It goes without saying that it would be a good idea to clean out unused data, but if it shouldn't be affecting performance then for the time being I'd prefer to keep it and investigate more pressing issues (like this one).

I read through MySQL high CPU usage [closed] with some good suggestions, but before testing too many out on our production database. I'm curious if this, what seems to me, some what obvious solution is indeed an answer.

I remember reading that with InnoDB deleting rows won't necessarily increase performance but can't find where. Perhaps someone can confirm if this is true.

Here is the SHOW CREATE TABLE item:

CREATE TABLE `item` (
  `encounter_id` varchar(50) NOT NULL,
  `spawnpoint_id` varchar(255) NOT NULL,
  `item_id` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `disappear_time` datetime NOT NULL,
  PRIMARY KEY (`encounter_id`),
  KEY `item_spawnpoint_id` (`spawnpoint_id`),
  KEY `item_item_id` (`item_id`),
  KEY `item_disappear_time` (`disappear_time`),
  KEY `item_latitude_longitude` (`latitude`,`longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

An example of a query I might run would be:

SELECT * FROM item WHERE latitude > 38 AND latitude < 37

Community
  • 1
  • 1
klvs
  • 1,074
  • 6
  • 21
  • I don't see how removing rows would _not_ improve the performance of a full table scan, but if the majority of your interaction with the table takes place via indices, then removing rows might have a smaller impact. – Tim Biegeleisen Sep 16 '16 at 04:36
  • InnoDB just hides the rows. – sectus Sep 16 '16 at 04:42
  • How would I find out if that's the case? My queries are by latitude, longitude, and time (within the last 15 minutes) if that is at all important. I never configured anything with indices, but after reading a bit it sounds like mysql is indexing the entire table. – klvs Sep 16 '16 at 04:46
  • klvs, does this table house IP Address locations? The reason I ask is that's theirs a few tricks to speed up IP searches. I take it the "Select" statement is chewing the CPU, can you show the statement and the database structure. – Henry Sep 16 '16 at 04:46
  • henry, no it doesn't. Only latitude, longitude, and time (within the last 15 minutes). I would have to reconstruct by hand the statement as it's being created by loopback.js Though if you think it important still I can. – klvs Sep 16 '16 at 04:49
  • 1
    _You_ have to declare indexes. Please provide `SHOW CREATE TABLE` and the query that is not getting faster. Then we can discuss the specifics. (Without these details, were are playing a guessing game.) – Rick James Sep 16 '16 at 05:01
  • Rick James, I just updated the question with that info as per request. – klvs Sep 16 '16 at 06:37
  • The sample query most likely uses the `item_latitude_longitude`, therefore this query would not really gain much from the removal of records. But only you can check your queries (perhaps by turning on the slow queries log or running explains). I'm also curious how on Earth you can have a CPU usage higher than 100%... – Shadow Sep 16 '16 at 08:47

1 Answers1

0

There are a couple of things you could do to improve the performance. Deleting a heap of rows won't necessarily speed up your overall mysql performance (nothing noticeable anyway). Some things you could try are:

  1. Remove un-used indexes
  2. You could add more memory to your VM (place where the MySql server is hosted)
  3. Use SSD's instead of HDD

I suggest you read up on the following site, it's got a lot of tips that will help you:

http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-mysql.html

James111
  • 15,378
  • 15
  • 78
  • 121
  • 1
    Before asking the OP to invest money, perhaps we should determine if the performance could be improved in a developer way. – Shadow Sep 16 '16 at 05:45
  • That is one of the solutions the OP can consider using, if you read the link (idk if you did or not), there are multiple other solutions. I don't think I deserve a down vote @Shadow – James111 Sep 16 '16 at 16:55