Questions tagged [innodb]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
9 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 ...
0
votes
1answer
1k views

InnoDB table locks without apparent reason

I have an InnoDB table for visitors' counting, which has perfectly worked for several years, but it failed twice yesterday, may be cause of the increase of visitors Without apparent reason, this table ...
1
vote
2answers
10k views

how to permanently increase innodb_buffer_pool_size on ubuntu

I have read lot of posts & manuals to try to find out how to increase memory in mysql 5.6 on Ubuntu, which say there are 3 methods: by editing innodb_buffer_pool_size in the my.cnf by command ...
0
votes
1answer
2k views

MySQL tmp tables: how to clean up diskspace after killing a copying to tmp table process?

i ran out of disk space while running an alter table on a large table. I restarted MySQL afterwards and checked the MySQL tmp dir. A show status like '%tmp%' lists 5 tmp files and 4 tmp tables. A df ...
1
vote
0answers
28 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 ...
2
votes
1answer
9k views

Recover mysql database innodb 'table doesn't exist'

Our server crashed last week and we lost a lot of information. We were able to recover .frm files for the tables, ib_logfile0, ib_logfile1 and ibdata1 but no .ibd. I've tried the innodb-force-...
1
vote
1answer
5k 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
0answers
14 views

MYSQL router bootstrap error

i got below error when i bootstrap mysqlrouter for an innodb cluster (mysql) , can any one advise of causing this error ? ** bootstrap command mysqlrouter --bootstrap cluster@mysql-innodb01:3306 ...
0
votes
1answer
2k views

Get mysql database from raw files

My root access linux server has crashed and I cannot SSH into it or interact with it. I can, however tell the online console to do a network boot to a "rescue system" which allows me to access the ...
1
vote
2answers
505 views

Visualizing InnoDB's Foreign Keys

What would be the best way to get a visual representation of a group of MySQL tables' foreign key constraints? (other than drawing them out by hand) Something like this: (source: postgresonline.com)
0
votes
0answers
48 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 ...
2
votes
2answers
132 views

innodb storage options

I'm trying to figure out how to set up the storage for a site that currently requires only one server, but will certainly grow to demand more. I the database is largely innodb tables. I understand ...
0
votes
1answer
173 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
1k views

MySQL ibdata1 permissions error on cloned EC2 instance

I am trying to get a Rails app running on a cloned EC2 instance and am running into some permissions issues with my ibdata1 file. On the original instance, everything behaves as expected. However, ...
3
votes
1answer
1k views

High CPU on MySql Fusion-io server

I've just finished reading this old Q&A which had some really good detailed information on a similar setup to ours, though unfortunately our problem (now) is not with replication. MySQL ...
0
votes
1answer
2k 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
1answer
100 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
34 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 ...
0
votes
1answer
373 views

In MySQL 5.1 InnoDB, does the maximum length of a VARCHAR affect secondary index size? [closed]

Assuming the data is the same either way, does the maximum length of the VARCHAR affect the space usage of a secondary index? Does InnoDB use fixed length records for indexes? Assume that we're ...
3
votes
4answers
319 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
179 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
1k 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
737 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. ...
16
votes
3answers
72k views

Mysql crashed and won't start up

Our production mysql server just crashed and won't come back up. It's giving a segfault error. I tried a reboot, and just don't know what else to try. Here is the stacktrace: 140502 14:13:05 [Note] ...
32
votes
6answers
23k views

MySQL InnoDB - innodb_file_per_table cons?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table. I am ...
1
vote
0answers
25 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 ...
2
votes
2answers
2k views

InnoDB no longer available

Recently changed servers and went from CentOS 5.8 to CentOS 6.4. I installed MySQL 5.1.69 on my new server, and manually swapped /var/lib/mysql. I am not sure what version of MySQL was on my old ...
1
vote
0answers
92 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
162 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 ...
6
votes
2answers
4k views

Way to avoid server downtime when creating Master - Slave relationship?

I am preparing for setting up a MySQL master-slave or master-master relationship. Right now I have a single MySQL production server, and of course I don't want a lot of downtime while I connect the ...
9
votes
1answer
18k views

MySQL (MariaDB) crashes frequently

I recently migrated an old server running MySQL to a new VPS running MariaDB 5.5. I don't have too much running on the server (just a few PHP sites) and free memory seems to be OK, but the DB keeps ...
1
vote
1answer
708 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 ...
20
votes
2answers
57k views

Best MySQL cache settings for 8gb RAM dedicated MySQL server using only InnoDB (5gb database)

I'm a pretty big noob when it comes to setting up MySQL for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of MySQL, but I do know that ...
1
vote
2answers
739 views

MySQL: view read/write proportion, per specific table

I'm optimizing the configuration of a MySQL server, running only Bromine. This is non specific to application, and was just noted for completeness. I am trying to determine if switching from MyISAM to ...
0
votes
1answer
481 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
77 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 ...
0
votes
1answer
158 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 ...
1
vote
0answers
290 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 ...
4
votes
1answer
3k views

InnoDB: Fatal error: cannot allocate memory for the buffer pool

MySQL Seems to be crashing quite often, I'd say every hour or so. The only thing running on this server that is using MySQL is a Drupal installation that I'm working on, there's only one person that'...
5
votes
3answers
5k views

Picking the right innodb_buffer_pool_size

I have a 24GB machine running MySQL (only InnoDB tables). My innodb data set is 11.5GB big, and i have set my innodb_buffer_pool_size to 14GB to allow some growth. So the entire innodb data set is ...
2
votes
2answers
5k views

Mysql innodb_buffer_pool_size in relation to innodb_buffer_pool_instances

I run a virtual server with an Owncloud installation on it. Now Owncloud has the problem that the way it's developed at the moment, each file upload causes a little MySql overhead. So is very ...
0
votes
0answers
239 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
102 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
2answers
2k views

Will dropping AUTO_INCREMENT rebuild table in MySQL?

I've got table with following schema: CREATE TABLE `tblsomething` ( `something_id` int(11) NOT NULL AUTO_INCREMENT, … PRIMARY KEY (`something_id`) ) ENGINE=InnoDB AUTO_INCREMENT=144620955 I ...
1
vote
1answer
129 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
234 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
551 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 ...
106
votes
1answer
138k views

InnoDB: Error: log file ./ib_logfile0 is of different size

I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine. innodb_buffer_pool_size = 2560M innodb_log_file_size = 256M innodb_log_buffer_size = 8M ...
-1
votes
1answer
173 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
328 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+ ...