InnoDB is the MySQL ACID-compliant storage engine.
2
votes
1answer
158 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, ...
1
vote
3answers
778 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
77 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 ...
0
votes
0answers
45 views
400GB Innodb table with deletes and no data_free?
I have an Innodb table which is over 400GB and performing quite a lot of deletes on it, so I thought I should try to optimize the free space kept it is probably creating. I ran this query to find out ...
1
vote
1answer
87 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
42 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
89 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
34 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, ...
0
votes
1answer
52 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 ...
0
votes
1answer
57 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
41 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
84 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
182 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
389 views
Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…?
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
173 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
30 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
106 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
2answers
135 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
57 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
74 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). ...
1
vote
1answer
225 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
80 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
139 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
133 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
163 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
564 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 ...
3
votes
2answers
122 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 ...
0
votes
2answers
287 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 ...
0
votes
1answer
94 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
189 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
262 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;
...
1
vote
1answer
273 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 ...
2
votes
3answers
996 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
177 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 ...
1
vote
2answers
325 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
368 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
219 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 ...
2
votes
2answers
168 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
182 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' | ...
1
vote
1answer
282 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
151 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 ...
0
votes
0answers
29 views
“Disk leak” with xtrabackup intrementals/innodb
I have several Percona Mysql databases being backed up using Xtraback. Aside from the mysqldb these are all innodb databases. These are setup on a weekly full backup schedule with multiple daily ...
3
votes
1answer
304 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
85 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
185 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 ...
5
votes
4answers
491 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
223 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
136 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 ...
3
votes
1answer
381 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 ...
