4,453 questions
3
votes
1
answer
70
views
Is there an option to run a large query without causing it to pollute the InnoDB cache?
I want to run a bunch of large background queries where I don't want these lower priority queries to fill up the InnoDB cache with rows that will flush out cached rows needed by higher priority ...
- 532
0
votes
1
answer
61
views
MySQL Aurora slow COUNT query
I have a COUNT(*) query that is really fast in several DB's, but is really slow on MySQL AURORA:
I can't seem to figure out why. First I thought it was because it went the MEMORY went over the 4MB, ...
- 466
3
votes
1
answer
78
views
Is a clustered primary key in InnoDB considered a covering index when selecting only PK columns?
I have a table like this (InnoDB):
CREATE TABLE group_member (
group_id INT NOT NULL,
member_id INT NOT NULL,
content TEXT,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
...
- 55
0
votes
1
answer
51
views
Is it a good idea to run Optimize table query on a table with billions of records in mysql 8 (innodb engine)?
As part of a maintenance activity we have started deleting unwanted data from our database tables. This is being done to free up space and improve the I/O operation efficiency of our database tables, ...
1
vote
1
answer
63
views
Why does MySQL InnoDB partitioning use modulo-based hashing instead of consistent hashing? [closed]
I’ve been digging into MySQL (both v5.7 and 8.4) InnoDB’s partitioning and noticed that PARTITION BY HASH just uses good ol’ modulo arithmetic to split data (e.g., id % number_of_partitions). That got ...
- 143
1
vote
0
answers
92
views
Cloud SQL mysql database became very slow after disk got full and upgrade to add more space
the web service was working well before the mysql database instance in Cloud SQL went unavaialable because the disk got full. After adding more space, enough space , the db and the web service start ...
- 12.2k
0
votes
3
answers
104
views
Strange behavior of Update Statement
The thing is about a very simple update statement:
UPDATE `user` SET fans = fans +1 where id = 8;
(because it will not cause any concurrency issue when just single[auto commit] use it, so I want to ...
- 81
-1
votes
1
answer
95
views
Mysql Query taking more time than expected
I have a simple MYSQL table containing a varchar column as PRIMARY KEY. Now, I'm querying on that column and the query is taking ~4 secs.
select entity_name from entity where entity_name in ('sunshine-...
- 81
2
votes
1
answer
317
views
Innodb not purging transactions
I got a warning in my AWS console about the InnoDB history list length increasing on one cluster. Taking a look at show engine innodb status, I see that the number in the "Purge done" line ...
- 121
0
votes
1
answer
103
views
Performance Issue with LIKE Query on MariaDB (Synology)
I'm using MariaDB hosted on a Synology NAS. I have a query that's taking around 2.5 seconds, and I want to optimize its execution time. Here's the query:
SELECT * FROM jobs WHERE job_number LIKE '%...
- 1,239
0
votes
1
answer
115
views
How does MySQL handle lock queuing order for SELECT ... FOR UPDATE?
I have a scenario where multiple jobs (job1, job2, and job3) may arrive at the same time and need to execute in a serialized manner. I'm using MySQL with the InnoDB engine and a locking mechanism with ...
1
vote
1
answer
56
views
Convert one table with Primary Key [customer_id] to lots of tables where the name is the customer_id and have one less key
I work on a system where we have a table with millions of rows where the primary key is a customer_id. We have about 8000 customers. I am about to lift the software onto a new major release and was ...
- 80
0
votes
0
answers
63
views
How to identify the cause of InnoDB Runtime Locks in Azure Mysql Flexible Server?
I am using Azure MySQL Flexible Server and encountering InnoDB runtime locks that seem to cause delays or failures in my database operations.
How can I determine which queries or operations are ...
- 1
1
vote
1
answer
387
views
MySQL deadlock when using SELECT FOR UPDATE SKIP LOCKED?
I have a queue system built using PHP and MySQL (I initially built it using redis, but it was kinda complicated and not very flexible), and after doing some research I found out that since MySQL 8.0.1,...
- 46
0
votes
0
answers
93
views
Why does Innodb_buffer_pool_read_requests increase by 400 for a simple indexed query?
I am monitoring the Innodb_buffer_pool_read_requests before and after executing my query on a MySQL, and I noticed that it increases by approximately 400, even though my query is very simple.
Here is ...
- 11
1
vote
1
answer
131
views
MySQL pagination query is very slow [duplicate]
I have this table using InnoDB as engine:
create table if not exists Playlists(
UserId bigint unsigned not null,
Title varchar(50) not null,
IsPublic bool not null,
primary key (UserId,...
- 77
-1
votes
1
answer
87
views
Restoring MySQL from Backed Up IBD Files [duplicate]
Last night, something happened to my MySQL database running on Ubuntu and no matter what I did, I could not not get it to start. I eventually had to backup the /var/lib/mysql folder and do a full ...
- 111
2
votes
2
answers
216
views
InnoDB deadlock caused by two transactions holding the same exclusive lock
I am running MySql v8.0 with InnoDB. I have a simple transaction that has a SELECT (not a SELECT ... FOR UPDATE) followed by an INSERT. I'm getting frequent deadlocks when many DB inserts are ...
- 21
0
votes
1
answer
40
views
MySQL InnoDB v5.7 Deadlock Scenario
We have MySQL 5.7 and frequently end up with errors due to deadlock condition. The most frequent queries that error out involve a Select transaction on different set of rows and an Update operation of ...
0
votes
0
answers
39
views
MySQL fulltext MATCH AGAINST IN BOOLEAN MODE query using a LEFT OUTER JOIN table's column unexpectedly returns no results [duplicate]
In MySQL 5.6.51 using InnoDB, I have two tables:
users
user_aka_names
Table details:
user_aka_names has a foreign key, user_id to users
One user can have many user_aka_names
Not all users have a ...
- 11
-1
votes
1
answer
75
views
Theoretically, in low-conflict scenarios , can MySQL support tens of thousands of concurrent connections without a performance collapse? [closed]
In the process of optimizing MySQL, we discovered a performance collapse at 10,000 concurrent connections during benchmarksql testing. Is there a theoretical limitation, or is it an issue with MySQL's ...
- 59
0
votes
0
answers
71
views
MariaDB is rolling back (removing rows) when running LOAD DATA INFILE on large CSV files
I have large CSV files with stock values and I have successfully added these to my MariaDB database using the "LOAD DATA INFILE" command:
LOAD DATA LOCAL
INFILE '/mnt/HD/HD_b2/Public2/...
- 215
1
vote
0
answers
48
views
Difference between log.write_lsn and log.flushed_to_disk_lsn in mysql documentation
In MySQL dev documentation the definition of log.write_lsn and log.flushed_to_disk is similar
log.write_lsn
Up to this lsn we have written all data to log files. It's the beginning of the unwritten ...
1
vote
0
answers
97
views
PostgreSQL / MariaDB, isolation differences
The following sequences of SQL statements are executed on two different connections to the same database.
1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1: UPDATE Person SET LastLogin=NOW() WHERE ...
- 73
1
vote
2
answers
119
views
How to delete most data from a 2,000GB Mysql InnoDB table
I have a Mysql InnoDB table with storage size(+index) over 2,000GB, There are 14 billion rows in that table..
I need to drop data added over 1 month ago, and figure out a way to always keep recent 1 ...
- 888
0
votes
0
answers
39
views
Handling InnoDB Behavior for Non-repeatable Reads with Auxiliary Indexes
Suppose there is a table t with columns id (primary key) and name, where name has an auxiliary index. The table contains three rows: 1 green, 2 red, and 3 black.
Assume I open transaction A and ...
- 168
0
votes
3
answers
126
views
Weird deadlocks on simple queries
I receive data from a remote server via HTTP POST requests in JSON format which I save for later processing in a database table. Occasionally I encounter deadlock errors doing so. The data is unique ...
- 34
0
votes
0
answers
59
views
Mysql Innodb reads are too high on raspberry
we have got some raspberry devices running small webservers with mysql database. They are literally clones and we are monitoring them through MySQL Workbench. Some of them has this high innodb read ...
- 131
0
votes
0
answers
32
views
Dose it order by primary key when value of secondary index is identical
ref：
https://dev.mysql.com/doc/refman/8.4/en/order-by-optimization.html
if InnoDB table, the table primary key is implicitly part of the index. Dose it order by primary key when value of secondary ...
- 1
0
votes
2
answers
119
views
Read query locking the insertion of a new record on a table with error - Lock wait timeout exceeded; try restarting transaction
I have a stored procedure that runs every one hour to generate a summary of the transactions that happened in the service. The stored procedure reads the data from a table called transaction_log and ...
1
vote
1
answer
59
views
InnoDB How to lock a db- row with script1.php and delete it with script2.php
please tell me, how to solve the following issue.
javascript.js gets data from givemedata.php which selects data from DB and shall lock the selected row to make it invisible for other queries with a ...
- 116
1
vote
0
answers
40
views
On duplicate key check if the other column is the same
I have a table with the following schema:
CREATE TABLE T (
id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
key BIGINT UNSIGNED NOT NULL,
val BIGINT UNSIGNED NOT NULL,
);
I need to ...
- 3,837
0
votes
0
answers
55
views
Laravel MyISAM to InnoDB migration
I have a database that my laravel applications connects to, that has MyISAM tables.
So it doesn't show foreign keys, although they are implemented.
I created this migration to fix the issue and then I ...
- 187
0
votes
1
answer
63
views
Mysql: insert same value on a unique index, why locked?
Table like that:
CREATE TABLE `test_un_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_test_name` (`name`)
) ENGINE=...
- 1
0
votes
0
answers
83
views
Inno DB / MySQL deadlock with two UPDATE statements to the same table but different rows
Executing show engine innodb status shows deadlocks between the following two queries:
update `transparency_codes` set `shipment_id` = 1265, `transparency_codes`.`updated_at` = '2024-05-08 17:45:30' ...
- 391
0
votes
1
answer
74
views
High write times (apparently random) to a MySQL InnoDB table
I have a performance issue in MySQL.
I have a relatively simple table that only uses numeric values.
CREATE TABLE `_user_activity` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`...
- 567
0
votes
1
answer
46
views
How to find rows in a table that have no references left?
I have this user-agents-table in my db:
CREATE TABLE user_agents (
pk bigint NOT NULL AUTO_INCREMENT,
user_agent TEXT NOT NULL,
user_agent_hash BINARY(16) UNIQUE NOT NULL,
PRIMARY KEY (pk),
) ...
- 489
1
vote
0
answers
46
views
mysql is getting long lock while adding foreign key column as nullable and no default value with index on a big table
I am running Laravel Migration and this is how it looks on terminals
adding a column state_id on the click table which contains millions of records. state_id is nullable and has no default value with ...
- 376
0
votes
0
answers
47
views
Converting MySQL MyISAM longtext to Innodb
I am belatedly converting some php applications using MySQL from MyISAM to Innodb. One set of tables were converted on Friday, and now on Monday there are problems. Specifically for one table used ...
- 302
0
votes
0
answers
20
views
MySQL DeadLock on Delete and Insert [duplicate]
in Laravel I have a service which is not wrapped in a transaction and involves both INSERT ... ON DUPLICATE KEY UPDATE and DELETE statements targeting the same table, customers.
INSERT INTO customers (...
- 1
0
votes
1
answer
351
views
Speed up SQL query with huge amount of data
I have this SQL query
SELECT *
FROM `storages_remains`
WHERE `FREE` > 0
AND `CATALOG_ID` IN (...)
CATALOG_ID is not unique, because table may contains multistorages data.
CATALOG_ID + ...
1
vote
1
answer
153
views
Incredibly slow count(*) on MariaDB / MySQL
Running EXPLAIN SELECT COUNT(*) FROM activities shows it plans to use the secondary index with and only one column int and keylen of 5. Action takes anywhere from 2 minutes to 6 minutes, sporadically
...
0
votes
1
answer
86
views
mysql locks relation to indexes
I am trying to understand the relation between locks and indexes.
I have the following table
CREATE TABLE example_table (
id INT AUTO_INCREMENT,
col1 VARCHAR(255),
col2 INT,
PRIMARY ...
- 563
1
vote
1
answer
28
views
MySQL InnoDB: Can one make large inserts from queries run with no rollback penalty
We've ran into some issues where a DBA or a developer will accidentally do something like
insert into `newtable`
select * from `verylargetable`
10 minutes (or maybe an hour!) into running this query, ...
- 2,235
0
votes
0
answers
84
views
Implementing Field Level Encryption for Rating Column in MySQL InnoDB Without Modifying Application Code
I am facing a challenge in implementing field-level encryption for the "rating" column in my MySQL InnoDB database. The codebase is distributed across various repositories, and I am unable ...
- 49
2
votes
0
answers
78
views
How to correctly set the lock in MySQL?
There are two tables: site (id, name, url), page (id, code, content, path, site_id). The site table has a record with id = 1. The page table is empty.
I'm trying to insert a new record into the Page ...
- 21
0
votes
1
answer
832
views
Undo logs are not truncating even the parameter is set to ON
we have configured the below settings in our configuration file.
--innodb_file_per_table=1
--innodb_undo_tablespaces=3
--innodb_undo_directory=/var/lib/mysql/
--innodb_undo_log_truncate=ON
--...
0
votes
0
answers
64
views
InnoDB - errno: 1005, sqlState: 'HY000' sqlMessage: "Can't create table (tableName) (errno: -1)",
I was trying to make club, and students database
I am using sequelize, my engine is innoDB
and I wrote like this
module.exports = (seq, DataTypes) => {
const Club = seq.define("clubs",...
- 13
0
votes
1
answer
38
views
Update a record or insert if it doesn't exists, with locking
I would like to update a record if exists. Thats the easiest part, but that record may not exist, so let's create it first. Pseudocode:
UPDATE x SET y=1 WHERE id=5
if the result of it is 0, then...
a,...
- 6,259
0
votes
1
answer
71
views
Dynamically create tables by foreign key
I am developing an application that records the values of several probes and I am wondering about storing these values in our DBMS.
The traditional way would be to create a table like this:
CREATE ...
- 21