0

I am a MySQL/MariaDB newbie.


I have an application that inserts lots of data, in short bursts, to a MariaDB database. The series of tests (or runs) stops consistently at 30 seconds and never completes (if I set the runtime in the app to 10 seconds it works, but is of no value).

DB and app are hosted on a Linux tower dedicated only to this application. It is connected to a NetApp for user directories.

A snippet of my processlist while the task is running:

Id  |user     |host                   |db  |command       |time|state             |info |progress
1345|myuser   |server.company.com:1234|MyDB|sleep         |268 |                  |      | 0.000
1319|DELAYED  |localhost              |MyDB|Delayed insert|131 |Waiting for insert|       |0.000

I have lots of connections on the DB in SLEEP state and one is always "Delayed insert"

What I have done thus far:

  1. delayed_queue_size from default to 5000
  2. vmstat to see if anything is blocking a process - none found
  3. deleting all the databases the app creates and trying over again

What can I do to fix this?

EDIT1:

Here are some new data points that I'm allowed to post:

I enabled logging and found the following:

[WARNING] aborted connection 1234 to db: 'mydb" user: 'myuser' host: 'company.com' (Got an error reading communication packets)

I used tcpdump and listened on the interface, lo (local host).

I found the following

  1. create table mytab.tab (myunique key) engine MyISAM commet='{"owner":"Me"}' INSERT ...
  2. A call to read a file from a NetApp, 10.XXX.XX.12:/vol/n02vol2/data
  3. The last line from the tcdump was: Select count(*) from information_schema.TAB (truncated)

The putty connection times out after 5 minutes of inactivity.

MariaDB is a 5 version

2
  • 1
    Have you read INSERT DELAYED and this ? Jan 10, 2023 at 19:30
  • From what I found online it seems to be related to replication .. But I'll read your link, thanks
    – Marinaio
    Jan 10, 2023 at 19:55

1 Answer 1

2

Sounds like you are using MyISAM. You should change to ENGINE=InnoDB.

InnoDB allows multiple concurrent INSERTs in many cases. The "delayed" feature of MyISAM is essentially unnecessary in InnoDB, and the keyword is probably just silently ignored.

I suspect you have encountered a case where MyISAM is slower (perhaps significantly) than InnoDB.

This may show a value of 1800 (30 minutes):

SHOW VARIABLES LIKE '%timeout';

If you could share the queries (both the dying one and the blocker) and SHOW CREATE TABLE, we might have further tips.

2
  • I did find MyISAM in the log. The DB is InnoDB | DEFAULT. Can I force the DB to ignore the call to the MyISAM engine? Does that really solve the "error reading communication packets" errors?
    – Marinaio
    Jan 11, 2023 at 17:23
  • @Marinaio - Yes, there is some such setting (I don't remember what it is.) Meanwhile, you should do ALTER TABLE name ENGINE=InnoDB; for each table. it will take a long time if the table is huge. I think the real problem is some long-running locks, not communication.
    – Rick James
    Jan 11, 2023 at 22:15

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.