InnoDB : MySQL's ACID-compliant Storage Engine
1
vote
1answer
42 views
Innodb updates records with delay
I have two tables in my database like these:
CREATE TABLE `main` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ...
0
votes
1answer
15 views
Zero Date Values & Converting Entire DB From MyISAM to InnoDB
I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's ...
1
vote
1answer
20 views
MySql 5.6: Ignores innodb_page_size setting in my.ini
So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini:
innodb_page_size=32k
I then stopped the MySql service, and restarted it. The service in Win looks like this:
"C:\...
0
votes
0answers
30 views
Concurrent connections queue up queries too much causing MySQL has gone away error [closed]
I am using innoDB for MySQL 5.7 with 3.75GB of RAM and I have not done any optimization other than adding index to my tables. My problem is that everytime I have a high amount of traffic coming in (e....
2
votes
1answer
59 views
Caching queries that last longer then then X seconds
Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.
...
1
vote
0answers
134 views
InnoDB: Unable to lock ./ibdata1 error: 11
I'm pretty sure this issue has occured before and I've looked around some of the answers. However, I wasn't able to find someone with an exactly similar problem so I thought I'll ask a new question. :(...
-1
votes
1answer
26 views
Trigger to copy deleted rows to another identical table with Cascade Delete On
I have two tables.
Sale and SaleProducts
SaleProducts contains all the products that have a Sale.
1 sale, many products. (1 to many)
SaleProducts has a relation to Sale with On Delete set to ...
2
votes
1answer
50 views
MySQL Replication binary relay logs transfer too slowly
Master server
MySQL 5.6.28
Ubuntu 14.04
8 core CPU
Slave server
Slave: MySQL 5.7.22
Ubuntu 18.04
16 core CPU
Both of them have 64GB of RAM and plenty of disk space.
What I did up to this ...
1
vote
2answers
36 views
How to remove options from mariadb tables?
Mariadb 10.2.8
I am trying to convert an aria table to InnoDB. My problem is
MariaDB> alter table materiales engine=innodb;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support ...
0
votes
0answers
25 views
MySQL8 Restore from Physical Files - Innodb
I recently had one of my VM's go down (MS Server 2016) and the only version I am able to get up and running is two weeks old. However, I do have a backup of my C:/ drive from last night that has my C:/...
1
vote
1answer
48 views
Does MySQL lock full table in case of update query on specific rows?
I just wanted to know if the MySQL 5.5 with InnoDB will lock the whole table instead of some specific rows for SELECT query while UPDATE query is running on that specific table?
I can see that the ...
0
votes
1answer
24 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 ...
1
vote
1answer
24 views
How innodb alter table inplace algorithm inner work?
From the mysql doc , it says :
ALTER TABLE operations are processed using one of the following
algorithms:
COPY: Operations are performed on a copy of the original table, and
table data ...
5
votes
1answer
140 views
Innodb: after 48 hours of optimizing 10mb/sec write speed
I run this on a 8 core server with NVME storage (2TB) and 64 GB ram.
The disks are hell fast, 1.1 GB/sec seq and 70-100k IOPS full duplex.
Because I had so horrible performance with mysql 5.7 I ...
1
vote
1answer
37 views
MySQL tuning: INNODB_BUFFER_POOL_SIZE not found?
My replicator MySQL db, hosted by Amazon-RDS-Aurora is performing poorly. The DB is quite powerful so performance shouldn't be an issue.
When I executed
SHOW VARIABLES;
I could not find ...
2
votes
3answers
55 views
How MySQL use the InnoDB Buffer?
For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB
Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from ...
1
vote
0answers
19 views
How to fix fragmentation issue with INFORMATION SCHEMA tables? [closed]
An instance in Amazon RDS (innodb_file_per_table=1) has a database of size 1G, however, the instance is continuously taking up space and we had to grow its size to 250G. All other tables except the ...
0
votes
0answers
21 views
What's the meaning of trx0sys.c in show engine innodb status?
Hi I have gone through https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/ howto read/interpret "show engine innodb mutex" output? https://www.percona.com/blog/2011/09/...
1
vote
1answer
32 views
How to measure the size of InnoDB's fulltext index
How can I determine how much space MySQLs fulltext index is taking up, using innodb?
Solution would ideally be by querying an information_schema table rather than looking at the filesystem.
0
votes
1answer
49 views
Help with setting up MySQL on a dedicated server - detailed report given
I need help because I do not understand much MySQL settings on a dedicated server.
I have one WordPress website installed with medium traffic with W3 Total Cache.
The storage engine is InnoDB.
...
2
votes
2answers
142 views
In what cases are BLOB and TEXT stored in-line on InnoDB?
I've googled this and can't find it, you can see Rick James mention it here.
... BLOB and TEXT are not always stored separately
The answer on What is the difference between MySQL VARCHAR and TEXT ...
2
votes
1answer
21 views
MySQL InnoDB Reset Existing Auto-Increment Records
Using MySQL Workbench, I have a MySQL InnoDB with a few hundred records.
The records table has the following format (id is auto_increment and unique);
+------+-------+
| id | name |
+------+-----...
0
votes
2answers
37 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
0answers
34 views
Unable to create index on InnoDB table (5.5.37)
I've met with the strange trouble with one InnoDB table.
Index creation on any column fails with error:
Incorrect key file for table 'table', try to repair it
Despite this error, this table is ...
0
votes
0answers
28 views
Impact of adding a MUL index on a table with 35 Million rows. The table has just 3 columns though
I have table
sql
show table status like 'testing_table' \G;
********************* 1. row *********************
Name: testing_table
Engine: InnoDB
Version: 20
Row_format: ...
0
votes
1answer
49 views
different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x)
I run pt-variable-advisor and got a note about different settings for max_heap_table_size and tmp_table_size.
With a web search I found only old articles (around 2007).
pt-variable-advisor h=...
1
vote
1answer
27 views
InnoDB multicolumn fulltext search: SELECT MATCH requires single column index?
I have table with two fields and run a fulltext query with weighted order:
SELECT
MATCH (`my_table`.`text_1`) AGAINST ('+lorem' IN BOOLEAN MODE) AS `reltext_1`,
MATCH (`my_table`.`text_2`) ...
3
votes
1answer
69 views
Increment value for unique key column
I have a table:
CREATE TABLE tbl (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
rank BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY rank (rank)
)
With some values:
(2,...
0
votes
1answer
33 views
Querying MySQL InnoDB to find if the Record is in its Buffer Pool
For us, it is very important to get high response time for our website php script which queries a large database. It is essentially getting user information for the user who visited the page. There ...
2
votes
1answer
98 views
How to change MySQL InnoDB cluster from single primary mode to multi primary mode?
My goal is to setup, in a lab environment, a MySQL InnoDB cluster in multi-primary mode.
I have read much, but I cannot get this setup to work.
Ubuntu 16.04.3 server LTS x 3
SQL0 - SQL2
Ubuntu 16....
0
votes
5answers
79 views
slow queries on indexed columns (large datasets)
I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete.
product_base (1.5GB, 26 million rows)
CREATE TABLE `product_base` (
`EAN13` varchar(13) NOT NULL DEFAULT '',
...
4
votes
1answer
53 views
Does MySQL allows incoming queries while altering a table?
I'm adding a new column to an existing table say ABC with the following query,
ALTER TABLE ABC add column STATUS int(10) NOT NULL
This is done in master, slave and secondary slaves. Meanwhile, ...
3
votes
1answer
85 views
Database slows down at millions of rows, how to optimize schema and queries?
I am building a database to store Ethereum Transactions that are connected to specific addresses. Once the database table eth_addresses_tx starts hitting 3 million + rows, things start slowing down a ...
0
votes
1answer
80 views
How do I stop MYSQL trying to load a missing InnoDB table?
Everytime I start MySQL, I receive the error:
Cannot open datafile for read-only: '.\deleteddatabase\deletedtable.ibd' OS error: 103
I can't DROP the table, because it doesn't exist. There's no ...
0
votes
1answer
52 views
Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse
I have a question about tables and fragmentation in MySQL
My starting point is this question:
How find and fix fragmented MySQL tables
I have a table with 1 million of rows and 128MB of space. This ...
0
votes
1answer
57 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 ...
0
votes
0answers
24 views
mysql innodb cluster : possible to upgrade myisam to innodb on live cluster?
[nevermind the issue is that nodes are like 1 day behind for some reason, is updating but slow] Hello just wondering because all our databases are innodb but mysql db is not.. so when I create a user ...
1
vote
0answers
48 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 ...
1
vote
0answers
267 views
Getting “Cannot open datafile for read-only” for an intentionally DROPped InnoDB table
It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB):
[ERROR] InnoDB: Cannot open datafile for ...
2
votes
2answers
205 views
MySQL - Left Join takes too long, how to optimize query?
A leader may have many followers. A notification_followers table gets a single notification when a leader adds a post with an entry leader_id 1 and notifiable_id 0 (id 1,2 in table). The same table ...
1
vote
1answer
75 views
Error 1215: Can't find how to solve it
I'm currently building a database and adding my foreign keys constraint on phpMyAdmin but I'm struggling with one of them.
Indeed, I've looked on the internet for many possible solutions but any of ...
0
votes
2answers
52 views
question about using tokuDB or InnoDB for financial data
we want to use Percona xtraDBcluster for clustering 4 nodes (Master-Master) and we have a finance project which should have a rather large database. The number of tables is not more than 40, but we ...
1
vote
2answers
65 views
MySQL not using primary key on inner join
I have two tables:
Table 1:
CREATE TABLE `lk_transaction_types` (
`transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`isActive` tinyint(2) unsigned NOT NULL,
`code` varchar(8) NOT ...
1
vote
1answer
49 views
Setting up indexes to support a flexible API search endpoint
I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure ...
1
vote
2answers
207 views
Composite vs auto incrementing integer as Primary key
I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists.
I have 3 tables, one for activities where it has an auto ...
1
vote
1answer
27 views
innodb_file_per_table and ibdata files
I am looking at mysql installation (Version 5.7.19). The mysqld is running and has innodb_file_per_table set to on. Looking at the tables using a mysql client shows up that tables all have engine "...
1
vote
2answers
66 views
Large table with geospatial data - slow queries during updates [closed]
I have a large table with millions of rows (geospatial data) which is getting updated several times per day.
Selects by lat/lon are very fast (0.02-0.04 seconds) most of the time, but during updates ...
6
votes
2answers
604 views
MySql Gap Lock Deadlock on Inserts
I'm getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes.
START TRANSACTION
UPDATE vehicle_image
SET active = 0
...
0
votes
1answer
43 views
How can Mysql server auto re-index tables
I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table.
Ex :
+-----------------------+--------+---------+------------...
0
votes
2answers
46 views
MySQL auto Increment id has taken but seems like not written to disk
MySQL is having a very strange issue and we are unable to figure out. So I need expert advice to figure out the serious problem. I have tried to provide all possible details. But you can ask for more ...
