New answers tagged mariadb
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 ...
- 22.1k
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 ...
- 964
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 ...
- 101
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