We are running a MySQL Innodb database, our server has 4gb memory. We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT
column. Currently, we have this query
CREATE DEFINER=`username`@`localhost` PROCEDURE `API_LogApiRequest`(sRequest
MEDIUMTEXT, sResponse MEDIUMTEXT)
BEGIN
DECLARE sUUID VARCHAR(36);
SELECT UUID() INTO sUUID;
INSERT INTO ApiRequest
(
Id,
Request,
Response
)
VALUES
(
sUUID,
sRequest,
sResponse
);
SELECT sUUID AS 'UUID';
END;
The sResponse
data can be as much as 800kb, and we are seeing it takes the following on 10 repeat calls. These calls were all made with the same data.
0.687 sec
0.078 sec
0.188 sec
0.718 sec
0.047 sec
0.031 sec
0.610 sec
0.313 sec
0.141 sec
0.032 sec
Here is the Innodb status
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 0, seg size 2, 560 merges
merged operations:
insert 560, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 49919, node heap has 1 buffer(s)
0.00 hash searches/s, 23.83 non-hash searches/s
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25772032; in additional pool allocated 0
Dictionary memory allocated 130139
Buffer pool size 1536
Free buffers 1024
Database pages 511
Old database pages 0
Modified db pages 11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1902256, not young 73968112
3.33 youngs/s, 52.77 non-youngs/s
Pages read 23821066, created 4126940, written 19043946
25.28 reads/s, 6.89 creates/s, 41.11 writes/s
Buffer pool hit rate 903 / 1000, young-making rate 12 / 1000 not 204 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 511, unzip_LRU len: 0
I/O sum[3805]:cur[242], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 7228, state: sleeping
Number of rows inserted 1340396, updated 304, deleted 1287534, read 12921295
2.78 inserts/s, 0.00 updates/s, 1.33 deletes/s, 4.11 reads/s
Here is my table structure
CREATE TABLE `ApiRequest` (
`ApiRequestId` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`Request` mediumtext COLLATE utf8_unicode_ci NOT NULL,
`Response` mediumtext COLLATE utf8_unicode_ci NOT NULL,
`EventTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ApiRequestId`),
KEY `IDX_EventTime` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
What do I need to change to improve the performance?
sUUID
set?sUUID
is set at the beginning of the SPSELECT UUID() INTO sUUID
. Those 10 calls were all made with the same data.