Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
0
votes
1answer
107 views

Simple Query intermittently returning 0 results

I'm having a strange problem and need help troubleshooting. if I run this query on my gallery table: SELECT * FROM `gallery` WHERE awarded = 1 ORDER BY awarded_date DESC LIMIT 29 OFFSET ...
0
votes
1answer
139 views

Backing up a single node Percona XtraDB Cluster

I have a single node percona XtraDB Cluster. Version: Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061 All tables in the DB are using InnoDB ...
0
votes
1answer
248 views

SHOW INNODB STATUS averages calculated

I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed ...
0
votes
1answer
2k views

Innodb: When bulk inserting or updating - how to disable transactions?

When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
0
votes
2answers
283 views

Is a update-only-once-row table worth sharding?

I have a big MySQL 5.6 Inno DB table that contains user's request. A table row updates only once, shortly-after the initial insert! (The initial insert is to get the unique auto-incrementing ...
0
votes
1answer
952 views

InnoDB ibdata1 corrupted, innodb_force_recover not working

I think I have checked every post in the internet. Thing is my MySQL (MariaDB) won't come up and after much testing I have seen it is because of the InnoDB files. I have tried with different ...
0
votes
1answer
98 views

generate unique primary key for all clients from the local database and NOT the server

I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server, I want to ...
0
votes
2answers
126 views

Time taken to drop/add index is lesser than adding a column. Why is that?

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our ...
3
votes
2answers
106 views

Importing a 16 GB MySQL file silently fails

I've looked at a lot answers here MySQL any way to import a huge (32 GB) sql dump faster? and on Server Fault but haven't found a solution to not being able to import a 16 GB MySQL file completely; it ...
1
vote
1answer
381 views

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows: CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY (id) ) ...
0
votes
1answer
2k views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
0
votes
1answer
119 views

In InnoDB, how does fuzzy checkpointing's recovery consistency work?

I've looked the documentation quite thoroughly but still cannot figure out so asking this question. In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets ...
1
vote
1answer
118 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
2
votes
1answer
453 views

MySQL innodb_row_lock_time is more than wall clock time

We recently started graphing the innodb_row_lock_time status variable in our monitoring system. I noticed something I can't understand on a graph for a busy server: Between 04:55PM and 04:57PM, the ...
0
votes
1answer
188 views

Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform ...
1
vote
1answer
3k views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
1
vote
1answer
214 views

Small MySQL table swap/replace taking incredibly long time

We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL: RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging ...
0
votes
3answers
3k views

Unable to fix ROW size too large even with innodb_file_per_table

I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large" Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table` Row size ...
0
votes
2answers
2k views

MySQL crashes and currently the error.log file is huge

I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
0
votes
1answer
99 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...
0
votes
1answer
64 views

Why is InnoDB so painfully slow on full table scans even though entire data is in buffer pool?

Environment AMD Ryzen 9 5950X, 128GB 3200MHz Dual Channel Datacenter NVMe SSDs with 3GB/s+ read and write MariaDB 10.6.3 x64 Windows Server 2019 (same issue on Debian though) dedicated machine, no ...
0
votes
1answer
32 views

Upgrade to Mysql 8 or MariaDB 10.3 for InnoDB Fulltext searches?

I'm upgrading a Mysql 5.7 server and have the choice to move to Mysql 8 or MariaDB 10.3. The by far most important function in this database server is InnoDB fulltext search which I haven't found ...
0
votes
3answers
79 views

How to avoid deadlocks when generating consecutive ticket numbers?

