6

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.

enter image description here

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.

6
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Commented Dec 10, 2020 at 15:32
  • I believe this question boils down to the fact that you have 2 indexes on the primary key. And that question already has an answer for version 5.5: dba.stackexchange.com/questions/75502/… (I strongly doubt it is any different on 5.6 or even 8.0). Commented Dec 11, 2020 at 10:01
  • @IsmaelMiguel where do you see duplicate indexes? There is the PK on (job_id) and an index on (entry_date, job_id). Why would that be a problem? Neither of these 2 columns is updated. Commented Dec 11, 2020 at 11:00
  • @ypercubeᵀᴹ job_id has 2 indexes. Possibly an UNIQUE index and aprimary key. Commented Dec 11, 2020 at 11:51
  • Where do you see that? The output of SHOW CREATE TABLE shows only one index on (job_id), the PK Commented Dec 11, 2020 at 14:06

3 Answers 3

6

To answer the specific question:

  • No, an extra index on (entry_date) or two would not hurt performance of updating a different, title column.

Additionally:

  • No, the version of MySQL, 5.6, is not too ancient, even if some modern features (like window functions) are missing. You should have decent performance with decent hardware.

We can only speculate on the issue in hand but what could be wrong or explain it:

  • old, inefficient hardware: check the disk specifications, measure it's preformance.

  • fragmented index/table. Check MySQL docs and old questions/answers here of how to defragment MyISAM tables (OPTIMIZE TABLE).

Last but not least:

  • Your table is using MyISAM engine, which is old news. InnODB has replaced it as the default engine in MySQL years ago. There is no active development of this engine. It lacks several features compared to InnoDB (transactions, foreign key constraints, etc) and performance in most work loads. I strongly suggest you change your table (after testing of course that your apps and procedures do not break) to use InnoDB.

  • Importantly to your Question, InnoDB can perform SELECT and UPDATE at the same time (usually). MyISAM completely locks the table; the update or select must finish before the select or update can even start.

  • When switching from MyISAM to InnoDB, be sure to adjust key_buffer_size and innodb_buffer_pool_size.

2
  • 1
    So the version of MySQL is not ancient but the engine selected is. Commented Dec 10, 2020 at 23:30
  • @CaptainMan 5.6 is not too ancient, no. It has however gone out of support on Feb 2018 and goes out of "Extended support" at Feb 2021. After that, one would have to pay Oracle for "Sustaining Support". I suggest anyone uses supported versions of any application in production, so yes, upgrading to 5.7 or even better to 8.0 would be good. But first the engine issue should be fixed for the OP's problem. Commented Dec 11, 2020 at 9:40
4

Indexes do slightly affect performance of INSERT and DELETE operations but generally this is negligible especially if your indexes are well planned and you don't go overboard by putting 30 indexes on the same table each with 30 different combinations of fields. (Generally I stick to a 5 by 5 guideline - 5 indexes max, 5 fields per index max. Of course this is just a guideline and not a hard rule).

That being said, indexes actually are used to increase performance of UPDATE and SELECT queries because the index is used to locate the rows to either UPDATE or SELECT.

The drastic change you're seeing is doubtful to be related to entry_date index you discovered (do you know if that was recently added or has been there already prior to the performance change?).

Two things you should look into is:

  1. If the previous indexes for your queries are still being used, especially for UPDATE and SELECT queries (and if they are being seeked on or is a scan operation occuring now). This can change due to changes in table statistics of the data over time. You can use the ANALYZE statement to check the statistics of a table.

  2. The other thing you can look into is index fragmentation, which is a natural occurrence over time. This happens as more data is added to a table. (Generally this shouldn't be a concern on a small table like yours, but it's worth checking into it anyway.)

I'll continue to update my answer with more things to look into as I think of them. Running an EXPLAIN on your queries may help clue you in on the issue too. Is try to separate the UPDATE and INSERT statement that are running slowly and try to understand if they are being affected by the same root cause (since again they act somewhat inversely to indexes, INSERT are negligibly slower but UPDATEs usually benefit from the correct index and should be faster.)

0

OPTIMIZE TABLE job; to eliminate fragmentation and have all indexes recreated.

Then check your UPDATE query timing.

View profile, Network profile for free downloadable Utility Scripts to assist with performance tuning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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