InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
12 views

MySQL what is the order of the table lock for this query?

An example query looks like this: INSERT INTO Adventure (sword, fighter, quest) SELECT Swords.id, Fighters.id, Quests.id FROM swords Swords, fighters Fighters, quests Quests WHERE Swords._id = ...
1
vote
0answers
6 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
0
votes
1answer
18 views

Can I use xtrabackup to import a database without altering others?

Context: Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos. I ...
0
votes
1answer
21 views

MySQL insert position value

I have trouble while inserting the values corresponding to a column: position. I have multiple inserts like this: INSERT INTO category_product(id_category,id_product,position) VALUES(2,1,1); INSERT ...
0
votes
1answer
11 views

What does FTS_ file mean after converting from MyISAM to InnoDB?

I have convert some tables in database from MyISAM to InnoDB. After that, I see some modification on file system. The size of database is grown up, from 2.8GB to 4.8GB and there are many file with ...
2
votes
1answer
96 views

LOCK=NONE when adding NOT NULL constraint fails with “cannot silently convert NULL values” error despite no NULL values being present

I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table ...
3
votes
1answer
4k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
0
votes
0answers
10 views

Problems with migration from Amazon RDS MySQL to MariaDB

We are trying to migrate from Amazon RDS MySQL to MariaDB for the performance benefits given by MariaDB. We have copied all the data from Amazon RDS MySQL to MariaDB and the database is up and ...
2
votes
4answers
4k views

Mysql innoDB write operations are extremely slow

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
1answer
270 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
0
votes
2answers
43 views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB sql dump that I need to import into MySQL. I haven't had to import such a huge sql dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
2
votes
1answer
28 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
2
votes
1answer
3k views

MySQL requires FORCE INDEX on huge table and simple SELECTs

We have an application which stores articles from different sources in a MySQL table and allows users to retrieve those articles ordered by date. Articles are always filtered by source, so for client ...
0
votes
2answers
1k views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
3
votes
2answers
412 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
1answer
23 views

Does a database update force index rebuild regardless of updated field?

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated. For example, if I have a table with 20 ...
1
vote
1answer
34 views

MySQL no query cache hits

My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB. mysql> SHOW ...
2
votes
1answer
19 views

MySQL table indexes change location

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
1
vote
1answer
13 views

75% of RAM for Innodb_buffer_pool size

I have read many documentation including percona and MariaDB documentation where they recommend setting innodb_buffer_pool_size to 70-80% of RAM. My question is, is that total RAM or available RAM? ...
6
votes
2answers
6k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
13
votes
2answers
17k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
0
votes
0answers
27 views

MySQL 5.6.19a InnoDB Concurrent Insert

I've looked through most of the threads on this topic but still had question I was hoping someone would be gracious enough to help with. I am seeing low CPU usage and write latency but transaction ...
1
vote
0answers
21 views

Need advice on optimizing database on a recurring basis

I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the ...
1
vote
1answer
75 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
1answer
235 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
2
votes
3answers
3k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
0
votes
2answers
716 views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
2
votes
1answer
5k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
0
votes
1answer
163 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
0
votes
0answers
25 views

Statements in State “Copying to tmp table” disrupt performance

On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". ...
0
votes
1answer
116 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...
1
vote
1answer
484 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
1
vote
1answer
1k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
2
votes
1answer
26 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
1
vote
1answer
167 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
0
votes
0answers
23 views

Advice on table structure change

I am in the process of converting an Access application that stores and reports evaluations to being web-based using php and mysql. I have converted the structure and data over from Access and I have ...
0
votes
1answer
179 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
3
votes
2answers
2k views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
0
votes
0answers
32 views

How to notice data lost in innodb engine

I have a web-application installed in some desktop pc, and I experienced some troubles with mysql innodb. In case of hardware fault (disk failure) or just power fault I lost many data in my database ...
2
votes
1answer
534 views

query cache hit value is not changing in my database

The query cache hit value is not changing in my database only for some queries.If I write select * from drugs_info limit 1000; the query cache hit has changed, but if I execute the following ...
2
votes
2answers
115 views

ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC Open log Flush log Open datafile Flush data O_SYNC NONE NONE fsync () ...
2
votes
1answer
42 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
2
votes
1answer
5k views

MySQL: Error reading communication packets

I get this warning in mysql, [Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets) I have been through few topics in google ...
0
votes
2answers
47 views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
1
vote
1answer
96 views

Innodb issue is preventing MySQL from starting [closed]

For some reason mysqld refused to start after I rebooted my server. Now when I try to start it I will get the following error: [root@vps1 ~]# service mysqld restart ERROR! MySQL server PID file ...
3
votes
1answer
2k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
0
votes
2answers
53 views

Do I need to reindex my table in mysql after delete?

I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
1
vote
1answer
230 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
0
votes
1answer
161 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
1
vote
1answer
31 views

MySQL InnoDB + high table creation-destruction rate

We are transferring a microsoft SQL based website (+app + plugins) structure to MySQL. The website has a high "Content + Comments + Activity Tracking" behaviour. Mainly because of the activity ...