I have implemented a ticketing system where when you join an event and placed an order depending on the quantity, a n of entries will be generated. Example: you place an order with 10 quantity, a 10 ...
1
vote
1answer
1k views

Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE `...
0
votes
1answer
251 views

MySQL/MariaDB data directory backup

I am using a solution of backblaze B2 / duplicity / duply to create backups of my databases. I upload full backups and incremental backups to B2 using duplicity via duply. The incrementals are done ...
0
votes
1answer
656 views

InnoDB Buffer causing a lot of deadlocks and connection/client timeouts

I have a PXC cluster (5.7) with three nodes running on CentOS 7 VMs, each with the following system info: mysql_db# free total used free shared buff/cache ...
2
votes
1answer
4k views

Query about MySQL Semaphores in Engine Status

During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following: ---------- SEMAPHORES ---------- OS WAIT ARRAY ...
0
votes
1answer
140 views

MySQL InnoDB add new instance to existing cluster

Server version: 5.7.23-log MySQL Community Server (GPL) I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks. ...
0
votes
1answer
108 views

MySQL table queries extremely slow

I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like CREATE TABLE `measurement` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` int(11) DEFAULT NULL, `field2` ...
0
votes
2answers
132 views

MySQL - Simple update is very slow and causing high load average with large amount of server

Simple update is very slow and causing high load average with large amount of server : table name : CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` ( `id_list_prom` int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
222 views

Measuring IOPS for innodb_io_capacity

Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max: measure random write throughput of your storage and set ...
0
votes
2answers
359 views

Should I defragment InnoDB table with fixed-length fields?

I am running MariaDB 10.2.13 on Linux (Debian). I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain ...
0
votes
0answers
48 views

MariaDb crashes when “show fields from <table>” is being executed

A mariadb/mysql 10.5.11 database crashed, and I am trying to recover it with the following docker compose file: version: '2.1' services: mariadb: container_name: mariadb image: mariadb:10.5....
2
votes
1answer
3k views

Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start

MySQL process was killed due to memory issues. When start command was issued, server started and is working but a status command shows the following: InnoDB: 128 rollback segment(s) are active. ...
0
votes
2answers
1k views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
0
votes
1answer
41 views

MySQL client goes down after using setPrimaryInstance() in InnoDB clustering, but the cluster remains up with all nodes functioning

So, I have a cluster of 3 nodes, each in a different data center located in the same city. My architecture is a single primary one where one node (let's call it Node 1) is primary and the other two ...
0
votes
0answers
75 views

InnoDB deadlock with concurrent SELECT .. FOR UPDATE queries

I'm currently having deadlocks during high load scenarios. These deadlocks occur even without any update, insert or delete statements Also changing isolation level to read committed does not help ...
2
votes
0answers
23 views

How to estimate table row size in MySQL InnoDB?

My particular problem that I'd like to solve is a table with very wide column (sometimes > 1000 columns) which requires me to split the table. In order to do so smartly, I'd like to split right ...
0
votes
1answer
384 views

MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <...
1
vote
1answer
313 views

Moving to innodb_file_per_table?

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file! As I understand I can reduce its size by moving to ...
1
vote
4answers
260 views

Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem -- -- further update. After doing ...
0
votes
1answer
401 views

MySQL: InnoDB data dictionary has unknown flags

I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs: InnoDB: Warning: table 'sampledb/table1' InnoDB: ...
1
vote
2answers
116 views

MySQLTuner request - high CPU problems

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately. Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day. ...
0
votes
1answer
192 views

MySQL How to Script to create indexes on all date columns in database

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index . So far I ...
1
vote
1answer
305 views

MySQL: Point in Time query using InnoDB log sequence number?

Is it possible to do a point in time query based on a log sequence number? For example, I would like to do something similar to Oracle's "as of" query select foo from bar as of scn 123923455; which ...
0
votes
2answers
310 views

How to speed up full text query on a table with 4 million rows? (MariaDB)

I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site and ...
1
vote
1answer
2k views

MySql InnoDB crash recovery - File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

Mysql 8.0.23 on CentOS 7.9 with WHM I ended up with a corrupted InnoDB table. I gave up on a long query and killed it. Then the query was still blocking so I did a forceful server reboot. I know now ...
0
votes
3answers
973 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
1
vote
2answers
5k views

Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery

We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running. As a ...
0
votes
0answers
40 views

MySQL mass delete from large innodb table very slow

I have a innodb table which contains more than 120million rows including duplicates. Let's name it as big_table. I fetch all the duplicates into another table(temp_big_table) which contains nearly ...

1
2 3 4 5
42