InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
785 views

Integer comparison in MySQL. does it cause lots of overhead?

I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to ...
3
votes
3answers
3k 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
2answers
571 views

Using MySQL InnoDB as an Archive

My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
2
votes
1answer
968 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
6
votes
1answer
13k views

Error when dropping InnoDB database

I am trying to drop a database in mysql, but every time I get this error ERROR 2013 (HY000): Lost connection to MySQL server during query even logged in as root. Originally, I had a corrupted ...
3
votes
1answer
116 views

Process sessions foreach minute

I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL). It's for online radio. A listener connects to the stream, and a row is created. Once ...
1
vote
2answers
59 views

Separated databases, separeted datas

We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB). I ...
1
vote
2answers
3k 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 ************...
5
votes
2answers
2k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
3
votes
1answer
1k views

Why is InnoDB auto_increment column skipping over large blocks of unused values?

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time. I turned on query ...
2
votes
1answer
675 views

What causes InnoDB to write 100% more pages while slowing down answering queries?

I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, 15KRPM,...
5
votes
3answers
10k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
0
votes
1answer
311 views

Maintenance in MYSQL when innodb_file_per_table disabled

I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace. But what if I ...
3
votes
1answer
2k views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
0
votes
0answers
166 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
2
votes
2answers
598 views

Negative numbers in InnoDB Status

There are some negative numbers displayed in the InnoDB Engine Status output. Anybody has encountered this or know why this happens ? ---------------------- BUFFER POOL AND MEMORY --------------------...
1
vote
1answer
63 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
120 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
725 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
1answer
136 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
213 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
1k 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 ...
5
votes
2answers
5k 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 ...
3
votes
1answer
3k 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
36 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
835 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
1k 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 ...
-2
votes
1answer
93 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
491 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). ...
3
votes
1answer
2k 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
482 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
3k 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]": innodb_data_home_dir="/...
2
votes
1answer
6k 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 ...
5
votes
1answer
11k 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
708 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 ...
6
votes
4answers
18k 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 ...
3
votes
1answer
2k 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
309 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 ...
4
votes
2answers
10k 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; ...
3
votes
1answer
7k 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 ...
8
votes
3answers
14k 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
797 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 ...
3
votes
2answers
12k 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 ...
4
votes
1answer
4k 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
1answer
703 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
2k 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
1k 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' | ...
6
votes
3answers
13k 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 ...
3
votes
1answer
7k 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
319 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 ...