Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

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

How to count the reads on index in MySQL

I have a table with index. CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_MFGR CHAR(25) NOT NULL, ...
22
votes
1answer
14k views

Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
0
votes
1answer
131 views

Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction. As per the calculations, ...
2
votes
1answer
3k views

Queries getting stuck on very simple COUNT queries

process list: | 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | ...
0
votes
0answers
5 views

Innodb cluster instance is changed during creation. The instance reports it to another one which I did not provided. What is the problem here?

MySQL db1:3306 ssl JS > cluster = dba.createCluster('prod_cluster'); A new InnoDB cluster will be created on instance 'db1:3306'. Validating instance configuration at db1:3306... This instance ...
0
votes
1answer
115 views

MySQL Dead Lock on Delete Issue

I have recently updated my production code with the new query that is supposed to clean up orphaned records. It runs within the transaction as the last step. And, as a result, I am seeing occasional ...
1
vote
1answer
54 views

What schema does this mean 'mysql - schema's default character set: utf8'?

While upgrading mysql from 5.6 -> 5.7 -> 8.0.23 in step 5.7 -> 8.0.23 I got a warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 ...
3
votes
2answers
274 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
0
votes
1answer
69 views

MariaDB innodb bufferpool load taking long time

When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster? +----------------------------...
0
votes
1answer
53 views

Mysqld draining memory, using way more then assigned and not giving any back

This is a production server. Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld). Sometimes ...
3
votes
1answer
9k views

Very poor insert performance MySQL / Percona Server

I'm running a Percona-Server instance off an Ubuntu server install. I'm using an application that needs to access this database, and its having VERY poor performance. Once the database has been ...
0
votes
0answers
14 views

How to resolve the corrupted innodb table

I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not ...
2
votes
0answers
213 views

Got fatal error 1236 from master when reading data from binary log

I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ? Background Needed to perform a host updates on master host. Hence ...
4
votes
2answers
25k views

The best MySQL Settings for 32GB RAM on a Dedicated Server

I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm. I have the following configuration on my MySQL: [client] port = 3306 socket = /...
1
vote
0answers
50 views

MariaDB service fails to start with InnoDB Space id error

I've been running MariaDB on a Raspberry Pi 4 (Raspbian 10 / buster) as the database server for my Webtrees and Owncloud sites. Recently, I noticed that the server wasn't looking right, and after a ...
1
vote
0answers
33 views

How can I tune my MySQL server to bulk import XML file (42 GB)?

I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time ...
1
vote
0answers
2k views

mysql - importing large Tablespace: Lost connection during query

iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB ) this is the exact steps i do: getting create table query using mysqlfrm command create the table Alter Table discard ...
0
votes
0answers
37 views

MySQL 5.7 32-bit table is full on unix

As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that ...
0
votes
0answers
20 views

Truncating partition locking whole table in mysql

Novice here. I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is ...
0
votes
1answer
24 views

Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

In the official documentation of Boolean Full-Text Searches: Boolean full-text searches have these characteristics: They do not automatically sort rows in order of decreasing relevance. However, ...
0
votes
1answer
48 views

Can large data deletion affect Innodb buffer pool performance? [duplicate]

At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
3
votes
2answers
6k views

Ample disk space with error: "1034 Incorrect key file for table 'table_name'; try to repair it"

I'm attempting to alter a 1.6 GB InnoDB table with the following query: ALTER TABLE `table_name` ADD `field_id` int(11) unsigned DEFAULT NULL The query fails with the following error: 1034 ...
0
votes
0answers
230 views

How to restore a MySQL InnoDB with ONLY .ibd files?

I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able ...
0
votes
1answer
43 views

Help on improving the query performance of MySQL table

My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed. The ...
0
votes
0answers
27 views

MySQL count limit on COUNT + GROUP BY results for performance benefit

I have a very large MySQL8 database, currently using the InnoDB engine, that I have to perform some COUNT() queries on. Specifically, something like this: SELECT parameter, COUNT(*) FROM table WHERE .....
0
votes
0answers
30 views

MySQL - Counting multiple parameters from the same subquery

I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
3
votes
3answers
1k views

MySQL - Master/Slave Replication with different server specs

I have a Master/Slave replication (using MySql 5.1 / InnoDB). Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) ...
9
votes
2answers
22k views

