0

I'm not a MySql Server DBA. Two years ago I've created web service that send messages and inserts responses from remote web server in MySQL database (Server version: 5.6.16 - MySQL Community Server).

I use phpMyAdmin 4.6.6 on Windows server 2012 R2 Standard, on virtual machine with CPU Intel Xeon E5-2670 dual core @ 2.60 GHz and 4GB RAM.

Every day on average my web service inserts 5000 rows in MySQL and it takes it about 1 hour. After script stops I run SQL select whit joins to check if there was any errors. This is that select:

`select * from soap_req_env q, soap_res_env r, soap_res_err k, res_err_type t, soap_message g, soap_mssg_err s, mssg_err_type m
where q.messageid=g.messageid
and g.id=s.id
and s.errorcode=m.errorcode
and q.messageid=r.messageid
and r.gsbid=k.gsbid
and k.errorcode=t.errorcode
and DATE(q.sendertimestamp)="2016-03-06"
and (k.errorcode!="A000" or s.errorcode!="A000")`

Performance of database is and was slow when I run select above (about 5 minutes to get result in phpMyAdmin) Couple days ago after couple hours of running this join it timed out. Only difference between now and couple days ago is 20000 rows in database, database has approximately 3.4 M records (rows).

Only connections on database are made by my web service and myself. This is my database structure:

