Questions tagged [innodb]

InnoDB is the main ACID-compliant Storage Engine used in MySQL.

Filter by
Sorted by
Tagged with
-1
votes
1answer
42 views

What is the best mysql configuration for mysql instance with a lot of databases and lot of tables inside?

I have a mysql database instance with more than 3000 database inside. Each database contains more than 200 tables. I have more than 100 gb of data in all these database at present. I am using windows ...
2
votes
0answers
28 views

MariaDB table has a metadata lock, but no blocking thread

I have a MariaDB 15.1 installed on a Debian system: $ mariadb --version mariadb Ver 15.1 Distrib 10.4.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 A script regularly loads data into ...
1
vote
1answer
86 views

Unclear why crash consistent backups are unsafe for MySQL

My understanding of the InnoDB engine is that the database is always recoverable after a crash, due to the internal architecture of the engine itself. That is, InnoDB can correctly recover from a ...
0
votes
0answers
104 views

Trying to restore InnoDB tables

I've reinstalled MySQL but when I copy the data dir from the old instance: maicol07@maicol07$ sudo mysqld --user=root 2020-07-14T13:07:34.031447Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (...
1
vote
0answers
361 views

mysql 8 only creating single log file instead of 2 (ib_logfile0)

I am having an issue with one of my mysql servers that has to do with the innodb logfiles. Even though it is configured to have 2 logfiles as normal, when I start up the server it only creates one of ...
1
vote
2answers
329 views

Will InnoDB or NDB cluster boost performance

We have fairly large MySQL database (about 35GB total) and topping about 900qps. Performance is not a big issue for now, but the project is growing constantly and I'd rather start thinking about ...
0
votes
2answers
193 views

need help in understanding the deadlock

I got the deadlock below when running insert into statements. What's the possible cause of this and how you suggest to solve it? LATEST DETECTED DEADLOCK ------------------------ 2020-01-01 06:56:20 ...
0
votes
0answers
46 views

why innodb use gen_clust_index instead of table lock when table have no any index?

My mysql version is 5.7. I try to test some case about innodb lock.I know: a. query using table lock when table have no any index. b. table have no any index, innodb will auto create gen_clust_index. ...
0
votes
1answer
304 views

InnoDB recovery from datafiles

First of all, my MySQL DB and Linux skills are not so good. I spent several days searching for a solution and tried several approaches. The following approach is the one I came furthest with. My SD ...
1
vote
0answers
52 views

What is the alternative for innodb_track_changed_pages in Debian 10/MariaDB 10.3

So we have been using Debian 9 with MariaDB 10.1. For faster backups (total DB size across three DBs is > 1TB) we have enabled innodb_track_changed_pages. However, that feature has gone away on ...
1
vote
0answers
57 views

Εxponentially growing Innodb_row_lock_time on “master” node of MariaDB 10.3 Galera cluster

I've been trying to investigate issues with an application failing, and I have reason to believe that the culprit lies somewhere in the database backend. To this end, I started collecting metrics from ...
0
votes
0answers
301 views

ERROR: InnoDB: Attempted to open a previously opened tablespace - how to avoid that?

I had no problems before, it was all fine, even when watchdog worked and restarted the device, everything worked fine. Until after one of such reboots something happened with partitioning, fixed that ...
0
votes
1answer
724 views

What could be causing the Google Cloud SQL MySQL replica instances to be stuck on a “System lock” state, causing huge replication delays?

Let me explain the problem in detail, We have a Google cloud SQL setup(1 master, 1 failover replica and a read replica). From the last two days, we are facing replication delays on both the replica ...
0
votes
1answer
399 views

MariaDB 10.3 InnoDB random crash due to memory exhaust on VPS

after migrating to new VPS my server occassionally experiences database crashes. This may happen 1-2x in 2-3 days. Server runs MariaDB 10.3 on CentOS 7 with nginx 1.16. Virtual server has 1 GB memory,...
0
votes
1answer
100 views

Fastest way to move Innodb database from one server to another

I understand I can use mysqldump or xtrabackup to move InnoDB from one db to another. Considering I am okay with server down time, are there better way to move InnoDB database? I have tried copying ...
3
votes
4answers
517 views

MySQL on VM, High IO Wait but low TPS

We are trying to figure out a strange situation on our MySQL installation on VM. We are on Mysql 5.7 and RHEL 7 with XFS. We are observing when we run a few select queries the IOWait races up to 40-...
0
votes
1answer
713 views

AWS Freeable memory decreases from 16GB to 1GB

