InnoDB is the main ACID-compliant Storage Engine used in MySQL.

learn more… | top users | synonyms

2
votes
2answers
2k views

Setting MySQL INNODB Compression KEY_BLOCK_SIZE

I recently started using the Barracuda InnoDB/MySQL table format which allows compression. I compressed one of my tables by running: alter table pricing row_format=compressed, key_block_size=8; ...
2
votes
2answers
99 views

Does MySQL InnoDB engine use tablecache?

Does MySQL InnoDB engine use tablecache? I migrated from MyIsam to Innodb as the table engine and the monitors for "table-cache" hitrate are super low. Does Innodb use table-cache?
0
votes
1answer
408 views

installing mysql innodb engine on redhat box

I'm using SL6.1 on an X86-64 server box. For whatever reasons, it looks like the stock RHEL-anything is not going to be including the mysql innodb engine anytime in the foreseeable future: ...
3
votes
2answers
3k views

Picking the right innodb_buffer_pool_size

I have a 24GB machine running MySQL (only InnoDB tables). My innodb data set is 11.5GB big, and i have set my innodb_buffer_pool_size to 14GB to allow some growth. So the entire innodb data set is ...
0
votes
3answers
1k views

MySQL server not starting up

I have a server running CentOS 5.5 x86_64, and have recently installed MySQL Server via YUM, but when starting the server I get: [root@server etc]# service mysqld start Warning: World-writable ...
2
votes
1answer
139 views

mysql Innodb engine disappear

I had to change the tmpdir directory from /tmp to /db/tmp/ folder due to the fact I didn't have enough space. When I changed my path and I ran SHOW ENGINES; I got below result; ARCHIVE MRG_MYISAM ...
0
votes
1answer
595 views

Moved mySQL to an EBS volume, now innoDB performs very slowly

Hello has anyone had a similar problem to this.. I've set up a new instance on Amazon EC2 for a client. I've also attached a 250GB EBS volume to hold the files and configured mySQL to use the EBS ...
1
vote
1answer
743 views

Monitoring MySQL Rollback Segments

I recently discovered the default open transaction ceiling of 1024 in MySQL. I have since increased the limit using innodb_extra_rsegments. However, I would like to keep my eye on this moving forward. ...
1
vote
1answer
873 views

Creating a raw InnoDB disk in Ubuntu 10.04 with LVM

I'm trying to create a 2TB raw partition for MySQL/InnoDB to use on one of my LVM's. I created the raw disk: Disk /dev/mapper/g0-sql: 2190.4 GB, 2190433320960 bytes 255 heads, 63 sectors/track, ...
3
votes
2answers
1k views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
5
votes
1answer
586 views

MySQL transaction issue with Django and Amazon RDS

I'm having a problem with a MySQL 5.5 database instance running on Amazon RDS. Periodically (once every couple of days) a transaction will fail to complete and appears to leave the tables locked. ...
2
votes
2answers
682 views

What happens to InnoDB and MyISAM tables if the power fails during a transaction?

We have two MySQL tables, one is InnoDB and the other is MyISAM. If I execute a chunk of inserts in a single transaction on both tables and the power fails in the middle, what will be the status? ...
5
votes
2answers
3k views

Way to avoid server downtime when creating Master - Slave relationship?

I am preparing for setting up a MySQL master-slave or master-master relationship. Right now I have a single MySQL production server, and of course I don't want a lot of downtime while I connect the ...
3
votes
1answer
2k views

InnoDB “log sequence in the future!” crashing, won't start

I stopped mysql only to find that it wouldn't come back up, /etc/init.d/mysql start only outputs . . . . . . failed. I've narrowed it down to an issue with InnoDB. The database starts when ...
0
votes
1answer
288 views

mysql innodb broken, after hostname change

Gents, Have a mysql cluster down right now... in dev, but down nonetheless. This occurred after a hostname change on both nodes. InnoDB: Check that you do not already have another mysqld process ...
1
vote
2answers
3k views

InnoDB recovery

My MySQL server run to crash due to (page file write error). I did a disk diagnosis but no errors found. I restarted MySQL server. It scanned the bin log like for 1-2 hours and recorded log as below: ...
1
vote
1answer
1k views

'Table is full' error on table with 3 rows

Every few weeks, i receive the message The table 'ttrss_users' is full from an installation of http://tt-rss.org/redmine/ (a RSS reader application written in PHP). It's always the users table ...
0
votes
2answers
714 views