How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session. How do I make this setting stick after stopping and restarting MySQL? NOTE: I am on version 5.5.19 of MySQL (...
0
votes
1answer
482 views

mysql innodb cluster 3 nodes, 2 lagging almost 24 hours

I have a 3 node setup and the 2 read only nodes are way behind (like 24 hours) from the rw node.. I saw here: https://bugs.mysql.com/bug.php?id=86797 to restart the lagging node and will pick up by ...
1
vote
1answer
413 views

SQL: How to get all tables' columns & keys + the primary key?

Database Info MySQL 8.0.22 InnoDB The Goal I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group. Given the ...
0
votes
0answers
27 views

Why does insert on duplicate-key error create a share lock?

In the mysql docs is the following example for locking on insert: INSERT sets an exclusive lock on the inserted row. [...] If a duplicate-key error occurs, a shared lock on the duplicate index ...
0
votes
0answers
12 views

How to avoid duplicate in mysql INNODB transaction for non index key?

I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
4
votes
2answers
704 views

Is it suitable innodb_buffer_pool_size for MySQL Master-Slave

I am wondering how to decide the value of innodb_buffer_pool_size for my Master-Slave style database. I have a set of MySQL, 1 x master + 1 x slave. As I know that the innodb_buffer_pool_size is ...
27
votes
7answers
60k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
0
votes
1answer
27 views

Should I use MyISAM over InnoDB to save disk space?

I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an ...
0
votes
1answer
110 views

Innodb primary key advice

We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly. The existing schema has a uuid4 as a primary key, and then two additional indexes ...
0
votes
1answer
82 views

How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database? [closed]

I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control. Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical ...
1
vote
1answer
32 views

Latest value of each GROUP with INNER JOIN

I'm using sensors to get temperature and humidity every X minutes. I've one table for sensors and one table for their results. tb_sensors: tb_results: I want to get the latest measurement for every ...
2
votes
1answer
44 views

what happens If I remove mysql-bin.index file?

Because of the file storage full, we are removing mysql bin log files. Accidentally, we removed mysql-bin.index file. What happens if I remove mysql-bin.index file? These below are my DBMS info. Thank ...
1
vote
2answers
332 views

MariaDB: how to reduce ibdata file size

I found that My MariaDB's ibdata file keep increasing. So, I've searched for this, and found that innodb_file_per_table should be set as 1. but, my DBMS's configuration has already set as 1; Why ...
9
votes
2answers
6k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
218
votes
4answers
372k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
0
votes
1answer
56 views

MySQL customized service script for zip installation

We are currently using MySQL 5.7.20 on CentOS and are using zip installation. Since it is a zip installation we do not have service created in /etc/init.d/. Is there any other method for auto-...
4
votes
2answers
12k views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
1
vote
2answers
845 views

MySQL: DELETE didn't shrink DB size, nor table size

In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same. Table size: +-------------------------...
0
votes
1answer
52 views

Assign different RAM usage (buffer pool size) per db in mysql

I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb. However, the InnoDB Buffer Pool is only 16Go, ...
1
vote
0answers
150 views

MySQL 8.0.x: Discard temporary table left behind during a pre-migration (5.7.x) crash when the parent schema is already dropped

We had a MySQL 5.7.x instance serving a production schema foo, and the server itself was migrated to 8.0.21 on 13 Sep 2020. Recently we migrated data from foo to a new schema bar. After migration we ...
1
vote
1answer
1k views

Mariadb error 1071: Specified key was too long; max key length is 1000 bytes

A bit of context: I am trying out flowable, however tomcat chokes at a certain point while populating the database, the usual exception wall of text boils down to the title of this post. First time I ...
0
votes
0answers
271 views

MySQL: message "Incorrect key file for table" "try to repair it"

I'm using MySQL 5.7.10 I have a table like this, with 100M rows and a size of 16GB . CREATE TABLE `my_table` ( `id` DOUBLE NOT NULL AUTO_INCREMENT, `entity_id` DOUBLE NOT NULL, `concept_id`...
1
vote
0answers
22 views

MySQL pagination

MySQL - I am trying to fix this pagination issue on my servers . The RAM is 40 GB and the innodb_buffer_pool_size is set to 36 GB which is a little more than 80%. Still getting paging alerts. Any ...

1
3 4
5
6 7
43