New answers tagged mariadb
0
Two node galera is a fundamentally broken design as it cannot maintain uptime without a quorum and the ability of a node to go down to aid recovery.
garb is a toy test thing and not a production tool.
Use 3 nodes all as full galera member nodes.
Use haproxy in a redundant configuration on all nodes. Use a different IP addresses for haproxy and galera without ...
1
select count(*)
from symbol_details
where tsetmc_id = 41974758296041288
and created_at > '2020-06-20'
and created_at < '2020-06-31';
Optimal index, in this order:
INDEX(tsetmc_id, created_at)
When you started with the range value, it could not go past that to the 'id'. The simple rule is: Put the = columns first in the ...
1
Sleeping connections are not running queries, sleeping connections represent connections that the client/app failed to close successfully, most of the time due to bad programming practices (developers nos closing connections after querying the DB) or bad connectors configuration (not sending a close connection signal or even using an old/outdated connector).
...
0
Make a simple Update
CREATE TABLE Table1
(`test_date` datetime)
;
INSERT INTO Table1
(`test_date`)
VALUES
('2020-06-10 00:06:17.185449'),
('2020-06-10 00:11:05.943217'),
('2020-06-10 00:13:45.375109'),
('2020-06-10 00:13:45.461321'),
('2020-06-10 00:15:32.497548'),
('2020-06-10 00:15:32.544873')
;
UPDATE Table1 SET ...
2
It helps to pay attention to what error messages say (emphasis mine):
check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE"
meaning this particular version of the server has trouble understanding what "RECURSIVE" means. When we do check what the manual says about "recursive", ...
0
The beginning of SHOW VARIABLES was chopped off; try again. (I need some of that info to finish my critique.)
SHOW GLOBAL STATUS shows about 250 queries per second, which is reasonable. That may be coming from 10K users; I can't tell, and it does not matter. Or maybe 302 users?
10K queries per second may be practical; it depends.
143.5G (232.47% of ...
1
INDEX(library_id, firstdateofinterval) will help the query run faster. Note that the column tested by = should be first and the range should be last.
Why not use the desired index? Because it won't get past the range. That is, all 4 of your indexes are equivalent to simply INDEX(firstdateofinterval) when you test for a range of dates.
When you used the &...
0
"Don't queue it, just do it." That is, don't bother to put the request(s) in a queue(s), spin off a process to work through the task(s). That leaves it up to the OS to share resources. And avoids several nasties that may happen when you try to use MySQL as a Queue.
1
The alter is failing because the row has a value of null for the TS column. If you remove the 'NOT NULL` from your alter statement it will work correctly though you will still end up with the row you first inserted with a value of null for TS.
You will get the same error if you do the alter without trying to set a default. The alter you are trying to use ...
1
Your own solution might be suboptimal but I am not sure there is a way to solve the problem elegantly and/or efficiently without at least some redundancy. I managed to avoid hitting the customers table more than once, but I still had to reference multiqueue twice. This is the query I ended up with:
SELECT
q.ID,
q.Content,
q.Volume,
q.CustomerID,
...
0
If I can remember correctly, I fixed it by setting the innodb_force_recovorey level or I just purged the mariadb package and restored from .sql files
0
The performance is going to be atrocious with your setup, especially in writes because every write has to be acknowledged by at least one remote node. You will also get an astronomical number of deadlocks.
If you need geo-replicated synchronous redundancy, you would be batter off with with DRBD for block level replication. Performance will still be terrible ...
0
Below is a query that achieves the goal, though it might be far suboptimal:
SELECT p1.ID, Content, Volume, CustomerID, runtot
FROM (
SELECT ID, Content, Volume, CustomerID,
SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
FROM multiqueue
WHERE PublishedTS IS NULL
) ...
0
SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
SELECT ID, Content, Volume, CustomerID,
SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
FROM multiqueue
WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=c1.ID
WHERE
runtot < 2000 *...
1
You have to FORCE the index, the optimizer will only then use your wanted index, else you give it only a hint
SELECT
BIN_TO_UUID(album_id) AS e,
firstdateofinterval AS p,
BIN_TO_UUID(territory_id) AS t,
SUM(playbacks) AS c1,
SUM(userplaybacks) AS c2,
SUM(userdownloads) AS c3,
SUM(usersingledownloads) AS c4
FROM
...
1
For recent versions, you can use window functions:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
SELECT ID
, CustomerID
, Content
, sum(Volume) over (
partition by CustomerID
order by ID
) as runtot
FROM multiqueue
) AS q1
WHERE runtot < 2000;
EDIT:
If you want at ...
2
Get enough disk to handle twice your data.
If you can afford enough RAM for the data, I/O is unlikely to be a problem.
If the data is much bigger than RAM, then the performance of the disk may be a critical factor. (Today's SSDs make this rarely an issue.)
If the queries are CPU-bound, then focus on indexing (especially "composite" indexes) and ...
0
check your services and be sure mysql80 is in running.
1
A simple standard computer Core i5 / Ryzen 5, so that you can administer it in comfort. Standard PC components guarantee that replacement and repair is quite easy manageable, by almost everyone. More important is a good strategy for backup and restore/ disaster recovery.
A VM on a fast computer makes only fun to work with, when there is enough power for the ...
2
300 orders per day for 10 years is about a million rows. Assuming 10KB of data per sale (probably will be a lot less), that's 10GB of disk space. As for how to check your assumptions about disk usage and throughout, you will need to write some test scripts to create those million sales and measure latency for each transaction with an increasing number of ...
0
Firstly RAID5 is very poor for writes, and delete does a lot of them.
When you do a big delete it all gets written to log first so recovery can happen if there's a crash. If your logs are on your RAID 5 array, move them somewhere else - preferably a Raid 1 array (mirrored), otherwise you have massive contention.
It may be worth dropping some indexes and ...
0
A basic database principle is to avoid duplication of data. Option 2 has 3 rows with essentially the same basic data. So, a vote for Option 1.
Option 1 is smaller overall, so another vote for it.
If you get much past 3 sorts, the problem gets messier.
You are doing SELECT id. Was that a simplification for this discussion? If you really are selecting only ...
0
I've tried my approach and partially worked. After manually changing ( with mariadb stopped ) master-bin.index the slave started working again but only Master_Log_File was increasing
Master_Log_File: master-bin.000319
Relay_Master_Log_File: master-bin.000312
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
As if slave was only reading but not writing. So I gave ...
0
Only users with PROCESS privilege can see processes other than their own.
From the SHOW PROCESSLIST Statement (MySQL Documentation):
SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using)....
0
Anti-OPTIMIZE
If you are using InnoDB (which you should be using), then OPTIMIZE TABLE is almost totally useless. Stop running that.
If you are DELETEing huge chunks of a table, and that led you to want to OPTIMIZE, then let's discuss alternatives.
Replication issues
It is possible to avoid replicating any command(s):
SET SESSION binlog = OFF; -- I ...
0
Analysis of STATUS and VARIABLES
Observations:
Version: 10.4.12-MariaDB
256 GB of RAM -- Is this correct??
Uptime = 52d 15:16:00
Are you sure this was a SHOW GLOBAL STATUS ?
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
Increase innodb_io_capacity to 500.
Lower ...
4
PROPOSED SOLUTION
DUMPFILE=mydump.sql
grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
EXAMPLE
# grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
...
1
You could do this (see test.sql sample file below):
grep 'CREATE TABLE' my_dump_file_name.sql | wc
Result:
2 8 54
The | (pipe) character feeds (pipes) the output of the grep into wc (not the wc!)!
From man grep
grep, egrep, fgrep - print lines that match patterns
will give you lines of the the form
CREATE TABLE `film_text` (
From man wc
wc - ...
0
It's been 2 years now, multiple SQL version upgrades have passed and 5 different high performance servers tested (basically the fastest in storage and cpu AWS has to offer).
The jump from mysql 5.7. to mysql 8 decreased performance, the only benefit from that change is more complex SQL support but in terms of performance nothing got better.
Well the count(*) ...
1
You have also to define two columns for a double Primary KEY
CREATE TABLE `category_product` (
`category_title` varchar(20),
`product_title` varchar(50),
`product_vendor_name` varchar(50),
CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ...
0
[very good] Do BULK insertions instead of many single insertions -- Yes. Inserting 100 rows in a single INSERT can run 10 times as fast.
[good] Use transactions -- Multiple single-row INSERTs save some by doing this.
[it depends] Increase innodb_log_file_size -- There are metrics in GLOBAL STATUS that would help determine the utility of this. (Your 768M ...
0
You code looks somewhat unfamiliar to me
CREATE DEFINER=`root`@`localhost` PROCEDURE `Discussant_Update`(_Work_at_Event_id INT,_Discussant_1 INT,_Discussant_2 INT,_Discussant_3 INT,_Discussant_4 INT,_Discussant_5 INT)
BEGIN
DECLARE n INT;
SET n = 0;
DELETE FROM Discussant WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE ...
0
OR is terrible on performance, and probably equally bad on transaction locks.
pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= ...
Instead of depending on NULL in that, consider storing a date in the future. That way this would suffice:
pxpl.DeactivationTS >= ...
EXISTS -- Since EXISTS stops at the first occurrence, LIMIT 1 is redundant. ...
0
One problem I see is that your DELETE will try to delete from both tables when a row exists in pxpl with WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR). But the related row (through the FK) in proxies maybe related to more rows in pxpl that do not match the criteria. And because the FK has ON DELETE CASCADE, it will try to delete them as well. ...
1
Apparently HeidiSQL corrupts the table creation code. The proper table creation code is (without bounds after double):
CREATE TEMPORARY TABLE `new_aps` (
`MinuteStart` datetime NOT NULL,
`ProxyListID` bigint(20) NOT NULL,
`SuccessCount` double NOT NULL,
`UnknownCount` double NOT NULL,
`FailureCount` double NOT NULL,
PRIMARY KEY (`ProxyListID`,`...
1
(Serge self-Answered the title question; this Answer tries to look beyond that.)
If the deadlock is caused by the same DELETE being run from multiple threads, then simply ignore the deadlock; the other thread will do the work.
If there are other concerns, there are ways to use subqueries instead of a JOIN. There is even a kludge where an extra level of ...
0
Apparently, ORDER BY is not supported in a multi-delete query, according to the doc:
For the multiple-table syntax, DELETE deletes from each tbl_name the
rows that satisfy the conditions. In this case, ORDER BY and LIMIT>
cannot be used. A DELETE can also reference tables which are located
in different databases; see Identifier Qualifiers for the ...
1
2 different techniques are needed:
SET default_storage_engine = InnoDB;
CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4;
USE foo;
CREATE TABLE ....
0
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
Your problem involves what STRAIGHT JOIN does.
Doing a STRAIGHT JOIN may take the Query Optimizer out of the way at some steps.
For example, note what the MySQL Internals Documentation says:
The straightforward use of find_best() and greedy_search() will not apply for LEFT JOIN or RIGHT JOIN. For example, starting with MySQL 4.0.14, the optimizer may ...
1
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
According to POINT - 3 :
In case when I query by news source ID (the commented out WHERE s.Id = 52), the result comes immediately, regardless of whether there are lots of items for that source or 0 items for that source.
This is possible because on using WHERE s.Id = 52 it using index from NewSources & NewITems table do check explain plan might be ...
0
I don't understand your two choices, but I feel sure that a LEFT JOIN is involved.
Ponder something like this: Step 1:
SELECT uv.video_id
FROM (
SELECT uf.friend_id -- all your friends
FROM user_friend AS uf
WHERE uf.user_id = u.id
AND u.id = 123 -- you
) AS f
LEFT JOIN user_view AS ...
0
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
A classic performance problem. But it takes some out-of-the-box thinking to make it perform.
The trick is to build _and maintains an extra table for ordering news articles by category (or byline or topic or ...). Here is a writeup on the details: http://mysql.rjweb.org/doc.php/lists
1
You can do it relatively easily by following these steps:
1) Take a dump of your database - for the sake of this example, I've used the MySQL sakila sample database "designed to represent a DVD rental store". The command I used was:
mysqldump -u root -p sakila > sakila.sql
You will then be asked for the password for the user that you are using - in ...
Top 50 recent answers are included