I had to recover InnoDB tables from my backup, but mysql is crashing

I just recovered my InnoDB tables from my backup (copied ibdata1, ib_logfile1, ib_logfile0) However, when I start mysql now, and try to access my site with InnoDB tables, *I get these errors Got ...
14
votes
2answers
26k views

Best Mysql cache settings for 8gb RAM dedicated mysql server using only Innodb (5gb database)

I'm a pretty big noob when it comes to setting up mysql for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of mysql, but I do know that ...
0
votes
1answer
600 views

How to prevent InnoDB log files from getting corrupted?

We run a MySQL server using 600GB of storage over 4 EBS volumes on Amazon EC2. About every 2-3 weeks, the active InnoDB log file gets corrupted, and MySQL won't allow any access on those tables ...
1
vote
0answers
407 views

MySQL InnoDB: Assertion failure ibuf0ibuf.c line 4185 unrecoverable data crash

I get this assertion failure on MySQL 5.5.15 version. Here are the MySQL server errors: 110927 21:56:51 InnoDB: Assertion failure in thread 1126107456 in file ibuf0ibuf.c line 4185 InnoDB: Failing ...
1
vote
3answers
7k views

Plesk: libmysqlclient_16 not defined in file libmysqlclient_r.so.16

I've found this error in my logs: relocation error: /usr/lib64/libmyodbc5.so: symbol strmov, version libmysqlclient_16 not defined in file libmysqlclient_r.so.16 with link time reference ...
2
votes
2answers
2k views

Unknown table engine 'InnoDB' after deleting ibdata1 file

I was having innodb with one ibdata file. i have changed it in my.cnf to have ibd file for every table (innodb_file_per_table). then i run the following query on all of my innodb tables to have its ...
1
vote
1answer
333 views

MySQL: view read/write proportion, per specific table

I'm optimizing the configuration of a MySQL server, running only Bromine. This is non specific to application, and was just noted for completeness. I am trying to determine if switching from MyISAM to ...
3
votes
1answer
1k views

Transferring InnoDB database via rsync

We are in the process of switching to a new RAID array and need to copy our MySQL InnoDB database to the new RAID array while minimizing downtime (50GB database). I understand that using rsync on a ...
3
votes
1answer
126 views

mysql innodb problem

seeing the following in my mysql error log file: InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 268435456 bytes! 110927 18:36:37 ...
2
votes
2answers
994 views

which is faster mysqldump/restore or alter table?

I have InnoDB databases and they are using the same ibdata file, the size of this ibdata is around 250G. now i have one of those DBs around 100G. My manager asked me to move this DB to another server ...
1
vote
1answer
141 views

Can I use a newer InnoDB plugin in Ubuntu MySQL?

The MySQL that comes with Ubuntu 10.04.2 is 5.1.41, which is distributed by MySQL with InnoDB 1.0.5. But for some reason Ubuntu ships it with InnoDB 1.0. Can I install InnoDB 1.0.5 (or newer?)
9
votes
2answers
12k views

MySQL InnoDB database 'hangs' on selects

I'm trying to fix MySQL configuration on our server. Our app's specifics is that a lot of data is stored in single table (currently over 300 millions of rows). This table is used often for inserts ...
3
votes
1answer
2k views

MySQL show table status always returns Data_free 17825792

When I execute SHOW TABLE STATUS databaseName; I get Data_free info with 17825792 value in all tables : | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | ...
0
votes
1answer
288 views

Measuring Performance through SHOW INNODB STATUS;

When looking at the output of SHOW INNODB STATUS on mysql what are the most important values to be looking at, and what would be an indicator of poor performance? Specifically under FILE I/O and ...
2
votes
2answers
32 views

What do I need to know in order to change a table schema that is being replicated?

I need to update the schema as I accidentally left out some foreign key constraints. The database server is MySQL 5.1.48 and the tables being altered are InnoDB. The server is replicated using ...
2
votes
2answers
2k views

Whats the best practice for defragmenting a MySQL InnoDB table in production?

