InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

12
votes
5answers
13k views

Why do MySQL threads often show “freeing items” status when the query cache is disabled?

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state. The MySQL manual suggests that at least part of the reason for a thread to be in ...
11
votes
1answer
14k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
8
votes
2answers
5k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
6
votes
4answers
8k views

Cannot open table mysql/innodb_index_stats

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
6
votes
3answers
6k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
8
votes
2answers
11k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
4
votes
1answer
245 views

AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?

I have a Multi-AZ AWS RDS instance running MYSQL 5.5 I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec ...
4
votes
1answer
8k views

How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine. When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work. I manually ran OPTIMIZE TABLE ...
3
votes
2answers
340 views

MySQL InnoDB Index in swap

I can't find any info regarding my question. This is more theoretical question. For example, i have table keywords. CREATE TABLE IF NOT EXISTS `keywords` ( `kid` int(11) unsigned NOT NULL ...
3
votes
2answers
2k views

Mysql load from infile stuck waiting on hard drive

I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
2answers
3k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
1
vote
2answers
142 views

What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

We have few thousand small databases with total .ibd files of about 800000. We noticed that MySQL takes couple of minutes to stop but about two hours to start. For e.g. while MySQL is starting and ...
8
votes
1answer
4k views

Is it possible to change ENUM() lists?

I wasn't sure if changing ENUM() list isn't possible so I made a test. In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no'). Then I executed... ...
6
votes
2answers
2k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
5
votes
3answers
335 views

InnoDB import performance

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far). I did some research how to ...
5
votes
3answers
11k views

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log). I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log). The DB on S1 was using one data file ...
3
votes
2answers
956 views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
3
votes
3answers
8k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
3
votes
1answer
577 views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
2
votes
2answers
914 views

How to tune mysql to be inmemory-like

I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is). Is it possible to force ...
2
votes
1answer
479 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
1
vote
1answer
2k 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 ...
6
votes
1answer
1k views

Tuning dedicated Percona Server with XtraDB for OLTP

Hardware specs for an OLTP DB dual quad core xeon with hyperthreading 24GB ram Hardware RAID10 BBU Software specs Standard install of Ubuntu 10.04LTS with default LVM partitioning Installed ...
4
votes
1answer
2k views

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system. Even though the guilty records have been removed or corrected, scanning ...
4
votes
5answers
4k views

TokuDB not much faster than MySQL

I have converted a MySQL database with 80.000.000 rows to TokuDB. Now when I run: select count(id) from xxx where active=1 it takes 90% of the time of the normal MySQL request. What do I have to ...
4
votes
2answers
5k views

Internal reason for killing process taking up long time in mysql

I copied a big table's structure with (it is an InnoDB table btw) CREATE TABLE tempTbl LIKE realTbl Then I changed an index, and filled it up so I could run some test. Filling it was done using: ...
3
votes
3answers
3k views

Issue after moving the ib_logfile1 and ib_logfile0 files

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
3
votes
2answers
785 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 ...
3
votes
1answer
4k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
3
votes
1answer
3k views

When are InnoDB table index statistics updated?

In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated? What events trigger such updates? I've seen comments suggesting that the following might trigger it: table is ...
3
votes
2answers
1k views

MySql replication with Raw Data and InnoDB tables

I want to start replication on DB that has many InnoDB tables and MyISAM tables. I did everything according to manual Creating a Data Snapshot Using Raw Data Files. Firstly, I tried copied all ...
3
votes
2answers
2k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
3
votes
2answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
2
votes
2answers
114 views

ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC Open log Flush log Open datafile Flush data O_SYNC NONE NONE fsync () ...
2
votes
2answers
189 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
2
votes
1answer
512 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
2
votes
3answers
1k views

whether a big (but not used) ibdata1 slow anything down

Hi I have an ibdata1 that is around 10 gig. I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore. I recently ...
2
votes
1answer
1k views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
2
votes
2answers
910 views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Title says it all. However i have to enter at least 30 characters.
1
vote
1answer
49 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
0answers
169 views

invalidating query cache entries(key) [duplicate]

when I tried to delete some records from a MYSQL server (version 5.0.95-log) and checked the process list I got the result as follows mysql> show processlist \G *************************** 2. ...
1
vote
2answers
717 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 ...
1
vote
1answer
775 views

How does innodb_stats_on_metadata relate to query execution plan stability?

The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example ...
0
votes
0answers
70 views

hot/live sql backup

please excuse my ignorant question as I am still fairly new to database things so I have a mysql database and a part of it has file with average size of 10M I store them in their own directory and ...
0
votes
1answer
133 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
0
votes
1answer
213 views

innodb completely crashed after interruption

I am having a major crash on mysql affecting a lot of websites. We had a crash on our SAN storage. Since that time, mysql refuse to start. I tried to reinstall mysql, but still no luck. I think ...
0
votes
1answer
93 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
0
votes
1answer
70 views

The most efficient method to obtain the total entries into a mysql table

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but ...
0
votes
0answers
487 views

InnoDB errors after having deleted “ibdata1”

By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly. After reading so many forums, I found a post that recommended deleting the 3 files ...
0
votes
2answers
716 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 ...