New answers tagged

0 votes

Is my schema "correct"?

As mustaccio and Rick James stated in the comments, a schema is not inherently fast or slow. However, if you are concerned about performance when doing thousands of inserts in a SQLite database, I can ...
Andrea B.'s user avatar
  • 1,483
1 vote

Mariadb threads blocking randomly

We've upgraded Mariadb to version 10.6.17 5 days ago and haven't seen the issue again. Although we're still monitoring, its most likely resolved.
vamyip's user avatar
  • 121
0 votes

mysql table tables_priv index is corrupted and the best possible way to repair it

To address the corrupted index in the tables_priv table manually, consider the following steps: stop MySQL, and remove tables_priv.MAD, start MySQL, and run REPAIR TABLE mysql.tables_priv;. Verify ...
Arun Pratap's user avatar
0 votes

MariaDB 11.2 using too much RAM

Your my.cnf [server] section has Global Variable names that belong in my.cnf [mysqld] section for starters. Please also post in your question the complete content of the !include... config files ...
Wilson Hauck's user avatar
  • 1,730
0 votes

MariaDB to MySQL migration using AWS DMS

binlog_format is a dynamic system, so it will take effect on the next statement if set in a session. If it is set globally (this is what you want), the next connections will adopt the new ...
danblack's user avatar
  • 7,699
1 vote
Accepted

Need help on database design for 5 entities complex relations

Looking at your diagram, I think your difficulty starts where you mistakenly try to model "Languages" as a subordinate of "Lines", when it is actually an independent entity. Apart ...
Giannis Tsimarakis's user avatar
0 votes
Accepted

Fatal error 1408 when installing mariadb-server / mariadb-upgrade

Ended up submitting a bug report and got help from a MariaDB developer on how to fix this: https://jira.mariadb.org/browse/MDEV-33262. In case that link breaks, here was his summary of the problem: ...
Curtis Spencer's user avatar
1 vote

Select last record of every hour, but time is not precise

