New answers tagged

0 votes

When do you need to run Analyze Table on MariaDB?

ANALYZE TABLE is rarely needed. Fortunately, it is quite fast, so it does not hurt to run occasionally. (This is unlike OPTIMIZE TABLE, which is even less useful and much slower.) Often a better ...
  • 70.5k
0 votes

Turn page compression off in MariaDB

InnoDB page compression can be enabled for just individual InnoDB tables -- https://mariadb.com/kb/en/innodb-page-compression/#enabling-innodb-page-compression In general, what you specify on CREATE ...
  • 70.5k
1 vote
Accepted

When I use group_concat on two fields, can I count on the two concatenations having the same order?

You are not guaranted that they will be in the same order, eventhogh it likely will happen. Not even adding an ORDER BY at the end of your query will guarantee it, since that is logically evaluated ...
0 votes

MariaDB: Missing FILE_CHECKPOINT

Something you can try, especially if you don't have any valuable data in the database: Make a backup copy of the data directory (retain the file ownerships and permissions), just in case. Set ...
  • 5,861
0 votes

MariaDB uses all /tmp space

i have had similar problem for a long time. what solves this without having to restart is to issue: FLUSH LOGS; this instantly frees up over 400gb of unused disk space and there is no files present. ...
1 vote
Accepted

MariaDB Multi level replication (replicate from slave)

Both of these are needed: log_bin log_slave_updates Be aware that if the Primary in the second DC goes down, the Replicas in the same DC will not be receiving updates. A partial workaround is to ...
  • 70.5k
0 votes

High Load MariaDB Cluster (Galera Multi Master Replication) - Same hardware needed for Slaves?

You have Galera active, but are using it as a "single-Primary" with "multiple Replicas", correct? All writes occur on all servers. This is a fact of any replication topology. ...
  • 70.5k
0 votes

High Load MariaDB Cluster (Galera Multi Master Replication) - Same hardware needed for Slaves?

I want to add Slaves to get "realtime" Backups of the Cluster Your use case is still unclear. If you want the standby nodes to accept the application workload if the main cluster is ...
  • 22.4k
0 votes

Can a redundant index improve SELECT performance?

Assuming each table has PRIMARY KEY(id) (which is unclear from the phrasing in the question), you effectively added INDEX(a, id) Meanwhile, left join other O on O.a = M1.a turns into INNER join ...
  • 70.5k
0 votes
Accepted

Can a redundant index improve SELECT performance?

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index. ... A developer added a new index in the magic table on just (a) (which is a ...
2 votes

How do I find out what's causing processes to wait for table flush?

I am going to hazard a guess that the first query at the top, as Rick said, is the cause of it, especially if it is an ANALYZE TABLE query. This is a rather typical cause of the queries piling up in ...
0 votes

Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)

WHERE col LIKE 'US%' will [probably] use the index that you already have, and use it efficiently. I say "probably" because if more than (about) 20% of the table matches, the index will be [...
  • 70.5k
0 votes

I can not install mariadb through dnf

dnf has various options to handle situations with conflicting packages. See e.g. --allowerasing, --skip-broken and --nobest. Check out the dnf man page for a more explanation on each. Have you ...
  • 5,861
0 votes

Mariadb: Sometimes inserts made during mysqldump are stuck forever in Updating state, and then service mariadb restart never finishes - how to debug?

So far it seemed like I/O got too busy, and was never able to catch up. I tweaked some InnoDB variables, installed jemalloc allocator (that wasn't enough), and in the end I doubled the instance RAM ...
5 votes
Accepted

Maria DB - Is there a point in indexing each uniq index cols separately?

I'll assume you are using InnoDB, the default storage engine. The indexes on individual columns colb and colc may be used in a query searching on those columns. The three-column index cannot be used ...
  • 10.5k
2 votes
Accepted

How can I change the REFERENCE in a FOREIGN KEY?

You could follow a three step process. 1. Identify the foreign keys names from the tables In your example show create table user_events would give the foreign key name . You can find al the foreign ...
  • 1,807
0 votes

MySQL INSERT INTO SELECT vs BULK INSERTs performance

Since nobody has answered it up to now like this, I dare to fill the gap. "batch insert", not "bulk insert" First of all, "bulk insert" might better be replaced by "...
0 votes

Make data rotation with MariaDB database

An alternative solution to this problem could be to implement a ring buffer. This is possible in MariaDB since version 10.3. The ring buffer idea was first described on Federico Razzoli's old blog ...
  • 5,861
0 votes

Make data rotation with MariaDB database

Suggest weekly partitions, else you need to hold nearly 4 months worth before dropping the oldest. More discussion of partitioning for time-series: Partition Once you have debugged the code, there ...
  • 70.5k
1 vote
Accepted

Importing working MariaDB innodb dump into new database and receiving Error 1071 Key too long

That's a significant downgrade. This problem may not be the only one. Anyway, see the following for 5 ways of addressing what I susupect it the problem: http://mysql.rjweb.org/doc.php/limits#...
  • 70.5k
0 votes

Reduce your overall MySQL memory footprint for system stability, Dedicate this server to your database for highest performance

Swapping is terrible for performance. So, if mysql is swapping, lower innodb_buffer_pool_size to avoid swapping. Of course, if you lower that setting "too much", there will be other ...
  • 70.5k

Top 50 recent answers are included