InnoDB is the MySQL ACID-compliant storage engine.
3
votes
4answers
740 views
Prevent reset of auto_increment id in Innodb database after server restart
I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.
Normally, this isn't a ...
3
votes
3answers
103 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 ...
0
votes
2answers
189 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 ...
1
vote
1answer
169 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 ...
0
votes
3answers
48 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 ...
0
votes
0answers
7 views
ibdata deleted and innodb error [duplicate]
By accident I have deleted ibdata1 file in MySQL directory because the MySQL wasn't functioning correctly so after reading so many forums I found a post asking to delete the 3 files in MySQL directory ...
0
votes
0answers
50 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 ...
1
vote
1answer
83 views
Mysql DB server hits 400% CPU
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.
- load average 40 to 50
- CPU % - 400%
- idle % - 45%
- wait % - ...
1
vote
1answer
235 views
InnoDB - How to get top locked tables and rows which are locked
I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
1
vote
1answer
44 views
MySQL InnoDB does not return the last row on select with DESC order
We have a table scheme like following:
CREATE TABLE IF NOT EXISTS `offers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`campaign_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`price` double ...
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, ...
3
votes
1answer
210 views
MySQL: Can't find .ibd files after turning on innodb_file_per_table
Question:
This a question about MySQL. Let me give the specifics of my setup:
Mac OSX 10.6.8
MySQL 5.5.28
Workbench 5.2.44 for all administration
InnoDB engine exclusively
In order to manage ...
5
votes
1answer
264 views
MySQL hogging memory
An installation of MySQL 5.6.10 on a virtualized Ubuntu 12.04 is exhibiting massive memory hogging. The mysqld process claims the entire available memory within a couple of hours of uptime and forces ...
16
votes
3answers
16k views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
2
votes
1answer
210 views
MySQL - Installing InnoDB Plugin
I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61.
I want to upgrade the master server to a stronger machine with the following:
2 processors - 6 cores each
48GB of RAM
To ...
0
votes
0answers
31 views
MM Replication and Zabbix in slave, Error code 1032
I have multi-master replication and using Zabbix for server monitoring. Since the replication is row based, I get this error in slave everytime which also breaks my replication. The error I get is:
...
0
votes
2answers
49 views
Unable to drop the table since its showing waiting for meta data lock
We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
3
votes
2answers
859 views
“freeing items” taking forever on inserts, updates and deletes
Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/).
After the migration, ...
0
votes
1answer
191 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
...
1
vote
1answer
52 views
Issues converting MyISAM table to InnoDB (auto column issue)
I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.
The following is the table dump:
--
-- Table structure for table `companies`
--
DROP TABLE IF EXISTS `companies`;
...
0
votes
0answers
65 views
InnoDB tuning with 1G of ram limit
I am trying to calculate variable moving averages crossover with variable dates.
That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
1
vote
2answers
42 views
What is the size of currently-existing tmp tables in innodb?
This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
0
votes
0answers
25 views
Defacto way of fixing constraint issues without downtime?
I come across two constraint issues all the time (UNIQUE, and referential integrity constraints) within InnoDB. My question is when these issues arise reproducibly what is typically the solution? I'm ...
1
vote
1answer
113 views
innodb changing row format doesn't decrease table size? [closed]
We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone ...
2
votes
2answers
105 views
Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
I have a table that has a large number of rows in it.
The primary key (an auto-incrementing integer) is, by default, indexed.
While waiting for a row count to be returned I did an EXPLAIN in another ...
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
1answer
66 views
MySQL: Error reading communication packets
I get this warning in mysql,
[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)
I have been through few topics in google ...
0
votes
0answers
21 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
1
vote
1answer
36 views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
3
votes
2answers
5k views
How do I shrink the innodb file ibdata1 without dumping all databases?
InnoDB stores all tables in one big file ibdata1.
After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and ...
3
votes
1answer
32 views
Is it possible to select internal InnoDB columns?
I have read that InnoDB uses a few fields internally for all rows, such as the hidden columns DB_TRX_ID and DB_ROW_ID. Is there any easy way to see these values, such as within a select statement?
1
vote
1answer
33 views
High IO when switched to InnoDB
We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times.
The query is type of INSERT ... ON DUPLICATE KEY UPDATE
I don't know if the problem is ...
1
vote
1answer
56 views
mysql ibdata1 file corrupted or increased unusually
We are using MySQL 5.2 version all InnoDB tables.
I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days.
In last 6 months, it had taken only 6 GB though ...
1
vote
1answer
62 views
Linux 32b MySQL periodically crashing with signal 11 and OOM errors [closed]
I have a MySQL DB with mostly InnoDB tables that I'm fairly certain isn't tuned properly based on what's happening, but I don't have enough know how to pinpoint what to change. The server is a 8G 32b ...
1
vote
3answers
798 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: ...
1
vote
1answer
169 views
How to recover data from corrupted innoDB table from a crashed mySQL server?
Some background:
I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, ...
2
votes
3answers
4k views
mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
5
votes
5answers
3k views
MySQL said “InnoDB: Initializing buffer pool, size = 128.0M”, does it mean it is using 128MB RAM?
I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said
InnoDB: Initializing buffer pool, size = 128.0M
Does it mean even I am using in such a small size, ...
1
vote
1answer
62 views
Trying to allocate 3G for buffer pool in InnoDB fails
I'm trying to boost my InnoDB driver on my Windows 7 PC since each page request takes just about 1.7 seconds to load versus 0.002 seconds on my Ubuntu 12.10 workstation.
The setting I'm playing ...
0
votes
1answer
76 views
Large queries on MyISAM tables crashing MySQL
I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
1
vote
1answer
70 views
Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?
We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
0
votes
1answer
46 views
MySQL: replicating to a different table engine type
According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
4
votes
1answer
33 views
High amount of Read Misses and Pages To Be Flushed
I am running a mysql database backend for a Moodle installation, and after a few months performance really starts to suffer (Up to 30 seconds for some pages to load). Under investigation in the InnoDB ...
0
votes
2answers
53 views
VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?
For a log table, I have a custom message column.
It will never be updated.
It will be searched with LIKE conditions.
It will be capped at 1KB.
TEXT fields are slower than VARCHAR fields for ...
2
votes
1answer
39 views
Do I need to reenter old data after adding an index to a table?
I want to add index to my tables. Some of my tables already have couple thousand rows.
Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
1
vote
1answer
44 views
MySQL: OPTIMIZE after TRUNCATE?
Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
1
vote
1answer
35 views
extra steps after changing storage engine and adding index
Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.
I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
1answer
230 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 ...
1
vote
2answers
357 views
How to insert into junction table using triggers
Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.
Artist and Song are base tables and SongArtist is the ...
1
vote
1answer
346 views
when will Innodb support activation of triggers on cascade foreign key? [closed]
The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
Currently, cascaded foreign key actions do not activate triggers.
Any Idea when will this be implemented.
Is ...
