Slow Update Queries under MariaDB-CodePudding

our server was updated from Ubuntu 16 to Ubuntu 20 with MariaDB. Unfortunately, the loading time of the website has become slower. Normally MariaDB should be faster than Mysql. I've found that, quite simply, update commands on the website take about 7 seconds sometimes. However, if I enter these update commands directly into the database via myphpadmin, they only take 0.0005ms.

It seems to me that MariaDB has a problem with update commands when they occur frequently. This was never a problem with mysql. Here's an query example:

UPDATE LOW_PRIORITY users
SET user_video_count = user_video_count   1
WHERE user_id = 12345

The database format is MyISAM.

I have no idea what could be the reason. Do you?

Thank you very much.

CodePudding user response：

As you are using MariaDB, you can use tools like EverSQL to find missing indexes or discover redundant indexes (e.g. you have an index on user_video_count that you don't really need)

CodePudding user response：

It may be something as simple as a SELECT searching for something in users. Note, InnoDB would not suffer this problem.

MyISAM necessarily does a table lock when doing UPDATE, INSERT, or DELETE. (Also ALTER and other DDL statements.) If there are a lot of connections doing any mixture of writes and even SELECTs, the locks can cascade for a surprisingly long time.

The real solution, whether in MariaDB or [especially] in MySQL, is to switch to InnoDB.

If this is a case of high volume counting of "likes" or "views", then a partial solution (in either Engine) is to put such counters in a separate, parallel, table. This avoids those simple and fast updates fighting with other actions on the main table. In an extremely high traffic area, gathering such increments and applying them in batches is warranted. I don't think your volume needs that radical solution.

MySQL has all-but-eliminated MyISAM. MariaDB may follow suit in a few years.

To address this:

the same query in myphpadmin its really fast

The problem is not with how you run it, but what else happens to be going on at the same time.

(LOW PRIORITY is a MyISAM-specific kludge that sometimes works.)

MyISAM does "table locking"; InnoDB does "row locking". Hence, Innodb can do a lot of "simultaneous" actions on a table, whereas MyISAM becomes serialized as soon as a write occurs.