MariaDB views: I want to replace repeated multi-table joins in my queries with a view – are there any issues to watch out for?

Rather than

SELECT a.pk, b.pk, c.pk, d.name  FROM a JOIN b on b.pk = a.fk JOIN c on c.pk = b.fk JOIN d on d.pk = c.fk 

I can do

SELECT a_pk, b_pk, c_pk, d_name  FROM view_a_b_c_d 

I have a lot of this sort of thing through my code.

I’ve done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.

But before I commit to that (as it’d be a big change with a lot of work and testing), I want to check that this IS a good thing to do. I didn’t study computer science and have no formal DBA training. I’m also a sole dev working on my own closed-source product. So I don’t get much input from the outside world, unless I strike out and ask for it.

Thank you – any opinions/experience appreciated.

Slow inserts in mariadb columnstore

I have just started on researching the feasibility of using MariaDB’s columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with 8GB RAM. This is the table, a trigger and the timings:

MariaDB [test]> show create table analytics_test\G *************************** 1. row ***************************        Table: analytics_test Create Table: CREATE TABLE `analytics_test` (   `id` int(11) DEFAULT NULL,   `str` varchar(50) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4 1 row in set (0.000 sec)  MariaDB [test]> show create trigger test_trg\G *************************** 1. row ***************************                Trigger: test_trg               sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg before insert on analytics_test for each row begin   set new.str=concat('Value: ',new.id); end   character_set_client: utf8   collation_connection: utf8_general_ci     Database Collation: utf8mb4_general_ci                Created: 2021-01-07 11:14:28.81 1 row in set (0.000 sec)  MariaDB [test]> insert into analytics_test set id=1; Query OK, 1 row affected (0.817 sec)  MariaDB [test]> insert into analytics_test set id=2; Query OK, 1 row affected (0.560 sec)  MariaDB [test]> insert into analytics_test set id=3; Query OK, 1 row affected (0.611 sec)  MariaDB [test]> select * from analytics_test; +------+----------+ | id   | str      | +------+----------+ |    1 | Value: 1 | |    2 | Value: 2 | |    3 | Value: 3 | +------+----------+ 3 rows in set (0.085 sec) 

I think .5 sec for a simple insert is very slow – compare to the same table in innodb:

MariaDB [test]> show create table test\G *************************** 1. row ***************************        Table: test Create Table: CREATE TABLE `test` (   `id` int(11) DEFAULT NULL,   `str` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.000 sec)  MariaDB [test]> show create trigger test_trg1\G *************************** 1. row ***************************                Trigger: test_trg1               sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg1 before insert on test for each row begin   set new.str=concat('Value: ',new.id); end   character_set_client: utf8   collation_connection: utf8_general_ci     Database Collation: utf8mb4_general_ci                Created: 2021-01-07 11:45:07.85 1 row in set (0.000 sec)  MariaDB [test]> insert into test set id=1; Query OK, 1 row affected (0.010 sec) 

Is there anything I need to do in order to make inserts perform better?

Cannot connect to MariaDB Columnstore database on Docker

I’m running MariaDB 10.5 Columnstore on Docker as described here. This works fine when I connect to the database starting a command line in the container itself. I can connect and create a database and tables.

But when I try to connect with an SQL Client (such as DBeaver) from localhost on port 3306, I get the following error:

Host '_gateway' is not allowed to connect to this MariaDB server 

I tried to fixed that changing the /etc/my.sql.d/server.cnf file. I tried all the options below but the container always fails after restart, no matter the option.

How to make a MariaDB Columnstore container accept connections from localhost and/or other IP addresses?

These are the options that didn’t work:

[server] bind-address = ::  [mysqld] skip-networking bind-address = 0.0.0.0  [mysqld] bind-address = ::   [mysqld] skip-networking bind-address = ::   [mysqld] skip-networking=0 skip-bind-address 

MariaDB 10.5.8 refusing my connection over SSH tunnel once I add “skip-name-resolve”

I’ve moved from a 10.3 installation to a 10.5 installation, and ported my various my.cnf settings over.

One of those is skip-name-resolve.

If I leave it in I’m suddenly unable to connect to the database with Navicat. Normally it uses an SSH tunnel and then connets to localhost on port 3306:

1045 Access denied for user 'root'@'127.0.0.1' (using password: YES)

My web app is unaffected, and can still connect to the database happily.

If I take it out, I can connect quite happily. What should I change? I tried changing localhost to 127.0.0.1 but that made no difference.

Thanks

MariaDB (MySQL) slow query when primary key range combined with fulltext index

I’ve a table described below, with two columns – integer primary key and title text – currently holding circa 3 million records. As seen in the metadata below, there’s a BTREE index on integer primary key column, and FULLTEXT index on title column.

MariaDB [ttsdata]> describe records; +------------------+---------------------+------+-----+---------------------+-------------------------------+ | Field            | Type                | Null | Key | Default             | Extra                         | +------------------+---------------------+------+-----+---------------------+-------------------------------+ | id               | int(15) unsigned    | NO   | PRI | NULL                | auto_increment                | | title            | varchar(2000)       | YES  | MUL |                     |                               | +------------------+---------------------+------+-----+---------------------+-------------------------------+  MariaDB [ttsada]> show index from records; +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table   | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | records |          0 | PRIMARY                 |            1 | id               | A         |     2798873 |     NULL | NULL   |      | BTREE      |         |               | | records |          1 | title                   |            1 | title            | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

I’d like to run the following query:

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 

This query takes more 5 seconds to execute. When I remove the the range part or the fulltext part, in both cases the query executes in circa 100 ms. Below is analysis of individual queries, the last one being the one I want to use.

I’m new to MySQL and DBA in general. I’ve posted EXPLAIN statements but I have no idea how to draw any conclusions from them. I assume that the query is slow because the range filtering happens on data set obtained from full text query.

So my question is: How can I make the query fast?

The 1928177 magic number is something that just happens to be needed.

Query 1

SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | id   | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                 | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ |    1 | SIMPLE      | records | range | PRIMARY       | PRIMARY | 4       | NULL | 227183 | Using index condition | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; ... 200 rows in set (0.108 sec) 

Time: 0.108 sec

Query 2

SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | title         | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.007 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (0.138 sec) 

Time: 0.138 sec

Query 3

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | PRIMARY,title | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (5.627 sec) 

Time: 5.627 sec

What does MariaDB use for an IV?

I have been using MySQL 5.6 in development environment and MariaDB 5.6 in production until I needed to use encryption. MySQL 5.6 has multiple encryption modes and allows AES-256-CBC. MariaDB 5.6 only uses ECB.

So, I was working towards and upgrade to MariaDB 10, and noticed that the AES_ENCRYPT() and AES_DECRYPT() functions don’t use an IV. This is strange since CBC mode requires an IV. MySQL 5.6 and up those functions use an IV (see chart lower down in MySQL documentation). Why doesn’t MariaDB use an IV? What does MariaDB 10 use as an IV for CBC mode?

I just upgraded to MariaDB 10 and comparing the AES_ENCRYPT() function to MySQL 5.6 I noticed that it does not use an initialization vector in the function. Why? CBC mode requires an IV and so where does the IV come from for MariaDB?

MariaDB subqueries to same table and column resulting in several columns

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB

Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.

`SELECT RD, OT1, OT2, OT3 FROM

(SELECT rdate from OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:

`RD OT1 OT2 OT3

2006-07-01 00:00:00 1.2345 2.1234 1.543

… … … …

2006-07-31 23:59:00 3.456 3.234 2.234`

And, no I dont want to use UNION because then they will still follow one after the other…

Any thoughts?!

MariaDB uses HAProxy IP instead of the WebServer IP

I have a HA Setup that goes like this:enter image description here

I made a user for the web server:

grant all on user_db.* to 'db_user'@'10.10.10.26' identified by 'password'; 

But when I try to access it via this command: Haproxy runs on 3306

mysql -u db_user -p -h 10.10.10.14 --port=3306 

I get:

Access denied for user db_user@10.10.10.15 or 10.10.10.16 

Why does it use my HAProxy IPs? Is there any way to make it use my Web Server IP instead, for security reasons. Or is there any way to prevent my HAProxy servers from receiving data from a different IP? Example: allow only 10.10.10.26 then block the others.

Why is disk IO higher on Debian 10 (MariaDB 10.3) with MySQL replication?

I have a MySQL/MariaDB master-master replication setup that has been working well for several years, the db and tables are not very large (under 200MB for 18 tables). These were on 2 servers running Debian 9 and MariaDB 10.1.44. Now I’ve spun up 2 new servers running Debian 10 and I’m in the process of moving things over to them, but stopped half-way because I’m seeing much higher disk IO usage on the new servers (about 6x more).

So currently, one of the Debian 9 servers and one of the Debian 10 servers are in master-master relationship, with one Debian 9 still being a slave of the master Debian 9 server, and same on the Debian 10 side of things.

I didn’t notice the increased disk IO until after all read/write operations were moved to the Debian 10 master. I was trying to browse tables and saw how slow it was outputting the query results, and it felt like I was on a dial-up connection watching the rows scroll across. It turned out there was some disk contention with the virtual host that was partly responsible, and that problem is now mostly gone.

Now, as you can imagine, none of this is crashing the server with such a "small" set of tables, but as things continue to grow, I’m concerned that there is some underlying mis-configuration which will rear its ugly head at an inopportune time. On the Debian 9 servers, iotop shows steady write IO at around 300-600Kb/s, but on Debian 10 it spikes as high as 6MB/s, and averages around 3MB/s.

Here is the standard config on all 4 servers, everything else is default Debian settings (or MariaDB, as the case may be), full config for Debian 10 at https://pastebin.com/Lk2FR4e3:

max_connections = 1000 query_cache_limit       = 4M query_cache_size        = 0 query_cache_type        = 0 server-id               = 1 # different for each server log_bin                 = /var/log/mysql/mysql-bin.log binlog_do_db            = optimizer replicate-do-db         = optimizer report-host             = xyz.example.com #changed obviously log-slave-updates       = true innodb_log_file_size    = 32M innodb_buffer_pool_size = 256M 

Here are some other settings I’ve tried that don’t seem to make any difference (checked each one by one):

binlog_annotate_row_events = OFF binlog_checksum = NONE binlog_format = STATEMENT innodb_flush_method = O_DIRECT_NO_FSYNC innodb_log_checksums = OFF log_slow_slave_statements = OFF replicate_annotate_row_events = OFF 

I’ve gone through all the settings here that have changed from MariaDB 10.1 to 10.3, and can’t seem to find any that make a difference: https://mariadb.com/kb/en/replication-and-binary-log-system-variables/

I also did a full listing of the server variables and compared the configs on 10.1 to the 10.3 configuration and didn’t find anything obvious. But either I’m missing something, or the problem lies with Debian 10 itself.

Results of SHOW ENGINE INNODB STATUS are here: https://pastebin.com/mJdLQv8k

Now, how about that disk IO, what is it actually doing? I include 3 screenshots here to show what I mean by increased disk IO: Resource graphs on the Debian 10 master

That is from the Debian 10 master, and you can see where I moved operations back to the Debian 9 server (more on that in a second). Notice the disk IO does go down slightly at that point, but not to the levels that we’ll see on the Debian 9 master. Also note that the public bandwidth chart is pretty much only replication traffic, and that the disk IO far outstrips the replication traffic. The private traffic is all the reads/writes from our application servers.

Resource graphs on Debian 9 master

This is the Debian 9 master server, and you can see where I moved all operations back to this server, the private traffic shoots up, but the write IO hovers around 500kB/s. I didn’t have resource graphs being recorded on the old servers, thus the missing bits on the left.

Debian 10 slave server resource graphs

And lastly, for reference, here is the Debian 10 slave server (that will eventually be half of the master<–>master replication). There are no direct reads/writes on this server, all disk IO is from replication.

Just to see what would happen (as I alluded to above), I reverted all direct read/write operations to the Debian 9 master server. While disk IO did fall somewhat on the Debian 10 server, it did not grow on the Debian 9 server to any noticeable extent.

Also, on the Debian 10 slave server, I did STOP SLAVE once to see what happened, and the disk IO went to almost nothing. Doing the same on the Debian 10 master server barely did not have the same drastic effect, though it’s possible there WAS some change that wasn’t obvious; the disk IO numbers on iostat fluctuate much more wildly on the Debian 10 servers than they do on the Debian 9 servers.

So, what is going on here? How can I figure out why MariaDB is writing so much data to disk apparently and/or how can I stop it?

Thanks in advance!