4
Assuming you use a recent enough version* of MariaDB, you can use window functions instead of variables to get a running total:
-- CREATE VIEW
-- running_total AS
SELECT
id, credit, debit,
SUM(credit-debit) OVER (ORDER BY id) AS balance
FROM
tbl_total
ORDER BY
id ;
You can create a view with the above query.
*: Window functions are ...
3
It would be helpful to have SHOW CREATE TABLE, but I will make some guesses...
You have INDEX(QualityLevel), and that index is smaller than the buffer_pool?
You have been doing a bunch of queries; these were not the first? (Hence some things are cached.)
You did not use SQL_NO_CACHE to avoid the Query cache?
How much RAM do you have? ...
2
A partial answer:
$ mysql -u root --column-type-info -v
Welcome to the MySQL monitor. Commands end with ; or \g.
...
Note the extra parameters. Now I will issue just one simple command; see what it comes back with:
mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------
ERROR 1046 (3D000): No database selected
mysql> use ...
1
Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result until the computation of the SUM is complete.
I made a small example which is similar to your data:
CREATE TABLE test_json (
prices JSON NOT NULL,
...
1
I had a similar issue. In my case after importing the users table from an old server and upon restarting mysql, I couldn't start it again.
I fixed my user table by:
1- Editing my.cnf and adding:
[mysqld]
skip-grant-tables
2- #service mysql start
3- #mysql_upgrade
4- #service mysql restart
Don't forget to edit your my.cnf again and remove skip-grant-...
1
The algorythm as I see it.
We don't know how many replaces we need. So we must use recursive CTE or iterative/recursive code.
In it we must:
Find first pattern occurency offset in source data starting from some offset (initially 1)
Split source by head, pattern and tail
Perform pattern replace and encode
Concat head and encoded replace, calculate its ...
1
If the Pis are the source of the data, use "multi-source" replication from them to MariaDB.
Then have 3 nodes for the cluster (2 is mostly useless), and have all the data for both databases on all the nodes.
Or, more simply, the 2 Pis replicate to a single Master (no cluster).
1
SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`,
-- generate masks based on SET values list count
(SELECT 1 `mask` UNION
SELECT 2 UNION
SELECT 3) `mask`
WHERE `set_2` & 1;
fiddle
If server version allows build mask in recursive CTE (dynamic count may be used)
1
table_cache=10G
Is the old name for table_open_cache. They limit the number of entries. 10G is unreasonable. 9000 is high, but probably OK.
tmp_table_size = 8G
max_heap_table_size = 4G
are dangerously high. If just a few threads decide to build a big temp table, you could run out of RAM, possibly out of swap space. Set them to no more than 1% of RAM.
...
1
Two things:
As you posted 600% of CPU usage, I'll guess that you are using Linux, so no worries, actually this means that you are just using 100% of 6 processors, so you still have 10 processors totally free. When you start seeing a CPU usage above 1400% then you can start worrying.
More important than point number 1 is the crazy mess you have in your ...
1
https://mariadb.com/kb/en/library/procedure-analyse/ (Also works in MySQL.)
SELECT * FROM blah PROCEDURE ANALYSE();
For example, using a table of Canadian cities; notice the min and max values:
mysql> SELECT * FROM canada PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
Field_name: world.canada.id
...
Only top voted, non community-wiki answers of a minimum length are eligible
