InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

-1
votes
1answer
61 views

Statements in State “Copying to tmp table” disrupt performance [closed]

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". ...
1
vote
1answer
30 views

MySQL InnoDB restoration from hardware failure

One of my server went down due to old hardware failure on a old Dell server that's over 10 years old. I was able to recover the files and got it to a local machine. I want to restore a MySQL instance ...
5
votes
3answers
15k views

InnoDB row locking - how to implement

I've been looking around now, reading the mysql site and I still can't see exactly how it works. I want to select and row lock the result for writing, write the change and release the lock. ...
1
vote
0answers
106 views

25.000+ rows - MySQL SELECT consecutive numbers performance issue

I have tried to be as explicit as I could. Below you have the query explained in details What I want to obtain? - I want to SELECT start_number and end_number from a set of consecutive numbers from ...
0
votes
2answers
2k views

Unable to change engine to MyISAM in MySQL

I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine. I have checked following engines are supported in MySQL: show engines\G ...
0
votes
1answer
89 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
0
votes
1answer
580 views

Need help to understand how information_schema.TABLES get updated

I created large innodb table mysql> select count(*) from big_data; +----------+ | count(*) | +----------+ | 23822336 | +----------+ checked the stats in informaion_schema.TABLES mysql> ...
1
vote
1answer
78 views

MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss

Originally posted to https://www.facebook.com/groups/GNUAndLinux/permalink/10155020539805019/ but no replies received. I run Percona Server 5.6.21-70.0-688 x64 on Ubuntu Server 12.04 LTS x64, 64GB ...
1
vote
4answers
125 views

How to backup live mysql database?

I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website. How do I make backup of my live ...
2
votes
3answers
77 views

Drop Tables with Bigger size

We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that ...
0
votes
2answers
44 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 ...
3
votes
1answer
95 views

InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?

I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around? For examples: ...
3
votes
1answer
576 views

InnoDB inserts 100 times slower than SQL Server inserts

I have a simple table with two columns: id (autonumber) and name (char(255). When I use C# to insert 100 records in a loop it takes about 30 milliseconds per insert. This is way too slow. So I ...
0
votes
0answers
27 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
201 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. ...
1
vote
1answer
54 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
42 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
194 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
5k 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
118 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
1answer
156 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
129 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
89 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
74 views

MySQL table indexes change location [closed]

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
36 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? ...
14
votes
2answers
19k 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 ...
1
vote
0answers
137 views

MySQL 5.6.19a InnoDB Concurrent Insert [closed]

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
22 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 ...
3
votes
3answers
5k 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 ...
2
votes
1answer
6k 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
363 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
29 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
0answers
69 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 ...
3
votes
1answer
907 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
1answer
7k 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 ...
1
vote
1answer
290 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 ...
0
votes
2answers
121 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
53 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 ...
0
votes
1answer
709 views

ib_logfile0 and ib_logfile1 got deleted

After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed: ...
0
votes
0answers
71 views

mysql uses a lot of virtual memory

I've a mysql server with a lot of small size innodb databases (each db also contains a lot of small tables) & 6GB of RAM dedicated to mysql server. Recently I've increased my ...
1
vote
0answers
43 views

Can't seem to optimize joined select query

Can't seem to figure this one out. Query is taking upwards of 7-8 seconds which is just not acceptable and seems as though it will get worse as the table(s) get larger. Without going into a ton of ...
0
votes
0answers
201 views

MySQL Inno DB Crash

This is the log of the crash. Basically every time I run a query it looses connection an crash. 2014-11-03 19:46:42 58021 [Note] Event Scheduler: Loaded 0 events 2014-11-03 19:46:42 58021 [Note] ...
0
votes
1answer
64 views

moving binary database folder is causing issues with innodb tables

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables ...
1
vote
1answer
2k views

How to recover the innodb when mysql data dir moved from one system to another system?

I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted ...
1
vote
1answer
83 views

Move a MySQL 5.5 database to a server running MySQL 5.1

I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box ...
1
vote
1answer
29 views

Does InnoDB Log Files take RAM space or Disk Space

When I set InnoDB Log File size to 512M, does it take the space (512M * 2) from RAM or the SSD Disk space? When I explored, I can see that the log files are located inside /var/lib/mysql folder. So ...
0
votes
0answers
74 views

MySQL Crash due to “InnoDB Assertion” after Query

Current setup: Ubuntu 12.04 LTS MySQL 5.5.31 Current Use: Slave - Only ETL queries 64 GB RAM Intel(R) Xeon(R) CPU X5670 @ 2.93GHz - 12 Cores - Hyperthreaded (24 Logical) MySQL Main Settings: ...
0
votes
1answer
564 views

How to resolve “InnoDB data dictionary has tablespace id N” [duplicate]

I recently messsed up a migration to table-per-file for my dbs. In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed ...
-2
votes
1answer
91 views

How to efficiently model this simple data structure?

catId(INT) | userId(INT) | catName(VARCHAR) 0 1 Cat1 1 1 Cat2 2 2 U2Cat1 3 2 ...
1
vote
1answer
93 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...