I have a MYSQL database hosted on RDS with 16GB RAM. I'm using an r4.large with 20GB General purpose SSD storage. We are using a microservice architecture and has 6 backend containers. So the request ...
2
votes
1answer
3k views

Multiple files found for the same tablespace ID

After a management over-sight, the hard disk utilization of the mysql server went to 100%. In panic, I shut down most of the services - including mysqld, which I guess caused this problem. ...
1
vote
0answers
26 views

mariaDB uses the wrong disk

I am using mariaDB version 10.2 - innodb, with a boot and ssd drive. the boot drive is only for root, all the data is configured to be on the ssd and mysql dir is mounted on to is. when writing I see ...
1
vote
0answers
113 views

MariaDB 10.2 crash on innodb table corruption

I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
2
votes
1answer
273 views

What is the maximum number of words allowed in a MariaDB InnoDB Full Text Search query

I recieved this error when performing a search with 746 words (it wasn't intentional someone pasted text into the wrong box). HA_ERR_FTS_TOO_MANY_WORDS_IN_PHRASE: Too many words in a FTS phrase or ...
2
votes
1answer
4k views

Mysql high cpu usage (over 600%)

The server has 8core CPU and 22 GB of ram. Every day receives many requests from users, and recently a replication mechanism has been activated, with an implementation of a db master and a slave. Now ...
1
vote
1answer
2k views

MySQL server (mariadb) auto shutdown without overload

With old configs, my VPS (1 CPU, 1 GB RAM, 1 GB swap) works normally for almost 2 years. I didn't change anything from beginning. But I have this issue several days ago, MySQL server automatically ...
0
votes
1answer
885 views

MariaDB - import raw InnoDB frm/ibd files?

Good afternoon. My Debian 9 server ran into an issue that rendered the OS inaccessible , I managed to extract all the raw database files (/var/lib/mysql) for my project but unfortunately the engine ...
0
votes
0answers
104 views

InnoDB recovery from ibdata1 from disk failure

I started recovering data for a non-profit organization from a server that had a hard drive failure. The mysqldump was not working, but I managed to tar the /var/lib/mysql on time and download via ...
1
vote
0answers
680 views

MySQL's innodb_buffer_pool_size doesn't seem to have any effect on RAM usage

(I have checked similar questions, none of them cover my situation) I'm trying to increase MySQL 5.7 memory usage on my Ubuntu 16.04. Before my changes mysqld was using about 8% of RAM out of 8GB ...
0
votes
1answer
291 views

MySQL perf bad on SSD-based server. Small tables. MySQL config? RAID?

I have a couple of servers in an industrial setting (air-gapped network) that do some fairly light telemetry gathering. We generate about 10GB of telemetry history in ~30 days. All telemetry goes ...
0
votes
0answers
329 views

mySQL shutdown “ERROR: 1050 Table 'plugin' already exists”

Have a mySQL DB (running on a Debian LAMP) that seems to do a maintenance shutdown or check each night, and fails with the following. The only "plugin" table is the empty one in the mysql db, and I'm ...
0
votes
0answers
113 views

MySQL causing major I/O Lag

I have a mysql server (version 5.1) running Roundcube Webmail client on a plesk box (onyx). We have a few hundred users on it during the day. My hypervisor, Virtuozzo, they have been working with us ...
1
vote
1answer
260 views

What would cause mysqlcheck to incorrectly report a table as undamaged?

We are administering a MySQL server for one of our customers that has >100 databases with about 50 tables each, many of them InnoDB tables. The server crashed and I'm trying to find the culprit. When ...
0
votes
1answer
307 views

Why are partitioned MySQL InnoDB tables twice as big as unpartitioned ones?

I have two InnoDB tables identical in every respect except for one being partitioned, and the other - not: DROP TABLE IF EXISTS `simple_table`; CREATE TABLE `simple_table` ( `date` date NOT NULL, ...
0
votes
1answer
739 views

Mariadb tuning - slow writes

Background: I have a a website I am migrating from a stand alone MariaDB server to a MariaDB galera cluster. Part of this had me convert the tables from a large number of MyISAM to InnoDB. For what it ...
-1
votes
1answer
249 views

Cannot log in to the MySQL server after hosting company restarted my server

Today my hosting company decided to patch their systems due to Meltdown/Spectre and restart all servers. I had my CentOS 7.2 server running for 280 days with no problems of any kind at all. But now ...
3
votes
0answers
375 views

MariaDB on Windows - InnoDB hang on table conversion to MyISAM

This is a bit of a strange one (and a big post, sorry), i am working with a customer who runs MariaDB on Windows (Server 2008 R2). They have several large MyISAM tables (up to 30 GB each, 200+ ...
0
votes
1answer
224 views

How to decrese MySQL Innodb crash recovery time (force MySQL to do it faster)

I'm running MySQL 5.7 with 1GB of innodb_log_file_size on CentOS 7 x64 and I can't change the value of that parameter right now. In the case of crashes, it takes about 20 minutes to recover, but ...
0
votes
1answer
276 views

Different InnoDB data file sizes on Galera

We have a cluster of 3 Galera-MariaDB boxes, using InnoDB as an engine. Cluster's in sync, and reporting all good as far as I can see. Much to my surprise, the size of the InnoDB data files differ ...
0
votes
0answers
654 views

Occasional slow database queries caused by InnoDB

Update 2017-11-26 The problem seems to be related to InnoDB. The CI as well as the Wordpress installations are using InnoDB Engines while the rest is working with MyISAM. After I converted all MODx ...
1
vote
3answers
1k views

innodb-read-only prevent mysql replication? Table 'gtid_slave_pos' is read only

I set up a couple of maraidb v10.2.10 containers with the official docker image. Master can read/write and slave is read-only with this my.cnf. [mysqld] server_id=2 read_only=1 innodb_read_only=1 ...
0
votes
2answers
379 views

MySQL server doesn't start because of missing InnoDB plugin

I cannot start the MySQL server. My machine is an Ubuntu 14.04 with mysql-5.6: $> dpkg --get-selections | grep mysql libdbd-mysql-perl install libmysqlclient18:amd64 install ...
2
votes
1answer
7k views

Mysql cannot start - Could not find valid tablespace file for

My mysql service is currently unable to start with the error InnoDB: Could not find a valid tablespace file for 'wowcher/temp_import'. I think I know what the issue is. I have a PHP script which ...
0
votes
2answers
3k views

MySQL maximum possible memory usage above installed RAM

I'm using debian 8 Jessie and MariaDB. My mysqltuner seems to indicate as MariaDB can use 142% of RAM : -------- Storage Engine Statistics ------------------------------------------- [--] Status: +...
-1
votes
1answer
170 views

MariaDB generating INDEX_*.ibd files, racking up server space

Little bit of context before diving into the issue, I have a Wordpress based blog running on 1 GB (RAM), 20 GB (disk space) linode instance. The database itself is around ~105 MB. Recently, I noticed ...
0
votes
1answer
566 views

How to deal with mysqldump and innodb_buffer_pool_size?

We have a database of about 100GB. However, most of this 100GB is "archived" data that we rarely access -- about 25GB is active data. We have innodb_buffer_pool_size set to 48G, which is typically ...
0
votes
1answer
4k views

MySQL fails to start [InnoDB Repair]

After inspecting the log files I see that some of the databases have errors. 2017-05-16 08:52:48 7fc0cb8abb00 InnoDB: Assertion failure in thread 140466025315072 in file ha_innodb.cc line 21990 ...
0
votes
0answers
3k views

Trying to rebuild InnoDB files after server crash with no dump file

I am having a major problem starting mysql (using mariadb). I had a server crash (and didn't have a dump of the databases in the server. After restoring the server, I tried to manually copy the ...
1
vote
1answer
66 views

How to avoid updates to ALL rows in Mysql table? [closed]

Is there a way by which update to ALL rows of a Mysql table could be denied or avoided? Background: I have a table 'Orders' having 'OrderID' (Type VARCHAR) and 'VendorID' (Type Integer) as two of ...
2
votes
0answers
3k views

MySQL/InnoDB crash - “Log Sequence Number is in the future!”

So, after a database's files were copied over successfully, however an older version of InnoDB's logs were transferred, MySQL fails to start up in anything less than innodb_force_recovery=6. Due to ...
1
vote
2answers
600 views

MySQL limiting user disk space by tablespace

I am trying to figure out how to set a limit of disk space for users in MySQL. What I want is, for example, administrator assign 1G disk space for user A, if A used up the 1G disk space, MySQL would ...
0
votes
3answers
246 views

How can I copy only part of a MySQL database server to a new slave?

I'm trying to set up a slave MySQL server on another continent. I don't have enough space on the new server for the entire database that the master contains. Therefore I am trying to bring up this ...
15
votes
6answers
5k views

Backing up a MySQL database via ZFS snapshots

I've found a number of sites talking about doing exactly this, but I'm missing a few important details. The general steps are Run FLUSH TABLES WITH READ LOCK Take the ZFS snapshot Run UNLOCK TABLES ...

1
2 3 4 5
8