InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
9 views

Only InnoDB databases locking up

I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation. At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up. ...
0
votes
1answer
54 views

what's a reliable way to determine the ideal innodb_thread_concurrency setting?

on an article from 2006 , here they suggest such formula. 2*(number of cpus + Number of Disks) has anyone tried their settings according to this formula ? or do you have any other formula ? our ...
0
votes
1answer
24 views

MySQL InnoDB Deadlock For 2 simple insert queries

I have a deadlock for this two insert queries: insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, ...
0
votes
1answer
17 views

#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on. For this tried to create same table with create ...
0
votes
2answers
40 views

Minimizing database size (Many small tables)

Our database is interesting in that we have a large amount of tables that take up a correspondingly large amount of space, but when we do a MySQL dump of said database, it is surprisingly small. ...
0
votes
1answer
33 views

Pagination, check number of selected records

I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution. Imagine that SELECT id... is the ...
2
votes
2answers
38 views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
0
votes
0answers
16 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
1
vote
1answer
22 views

trying to understand table size

Can somebody help explain the "data_length" of the table? Why is it so big in my example below of a table with just one column and one row? mysql> create table `network_type` ( `id` int(11) NOT ...
0
votes
0answers
5 views

Percona XtraDB and innodb_flush_log_at_trx_commit=2

I'm running Percona XtraDB on a three nodes cluster. Each one has this piece of configuration: innodb_flush_log_at_trx_commit=2 sync_binlog=0 I perfectly know that this is not the safest ...
0
votes
1answer
51 views

Can I lose data if I use InnoDB? [closed]

During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.
0
votes
0answers
14 views

ERROR 2013 (HY000) When long string data managed

This is my first question on this community, I'll explain the situation: I have a server with PHP and Mysql on it, on that server I have a wordpress blog. Lately i've been receiving a lot of spam ...
1
vote
0answers
31 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 ...
1
vote
1answer
19 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
1answer
204 views

SELECT INTO OUTFILE vs INSERT INTO … SELECT

During my cursory research I have not been able to find a definitive answer on the benefits that SELECT INTO OUTFILE provides over INSERT INTO ... SELECT. Upon reading the docs related to INSERT INTO ...
1
vote
1answer
51 views

DELETE seems to hang

The following command: DELETE FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60) seems to hang indefinitely. However, the corresponding SELECT * command completes in a fraction of ...
0
votes
1answer
27 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" ...
1
vote
1answer
36 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 ...
2
votes
3answers
62 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 ...
1
vote
4answers
79 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 ...
0
votes
1answer
31 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
3
votes
1answer
43 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: ...
2
votes
1answer
142 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
18 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
33 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
2answers
29 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
0answers
37 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 ...
0
votes
1answer
30 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 ...
2
votes
1answer
41 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 ...
0
votes
1answer
18 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 ...
1
vote
1answer
37 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 ...
2
votes
1answer
28 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
37 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 ...
1
vote
1answer
18 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? ...
1
vote
0answers
48 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
2answers
98 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. ...
-1
votes
1answer
38 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". ...
2
votes
1answer
50 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 ...
0
votes
0answers
25 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
39 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
60 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 ...
0
votes
2answers
138 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 ...
0
votes
2answers
66 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
35 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
0answers
35 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
40 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
1answer
25 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 ...
0
votes
0answers
41 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] ...
1
vote
1answer
20 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 ...