(Just checked: the latest merge from the MyRocks upstream happened after MariaDB 10.4.6, so it could not be the problem).
Also, the slowdown is reported to be on the read path. There were some changes on the MyRocks write path, but read path hasn't been touched in a while.
The posted explain shows: 1,1M rows to be read (out of 115M). Looks like a reasonable plan.
- philip_38 is it still possible to check - did the EXPLAIN in the previous version look the same?
- Which version did you upgrade from?
The issue happened overnight in two separate boxes, after a common apt upgrade. I have since moved back to INNODB and it works fine. I can recreate the same table in RocksDB and give you access so you may compare the performance on identical selects.
philip_38, yes, that would be useful and I would very appreciate it.
I did reinsert the data and the performance returned to normal. Please close the case.
Ok, closing.
If this ever repeats, it would interesting to study why this was happening
I think I figured out the issue. In Rocks, when you have a very large database, basically it is impossible to do a select to retrieve fields NOT included in an index. That does not happen with Innodb. For example, I have a 1.9 BN records, and two fields are in an index. I can do a select count
from table where field1=A and field2=B, and it takes seconds. But if you do a real select and try to obtain OTHER fields not included in the index, using the same condition above, it can take hours. Clearly, something is not right.
So if you want to use Rocks, make sure every select has an index for all the fields.
Somebody should do something about it.
Should I open a new bug report? This a a RocksDB table
SELECT * INTO OUTFILE '$TMPDIR/download/${i}.csv' FIELDS TERMINATED BY ',' from ldatabase.table where left(did,3)='${i}' order by did asc
caused
190705 19:54:39 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 10.4.6-MariaDB-1:10.4.6+maria~bionic
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=33
max_threads=65546
thread_count=40
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 25331674 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f30424d9548
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f3ab0110d28 thread_stack 0x30000
-
-
- buffer overflow detected ***: /usr/sbin/mysqld terminated
Fatal signal 6 while backtracing
- buffer overflow detected ***: /usr/sbin/mysqld terminated
-
I want to point out the Innodb is holding Mariadb hostage. If a database has 100 tables and only one InnoDB table, and it gets corrupted., the server can spend days recovering from the redo log, rendering the installation useless, and the business unable to operate. Why the whole server does not accept connections if there are other engines working fine? MySQL and MariaDB must decouple from InnoDB or any other engine that cannot fail silently and threatens the whole server.
This just happened to me.