The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table cause the problem. Any page that updates, inserts, or deletes an entry of the jobs "table" takes around 10 seconds to load. (E.G. UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324
)
SELECT
queries seem to execute as before, though they seem to be slower if an update is taking place at the same time.
The table has around 180,000 entries. I noticed in the PHPMyAdmin view, that in addition to the "normal" index on the primary field, there is an index on the "entry_date" field (see image). Could that be an issue in this case? I've no idea why an index on that field would have been created.
If not, what else could be the source of the problem? I've checked space on the disk, which seems OK. (7 GB available) according to df.
SHOW CREATE TABLE job\G
Create Table: CREATE TABLE `job` (
`job_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0',
`entry_date` date NOT NULL DEFAULT '0000-00-00',
`timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00',
`title` varchar(60) COLLATE latin1_german2_ci NOT NULL,
`description` text COLLATE latin1_german2_ci NOT NULL,
`start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL,
`address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
`status` tinyint(4) NOT NULL DEFAULT '0',
`cat_id` int(4) NOT NULL DEFAULT '0',
`price` decimal(4,2) NOT NULL DEFAULT '1.00',
`emailcount` smallint(5) NOT NULL DEFAULT '-1',
`emailcount2` int(11) NOT NULL DEFAULT '-1',
`recemailcount` int(11) NOT NULL DEFAULT '-1',
`archive` tinyint(4) NOT NULL DEFAULT '0',
`post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`job_id`),
KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
UPDATE - Firstly thank you to all the contributors. I appreciate it very much. So over the last couple of days, the problem stopped, which made trying to find what the issue might be even more difficult. But now it seems to have returned once again. Let me start by providing the machine type hosted on google cloud: g1-small (1 vCPU, 1.7 GB memory). I'll continue updating this with the further information that's been requested by those commenting.
(job_id)
and an index on(entry_date, job_id)
. Why would that be a problem? Neither of these 2 columns is updated.job_id
has 2 indexes. Possibly anUNIQUE
index and aprimary key.SHOW CREATE TABLE
shows only one index on(job_id)
, the PK