CREATE TABLE `mssg_err_type` (
  `ErrorCode` char(5) COLLATE utf8_croatian_ci NOT NULL,
  `ErrorType` text COLLATE utf8_croatian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `res_err_type` (
  `ErrorCode` char(4) COLLATE utf8_croatian_ci NOT NULL,
  `ErrorType` text COLLATE utf8_croatian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `soap_message` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `MessageId` binary(16) NOT NULL,
  `OIB` char(11) COLLATE utf8_croatian_ci NOT NULL,
  `MssgText` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `soap_mssg_err` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `ErrorCode` char(5) COLLATE utf8_croatian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `soap_req_env` (
  `MessageId` binary(16) NOT NULL,
  `SenderTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `soap_res_env` (
  `MessageId` binary(16) NOT NULL,
  `GsbId` binary(16) NOT NULL,
  `ReceiverTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `FinalTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

CREATE TABLE `soap_res_err` (
  `GsbId` binary(16) NOT NULL,
  `ErrorCode` char(4) COLLATE utf8_croatian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;


ALTER TABLE `mssg_err_type`
  ADD UNIQUE KEY `ErrorCode` (`ErrorCode`);

ALTER TABLE `res_err_type`
  ADD UNIQUE KEY `ErrorCode` (`ErrorCode`);

ALTER TABLE `soap_message`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MessageId` (`MessageId`,`OIB`);

ALTER TABLE `soap_mssg_err`
  ADD KEY `ID` (`ID`),
  ADD KEY `ErrorCode` (`ErrorCode`);

ALTER TABLE `soap_req_env`
  ADD PRIMARY KEY (`MessageId`);

ALTER TABLE `soap_res_env`
  ADD PRIMARY KEY (`GsbId`),
  ADD KEY `MessageId` (`MessageId`);

ALTER TABLE `soap_res_err`
  ADD UNIQUE KEY `GsbId` (`GsbId`,`ErrorCode`),
  ADD KEY `ErrorCode` (`ErrorCode`);


ALTER TABLE `soap_message`
  MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4174893;
ALTER TABLE `soap_mssg_err`
  MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4174893;

ALTER TABLE `soap_message`
  ADD CONSTRAINT `soap_message_ibfk_1` FOREIGN KEY (`MessageId`) REFERENCES `soap_req_env` (`MessageId`);

ALTER TABLE `soap_mssg_err`
  ADD CONSTRAINT `soap_mssg_err_ibfk_3` FOREIGN KEY (`ID`) REFERENCES `soap_message` (`ID`),
  ADD CONSTRAINT `soap_mssg_err_ibfk_4` FOREIGN KEY (`ErrorCode`) REFERENCES `mssg_err_type` (`ErrorCode`);

ALTER TABLE `soap_res_env`
  ADD CONSTRAINT `soap_res_env_ibfk_1` FOREIGN KEY (`MessageId`) REFERENCES `soap_req_env` (`MessageId`);

ALTER TABLE `soap_res_err`
  ADD CONSTRAINT `soap_res_err_ibfk_2` FOREIGN KEY (`ErrorCode`) REFERENCES `res_err_type` (`ErrorCode`),
  ADD CONSTRAINT `soap_res_err_ibfk_3` FOREIGN KEY (`GsbId`) REFERENCES `soap_res_env` (`GsbId`);

I have indexes on every table in database. I have tried running select below but still no result (it timed out):

select count(*) 
from soap_req_env q, soap_res_env r, soap_res_err k, res_err_type t, soap_message g, soap_mssg_err s, mssg_err_type m
where q.messageid=g.messageid
and g.id=s.id
and s.errorcode=m.errorcode
and q.messageid=r.messageid
and r.gsbid=k.gsbid
and k.errorcode=t.errorcode
and DATE(q.sendertimestamp)="2017-03-06" 

Result of EXPLAIN Select is:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  r   index   PRIMARY,MessageId   MessageId   16  NULL    3135609     Using index
1   SIMPLE  g   ref     PRIMARY,MessageId   MessageId   16  okp_php.r.MessageId     1   Using index
1   SIMPLE  s   ref     ID,ErrorCode    ID  8   okp_php.g.ID    1   NULL
1   SIMPLE  m   eq_ref  ErrorCode   ErrorCode   15  okp_php.s.ErrorCode     1   Using index
1   SIMPLE  q   eq_ref  PRIMARY     PRIMARY     16  okp_php.r.MessageId     1   Using where
1   SIMPLE  k   ref     GsbId,ErrorCode     GsbId   16  okp_php.r.GsbId     1   Using index
1   SIMPLE  t   eq_ref  ErrorCode   ErrorCode   12  okp_php.k.ErrorCode     1   Using index

How can I improve MySQL performance?

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • Can you provide and EXPLAIN SELECT of the query? – JPeck89 Mar 7 '17 at 12:41
0

I can suggest You:

Change code for use JOINS

select 
    count(*) 
from 
soap_req_env q 
INNER JOIN soap_res_env r ON q.messageid=r.messageid
INNER JOIN soap_res_err k ON r.gsbid=k.gsbid
INNER JOIN res_err_type t ON k.errorcode=t.errorcode
INNER JOIN soap_message g ON q.messageid=g.messageid
INNER JOIN soap_mssg_err s ON g.id=s.id
INNER JOIN mssg_err_type m ON s.errorcode=m.errorcode

where 
    q.sendertimestamp >="2017-03-06 00:00:00" AND q.sendertimestamp <= "2017-03-06 23:59:59"

remove DATE function from timestamp, in more

and add index to timestamp column

Insert also very slow, but it could be next tasks The main idea of changes:

You have only 1 column in this query, which can reduce number of rows, and You not use this column. DATE() == full table scan. So we must use any possible chance for narrow filter conditions, and any chance for use indexes for query

0

Add the following index:

ALTER TABLE soap_req_env
  ADD KEY `SenderTimeStamp` (`SenderTimeStamp`);

Query re-written as:

SELECT COUNT(q.MessageId)
FROM soap_req_env q
INNER JOIN soap_message g ON g.MessageId=q.MessageId
INNER JOIN soap_mssg_err s ON s.ID=g.ID
INNER JOIN mssg_err_type m ON m.ErrorCode=s.ErrorCode
INNER JOIN soap_res_env r ON r.MessageId=q.MessageId
INNER JOIN soap_res_err k ON k.GsbId=r.GsbId
INNER JOIN res_err_type t ON t.ErrorCode=k.ErrorCode
WHERE q.SenderTimeStamp BETWEEN '2017-03-06 00:00:00' AND '2017-03-06 23:59:59';
0

Aside from fixing the DATE usage, there are other potential issues.

  • What are you counting? Often, when doing JOINs, there is an explosion of rows. And then you get a COUNT(*) of the resulting explosion. Are you sure you are getting the right value?
  • Be sure the datatypes involved on JOIN are "the same". I see that err_code has 2 different definitions; this could be causing trouble.
  • When using utf8, avoid CHAR; switch to VARCHAR.
  • BINARY(16) smells like a UUID or MD5; is it? Those are terribly random, hence INSERTs can be very slow. If so, let's discuss further.
  • soap_message has two UNIQUE keys; get rid of ID if practical.
  • When there is no PRIMARY KEY, upgrade the UNIQUE key to be PRIMARY.
  • soap_mssg_err seems to have no PRIMARY KEY.
  • and (k.errorcode!="A000" or s.errorcode!="A000") says "AND not both are A000"; it does not say "AND neither is A000". (Just checking that you specified the right thing.)
  • 1. I'am counting number of responses I've got. 2. Err_codes are same datatype char(4) and char (5) (example code A000 and KP010) – Cikson Mar 13 '17 at 12:35
  • 3. I'm going to 4. You already answered here: stackoverflow.com/questions/30481439/… 5.-7. I'm going to 8. Yes I've specified it right. – Cikson Mar 13 '17 at 12:42

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.