InnoDB is the MySQL ACID-compliant storage engine.
3
votes
1answer
383 views
Deleting MySQL table with pending transactions
Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?
What happened:
I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
1
vote
1answer
71 views
mysql freeze/crash on JOIN
here is query:
SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
nw.status, src.title as src_title, src.keyword as src_keyword,
src.url as src_url, src.icon as ...
3
votes
2answers
217 views
How to split a string to and get info from another table in mysql
I have a system to assign score for each sentence stored in a column:
TABLE 1: word_scores
word score
this 3
is 4
a 5
test 1
another 0
sentence 8
TABLE 2: ...
4
votes
1answer
124 views
Which MySQL storage engine is the most similar to ARCHIVE when that is not available?
I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
1
vote
2answers
165 views
MySQL Enterprise Backup error: Log scan was only able to reach xxxx
I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.
I get this error:
mysqlbackup: ERROR: Log scan was only able to reach ...
6
votes
1answer
218 views
LOAD DATA INFILE blocks on concurrent use against MyISAM tables
I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
2
votes
2answers
564 views
Dynamic change to innodb_flush_log_at_trx_commit
This is related to this question. It does help to get better performance for InnoDB tables.
According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change ...
3
votes
3answers
277 views
InnoDB insertion faster
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
3
votes
2answers
137 views
Tablestructure for fast inserts/deletes with foreign keys
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:
id | competitors | some infos about competitor | ...
5
votes
1answer
611 views
MySQL INNODB corruption after server crash during concurrent truncate command
My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
3
votes
2answers
158 views
How to concatenate a column after x rows?
I have a table with columns
object varchar(255),
frequency tinyint(3) unsigned,
I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as
.. ...
1
vote
1answer
113 views
innodb changing row format doesn't decrease table size? [closed]
We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone ...
1
vote
1answer
113 views
InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”
MySQL 5.5.24 .
We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on.
The tables were freshly dumped and ...
1
vote
1answer
169 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
2answers
105 views
Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
I have a table that has a large number of rows in it.
The primary key (an auto-incrementing integer) is, by default, indexed.
While waiting for a row count to be returned I did an EXPLAIN in another ...
0
votes
1answer
165 views
Unable to take backup of mysql database using Xtrabackup
I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .
Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I ...
3
votes
0answers
103 views
“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28
We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28.
During high load, randomly we got the below error.
MySQLcheck, innochecksum shows no ...
1
vote
2answers
495 views
jdbc+mysql: “Deadlock found when trying to get lock; try restarting transaction”
After recovering from a disk-full problem, I started getting these errors in my logs:
java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at ...
5
votes
2answers
105 views
Resolving DUPLICATE for a column with UNIQUE
I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
2
votes
1answer
295 views
MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!
So I wanted to test MySQL recovery from Innodb corruption.
file-per-table is enabled.
I have a few test db's on the MySQL instance.
I shutdown mysql. Manually edited/added random characters to an ...
0
votes
0answers
87 views
innodb deadlock on different tables
I am using mysql 5.5.28.
I would like to ask a question about the nature of deadlocks in innodb engine.
I monitor deadlock with pt-deadlock-logger.
Recently I found deadlock of two transactions to ...
1
vote
2answers
116 views
Is it always bad to use SELECT * FROM?
It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed.
Is it still bad to use SELECT * when ...
3
votes
1answer
528 views
Innodb, MySQL 5.5.28 - Segmentation Signal 11 faults, on high load. .. my.cnf file included
We have a high-end server,
128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 .
Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of ...
4
votes
1answer
133 views
I have multiple ibdata files : What should I do with them?
I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:...
I'm on MySQL 5.0.x...
Directory of C:\MySQL ...
0
votes
2answers
128 views
How long can Deadlock info hold in innodb status page?
When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp
------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31
Questions
Any idea how long Deadlock ...
2
votes
1answer
321 views
mysqlcheck to optimize innodb tables — performance/scheduling
I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.
Is there a ...
2
votes
1answer
243 views
inner join on PK with extra criteria slow despite indices?
Given the two tables below I am struggling to understand:
why is the third query slow even though first two queries are fast
what exactly is EXPLAIN saying
can I do anything to significantly speed ...
1
vote
1answer
481 views
InnoDB Performance Tuning
I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem.
I'm a RoR developer and our team is currently short a DBA, so we're ...
-1
votes
1answer
41 views
What is an accepted method of keeping old data in MySQL?
I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here.
Some background: I am running an application whereby users can take tests using ...
2
votes
1answer
91 views
Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?
Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL"
...
2
votes
1answer
281 views
Monstrous query hanging on “copying to tmp table” in MySQL
I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust. Though I'm somewhat of novice at writing SQL queries, at first glance the ...
6
votes
1answer
206 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
1
vote
2answers
357 views
How to insert into junction table using triggers
Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.
Artist and Song are base tables and SongArtist is the ...
1
vote
1answer
235 views
How to know the current log sequence number in 5.0.X
I would like to test the optimal value for iblog files on my mysql which is 5.0.77
I found the below procedure in mysqlperformance blog:
...
0
votes
1answer
93 views
What MySQL storage engine and table schema should I use?
I am making a web project and I created necessary and the most important table, but I am not sure that I did it right.
In this site users will post data like text, picture, text's category, etc. I ...
1
vote
2answers
356 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 ...
1
vote
2answers
774 views
How to analyse innodb status on deadlock in insert Query?
I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status.
OS WAIT ARRAY INFO: reservation count 247864, signal count 247470
Mutex ...
3
votes
2answers
115 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
5
votes
3answers
526 views
InnoDB Master-Master replication goes inconsistant after power failure tests
Afternoon gents,
I'm currently stress testing a Master-Master replication setup using InnoDB as database engine.
We're using this simple script for testing which we run in Linux CLI from a remote ...
0
votes
0answers
67 views
Should I use compression in my InnoDB tables for this project?
I'm building a web portal that has a lot of tables and data is mostly id's (integers) in range 0 -> max, and strings of chars (VARCHARs) and fixed char(1) like 'y' and 'n' s mostly.
I switched (still ...
0
votes
0answers
164 views
Error connecting to DB
In regards to:
http://serverfault.com/questions/442218/remounting-mysql-under-a-new-mount-point
Everything is running. MySQL started but for websites they can not connect to the mysql database at ...
2
votes
1answer
146 views
Recovering from a stuck query
I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will ...
3
votes
1answer
861 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 ...
2
votes
1answer
155 views
MySQL Unique Index Keylength
I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key.
The engine of choice here is InnoDB.
There are ...
1
vote
2answers
785 views
How much memory do I need for innodb buffer pool?
I read here that
You need buffer pool a bit (say 10%) larger than your data (total size
of Innodb TableSpaces)
On the other hand I've read elswher that innodb_buffer_pool_size must be up to ...
4
votes
2answers
4k views
How large should be mysql innodb_buffer_pool_size?
I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
1
vote
1answer
354 views
MySQL: Unique constraint on large column
I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.
MySQL appears to ...
1
vote
1answer
585 views
What exactly are iblog files in mysql
I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
1
vote
2answers
821 views
Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?
I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
1
vote
2answers
194 views
MySQL-5.5 InnoDB memory issue
Version in use is mysql-5.5.24.
In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version.
I am ...
