InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
51 views

Column order for better index

let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, ...
1
vote
2answers
106 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
1
vote
2answers
204 views

Are MySQL transactions under-utilised?

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related. Scenario : Suppose I have a series of queries which creates a user ...
0
votes
0answers
91 views

row locking or avoiding duplicate insertion on primarykey/otherfield combo

i have a table that stores additional data pertaining to files/images saved in files elsewhere, each entry can be associated with another type in the system, for users there may be one or more ...
0
votes
0answers
181 views

table is full in innodb

One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached ...
1
vote
1answer
644 views

MySQL InnoDB logfiles resized - review mysql-err.log for potential problems

I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool. (MySQL 5.1.53-Community on Ubuntu) To increase the ...
1
vote
2answers
2k views

Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…? [closed]

I manage a PHP/MySQL website : max 500 simultaneous users / average of 4000 per hour in update/read 1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM) InnoDB Storage engine on 100 tables for 1.4 Go ...
0
votes
1answer
1k views

What is the actual difference between innodb_autoinc_lock_mode 0,1,2

I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given. I see in "0" option, it locks the entire table and does the ...
0
votes
0answers
33 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
2answers
376 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
0
votes
1answer
508 views

MySQL InnoDB locking on combined UPDATE-JOIN statements

on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors: Statements writing to a table with an auto-increment column after selecting from another table ...
0
votes
0answers
74 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
175 views

Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). ...
2
votes
1answer
1k views

InnoDB temporary table location

I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink ...
3
votes
1answer
156 views

Which MySQL table to use for read-only, bulk-loaded tables?

So I have a workload on 2 key tables in my application with the following characteristics: READ-ONLY DATA The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
1
vote
1answer
886 views

Changing InnoDB data directory prevents MySQL service from starting

I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data. I modified the "my.ini" adding the following line after "[mysqld]": ...
0
votes
1answer
593 views

consequences of using “innodb_flush_method = O_DIRECT” without having a battery backed write cache? or on a KVM guest?

Mysql 5.5.29 Innodb- 128GB Ram - 32 cores - Raid 10 SSD. Our server which is a dedicated KVM guest on a 'baremetal' is hosting our heavy read-write DB server. Everything is file-per-table. ...
1
vote
1answer
1k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
2
votes
1answer
4k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
4
votes
2answers
291 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
4
votes
2answers
6k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
1
vote
1answer
663 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
2
votes
1answer
238 views

Restore InnoDB tables / database using only data files

I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine. I have run DROP DATABASE dbName statement by mistake. I have no backup of that ...
1
vote
1answer
2k views

What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?

2M rows inserted using LOAD DATA INFILE to innodb takes 7.5min, and profiling shows 99% of that time is in "System lock". Does this tell me anything useful? mysql> set profiling=1; ...
2
votes
1answer
3k views

Very poor insert performance MySQL / Percona Server

I'm running a Percona-Server instance off an Ubuntu server install. I'm using an application that needs to access this database, and its having VERY poor performance. Once the database has been ...
4
votes
3answers
5k views

MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done. I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
1
vote
1answer
509 views

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
2
votes
2answers
3k views

Finding and fixing InnoDB index corruption

I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a ...
3
votes
1answer
1k views

Very Slow MySQL Queries, even with indexes

I have a relatively large 4-deep relational data setup like this: client_applications: (potentially 1,000's of records)    - ...    - account_id    - ...
0
votes
0answers
390 views

InnoDB row level locks without SELECTs?

I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation: Each client runs the following ...
3
votes
2answers
618 views

Database space doesn't match ibdata1 size

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table. In the MySql workbench, when I query for the databases used space, with this query SELECT ...
1
vote
1answer
254 views

Queries getting stuck on very simple COUNT queries

process list: | 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | ...
2
votes
1answer
7k views

Mysql slowly uses memory until it starts to use swap

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
2
votes
1answer
2k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
1
vote
1answer
235 views

Set up replication and migrate to InnoDB with low downtime

Current situation is this: I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large. The database is ...
3
votes
1answer
670 views

Mysql Server optimization tips needed! (InnoDB)

I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
1
vote
1answer
236 views

Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
0
votes
1answer
1k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
6
votes
4answers
2k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
1
vote
1answer
926 views

Where is the MySQL variable - innodb_flush_method?

I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES. But I could not find it ...
2
votes
2answers
278 views

Bin logs with high i/o

I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload. So, my knowledge of MySQL is minimal. Few days ago I start use MySQL with a basic configuration as back-end for a ...
5
votes
1answer
2k 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
186 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
539 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
186 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
628 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
582 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
4k 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
2k 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
158 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 | ...