A lot of my Innodb tables require 'defragmentaion'. My understanding is that 'OPTIMIZE' may not be the best way to do it. Some people suggest doing a 'ALTER TABLE' to have Mysql rebuild (and therefore ...
5
votes
1answer
1k views

16 Cores 12 GB RAM Dedicated MySql Configuration

I have a dedicated 16 Cores 12 GB RAM Mysql server which is performing quite badly since I configure it for a 16 GB server and I guess that explains why in the world the site now takes more than 500 ...
3
votes
1answer
98 views

InnoDB erratic performance

I'm doing some tests with a really simple InnoDB table (named Test) with the following structure: Id int(10) unsigned NOT NULL AUTO_INCREMENT UserId int(10) NOT NULL Body varchar(512) COLLATE ...
4
votes
1answer
383 views

Mysql InnoDB table size performance

I have a table that is closely approaching 2 million records. This table stores a history of transactions. This is on a high traffic website but also the table is not accessed regularly. We ...
-1
votes
2answers
2k views

Cannot drop index from MySQL INNODB table with foreign keys/references

I'm about to drop an index from a mySQL INNODB table. There are references defined already. When I try to alter it, it says: #1025 - Error on rename of 'X/#sql-2a5f_219828' to 'X' (errno: 150) ...
0
votes
2answers
166 views

mysql error InnoDB: 13

mysql not started @ server boot automatically but I can start manually as long as log as user "mysql" by using "/etc/init.d/mysql.server" the error I got on the log is InnoDB: Operating system error ...
1
vote
1answer
620 views

fine tuning of my.cnf for InnoDB tables fails with setting of: `innodb_log_file_size=256`

I read some fine tuning recommendations on the mysqlperfomanceblog and tryed to adapt them. I added to my.cnf: innodb_buffer_pool_size = 1000M innodb_log_file_size = 256M After ...
2
votes
3answers
5k views

How to detect if InnoDB has pool size big enough?

I found out how much the innodb pool size has allocated memory from show innodb status but I couldn't figure out how much actually it uses. Maybe it needs more or I can take some memory off from it? ...
0
votes
1answer
336 views

Mysql : Cannot create Index:

Hi all we are having trouble to create/drop index(s) on a table I keep getting the following error: ERROR 1280 (42000): Incorrect index name '<>' Now the table has the following: 1. PRIMARY ...
9
votes
1answer
11k views

MySQL: InnoDB: ERROR: the age of the last checkpoint is X, which exceeds the log group capacity Y?

In a production MySQL environment the following error message is written to /var/log/mysql/error.log every fourth minute: 110723 18:36:02 InnoDB: ERROR: the age of the last checkpoint is 9433856, ...
0
votes
1answer
236 views

Cannot start MySQL on windows box with named pipes

I've installed MySQL server on Windows although in the configuration have selected to not run MySQL as a server and have disabled TCP/IP access so only named pipes can be used, I'm now having problems ...
1
vote
2answers
693 views

Copying /var/lib/mysql files over resulted in InnoDB corruption

My server had to be re-installed yesterday so they replaced the hard drive and hooked the old one up via USB. I grabbed the database files (/var/lib/mysql) and downloaded them and then I erased all ...
1
vote
3answers
850 views

MySQL / InnoDB replication: how to perform crash recovery?

How do I perform crash recovery in a MySQL/InnoDB Master-Slave asynchronous replication setup? Specifically: If a slave crashes, how do I make it syncs up with the master after I bring it back up? ...
0
votes
2answers
801 views

MySQL fails to start with one config line change - innodb_buffer_pool_size = 1G

My curent config works great but my innodb_buffer_pool_size is too small as the database is very large. All I am changing in my MySQL.cnf is innodb_buffer_pool_size = 1G to ...
0
votes
1answer
200 views

MySQL 5.1 to 5.5 Upgrade - InnoDB question

I posted this on stackoverflow, but was advised that here might be better... I am planning an upgrade of some MySQL servers (5.1 to 5.5). I am aware that the InnoDB plugin is now built in to MySQL. ...
2
votes
2answers
411 views

MySQL not using specified RAM anymore

A few days ago I got a new machine setup running CentOS 5.6 (64bit), with 12GB ddr3 ram, 4x SAS 15k rpm drives in RAID10. On this machine I'm running MySQL Server version: ...
0
votes
2answers
557 views

MySql is not picking up changes to my.cnf file on a custom Ubuntu AMI. How do I fix this?

I created an AMI from my running EC2 Ubuntu instance. I then brought it up, and MySql is not running the same way on this copied instance. 1) Changes to my my.cnf file are not getting picked up. ...
3
votes
1answer
366 views

How much disk space does innodb need to ALTER TABLE

I have a large InnoDB table that I want to alter by adding a small column to. I'm prepared to wait a while, but I'm wondering how much disk space will the ALTER TABLE command consume while it's ...