Optimize mysql Blob on slow connection

295 Views Asked by At

I'm using mysql 5.5. My OS is Centos7. I have this simple structure

Create table myContent
(
TheId int PK
Content blob
);

The size of each blob is around 100KB. I need to optimize

select * from myContent limit N. 

Usually, I put N as 50 so it is about 5MB. It runs around 1 minute to retrieve. My internet speed is 10mbps.

The query was just fine until this corona pandemic happens and my internet provider slows the internet down a lot. I then subscribed to faster speed to 20mbps and my query is still super slow (Limiting 5 rows (500KB) takes more than 1 minute). I can't change to another internet provider because it is the only provider I have in my area.

I checked my MySQL traffic and actually the traffic is slower than usual. There is no table lock as well. Are there any other things that I can do to MySQL?

EDIT: I think my explanation is not clear what my purpose is or what I'm trying to do. I tried to simply things so it won't get too complicated. I'm trying to create syncronization data between multiple local server using queue. All servers are located in different area and most of them is using the same internet provider. My blob is the result of compressed raw data. The blob is generated from each server that needs to be downloaded by another server. So here is my primary structure:

create table myQueue
(
    myQueueId int NOT NULL, -- PK
    serverId int DEFAULT NULL, -- this is the ID where the data is from
    blobId int DEFAULT NULL, -- the id of my blob table (TheId)
    timeupdate datetime, -- current time
    PRIMARY KEY (myQueueId),
    UNIQUE INDEX blobId (blobId),
    INDEX timeupdate(timeupdate)
);

CREATE TABLE downloadedServer (
    myQueueId int NOT NULL, -- PK
    serverId int(11) NOT NULL, -- PK
    serverIdDownloaded int(11) NOT NULL, -- PK
    PRIMARY KEY (myQueueId, serverId, serverIdDownloaded)
);

CREATE TABLE myContent
(
    TheId int NOT NULL, -- PK
    Content blob, -- this is the result of compressed data that is generated from the app that needs to be downloaded by another server. I tried my best so this is the very least amount of size that I can do
    PRIMARY KEY (TheId)
);

Everyday, the myQueue table is cleaned so it won't get bloated. My regular query in each server is as follows:

-- Check if there is any data that has to be downloaded
SELECT COUNT(*) FROM myQueue q
 WHERE q.serverId != vServerId -- make sure it's not from the same server
  AND NOT EXISTS (SELECT 1 FROM downloadedServer d WHERE
   d.serverIdDownloaded = vServerId AND
   d.myQueueId = q.myQueueId AND
   d.serverId = q.serverId);

-- Then I download the content that has to be downloaded using this query.
-- This query gets very slow even I reduced the limit N since my internet provider slows the internet down. The server that subscribed different provider doesn't have this problem.
SELECT q.myQueueId, q.serverId, c.Content 
 FROM myQueue q 
  JOIN myContent c ON q.BlobId=c.TheId
   WHERE q.serverId != vServerId -- make sure it's not from the same server
    AND NOT EXISTS (SELECT 1 FROM downloadedServer d WHERE
     d.serverIdDownloaded = vServerId AND
     d.myQueueId = q.myQueueId AND
     d.serverId = q.serverId)
    ORDER BY q.timeupdate LIMIT N;

After it is downloaded, the blob is decompressed from the server which just downloaded. It happens every 15 minutes.

My query select * from myContent limit 5 is for testing purpose why downloading 500KB data takes a long time and I'm wondering how I can make it faster in such a slow connection.

0

There are 0 best solutions below