I found a solution. Maybe is suboptimal, but suits my needs... SELECT T.* FROM electric_meters AS T JOIN ( SELECT MAX(ts) as ts_max, DATE_FORMAT(DATE_ADD(ts, INTERVAL 55 ...
Ajax's user avatar
  • 111
0 votes

InnoDB High history list length grows indefinitely

The following may avoid the problem: If the UPDATEs are on "like" or "upvote" counters, remove the column from the main table. Instead, have a separate table with the counter; ...
Rick James's user avatar
  • 77.6k
1 vote
Accepted

The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value

Thanks very much for your response. I solve my problem by taking a Dedicated server instead of VPS. The old VPS was : 120Go RAM 24 Core AMD CPU NVMe as storage Ubuntu 22.04 The new is : 64Go RAM 18 ...
Nelson Nokimi's user avatar
0 votes

Select last record of every hour, but time is not precise

If window functions are supported in your version, you could try select `id`, `ts`, `id_sensor`, `value` from ( select `id`, `ts`, `id_sensor`, `value`, row_number() over ( ...
Alexander Peresypkin's user avatar
2 votes
Accepted

Words and their occurences in table column

CREATE TABLE test (id INT, slug VARCHAR(100)); INSERT INTO test VALUES (1, 'name1-name2-name3-name4-name5'), (2, 'name6-name2-name3-name4-name7'); SELECT jsontable.word, COUNT(*) amount FROM test ...
Akina's user avatar
  • 19.6k
1 vote

Words and their occurences in table column

The "best" way would be to properly Normalise your data. select * from table1 ; +----+-------+ | id | name | +----+-------+ | 1 | name1 | | 1 | name2 | | 1 | name3 | | 1 | name4 | | ...
Phill  W.'s user avatar
  • 8,548
2 votes
Accepted

MariaDB - How to add index to a generated VIRTUAL column

This seems to be an open issue: MDEV-18812, Add support for generated indexed virtual columns to aria. It was created 2019-03-04, so I would not hold my breath in wait for a solution For the sake of ...
Lennart - Slava Ukraini's user avatar
0 votes

Can't export MariaDB large table by mysqldump when row_format is compressed

"Lost connection ... during..." means that there was a timeout. Compressed data is uncompressed right after reading, so the dump should work (baring the timeout).
Rick James's user avatar
  • 77.6k
1 vote

Need help on database design for 5 entities complex relations

In my opinion, your task is simple enough to write code that works 'identically' in both MariaDB and MySQL. There are two "things" to think about: Entity -- Implement as a table with ...
Rick James's user avatar
  • 77.6k
0 votes

Mariadb threads blocking randomly

(Nothing jumps out as being helpful for the problem at hand.) Analysis of GLOBAL STATUS and VARIABLES: Observations: Version: 10.6.16-MariaDB-0ubuntu0.22.04.1 8 GB of RAM -- my ...
Rick James's user avatar
  • 77.6k
0 votes

Mariadb threads blocking randomly

These indexes might help: `tabGL Entry`: INDEX(company, docstatus, party_type, party, -- in any order account_currency) -- last tabDefaultValue: INDEX(defkey, parent) -- in ...
Rick James's user avatar
  • 77.6k
0 votes

The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value

Analysis of GLOBAL STATUS and VARIABLES: Observations: Version: 10.6.16-MariaDB-0ubuntu0.22.04.1 120 GB of RAM Uptime = 00:15:12; Please rerun SHOW GLOBAL STATUS after several hours. 6.19e+3 QPS ...
Rick James's user avatar
  • 77.6k
0 votes

Live database migration MariaDB 10.5 to MySQL 8.0

I had the same experience recently when trying to move to CloudSQL, I could not get MariaDB (source) to replicate to MySQL 8.0 (replica). Upon starting replication, as soon as a binary log got ...
OscarVanL's user avatar
  • 101
0 votes

The mariadbd process is at over 140% CPU utilisation despite a high innodb_buffer_pool_size value

Rate Per Second = RPS Suggestions for your OS Command Prompt to consider, ulimit -n 128000 # from 1024 to enable more Open Files / handles This is a dynamic variable and after testing will need to ...
Wilson Hauck's user avatar
  • 1,730
1 vote

Query RDS tables over time

What I want is a database where I can store this data which is not timestamped but that is received each day and be able to query it by the day it was received. How would this kind of database be ...
mustaccio's user avatar
  • 25.6k
0 votes

Is there known such big perfomance diff between current MySQL and MariaDB?

According to your timings, each row takes several milliseconds. Pretty much the only thing that can be this slow is flushing transaction logs to disk on commit, which means you did not execute the ...
bobflux's user avatar
  • 1,391
2 votes

Tool for monitoring changes to data in a database

You can enable the audit plugin in MariaDB or MySQL servers. Configure it to store the logs to a file and backup these files in S3. This is better than using CDC or using the mysqlbinlog utility since ...
jerichorivera's user avatar
1 vote

Tool for monitoring changes to data in a database

You could use a change data capture tool like Debezium https://debezium.io/ or Maxwell’s Daemon https://maxwells-daemon.io/ to read the binary log where every row modification is logged as an event ...
Alexander Peresypkin's user avatar
2 votes

Is there known such big perfomance diff between current MySQL and MariaDB?

You touched on something that might run a lot faster and simpler in MariaDB: INSERT INTO your_table (id, val) SELECT seq, FLOOR(RAND()*POW(10, FLOOR(15 * RAND()))) FROM seq_101_to_100000; ...
Rick James's user avatar
  • 77.6k
0 votes
Accepted

Queries causing MariaDB to get signal 7

suggestions to consider for your my.cnf [mysqld] section default-storage-engine = MYISAM myisam_use_mmap = 1 table_open_cache = 2048 open_files_limit = 6144 # or REMOVE thread_concurrency=32 ...
Wilson Hauck's user avatar
  • 1,730
0 votes

Queries causing MariaDB to get signal 7

I opened up a ticket with Redhat You should have opened a ticket with MariaDB, as the error message suggests. The server seems to try to access an invalid memory location while reading an index on ...
mustaccio's user avatar
  • 25.6k

Top 50 recent answers are included