InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
160 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
162 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
0answers
105 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 ...
0
votes
0answers
85 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
2answers
1k 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 ...
0
votes
0answers
55 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
2k 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 ...
1
vote
1answer
320 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 ...
1
vote
1answer
434 views

How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
0
votes
2answers
208 views

Is there slowdown inserting into an InnoDB table that has no index set?

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such. Those tables only contain a few thousand rows. Would it be faster to INSERT data ...
2
votes
2answers
2k views

Cannot Utilize Maximum CPU and Memory Usage for MySQL

Good day. I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem. I have a single stored procedure ...
3
votes
1answer
114 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
180 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
155 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 ...
5
votes
1answer
1k 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 ...
1
vote
1answer
2k 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, ...
1
vote
1answer
535 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
969 views

MySQL database drop insanely slow

I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server. I installed it under my user home directory like I'm used to doing. But this time the queries are extremely slow to ...
2
votes
1answer
256 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 ...
3
votes
2answers
728 views

SHOW TABLE STATUS very slow on InnoDB

Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ ...
0
votes
2answers
384 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 ...
0
votes
1answer
191 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 ...
2
votes
1answer
51 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
192 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
76 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 ...
4
votes
1answer
119 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 ...
1
vote
1answer
56 views

mySql Relationships / Index - Checking for duplicates

I have this setup on innoDB: TblUser user details TblConnection User Connection TblConnection also has these indexes: User, Connection where User is the foreign key of TblUser.id and another ...
1
vote
1answer
92 views

Changing TEXT to VARCHAR

In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text. Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
2
votes
1answer
119 views

When and why can this kind of deadlock occur?

------------------------ LATEST DETECTED DEADLOCK ------------------------ 130409 0:40:58 *** (1) TRANSACTION: TRANSACTION 3D61D41F, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT ...
4
votes
1answer
3k views

increasing mysql table open cache?

I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"? Thanks!
1
vote
1answer
63 views

If MySQL isn't caching, then what is?

I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case. mysql> show variables like 'query_cache_size'; -------------- show variables like ...
2
votes
1answer
223 views

select count(*) in mysql 5.5 innodb— rewrite advice?

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... select count(*) from mails3 join questions using (question_id) where ...
1
vote
2answers
2k views

MySQL performance impact of increasing innodb_buffer_pool_size

I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB. My queries are responding well now i.e under mili seconds. ...
5
votes
1answer
252 views

“Site Offline” MySQL server failing to start and stop

I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the ...
1
vote
1answer
319 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
224 views

Is there a query to set a table's engine to innodb if it's not already?

I can set a (MyISAM) table's engine to InnoDB using the query: alter table tablename engine=InnoDB which takes some time to complete for large tables. It appears though that if I run the same query ...
1
vote
2answers
1k views

ibdata1 grows exponentially when innodb_file_per_table is configured

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month). Is My my.cnf file is ...
2
votes
1answer
711 views

Innodb Slow queries since convert from MyISAM

a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
0
votes
0answers
236 views

UPDATE / SELECT incredibly slow in MySQL InnoDB

I have 1 table with 2.5 billion records(2.5 Cr.) and I am trying to get count of records based on indexed column. it takes 115 seconds to execute same. Query : Select count(1) from table1 where ...
1
vote
1answer
185 views

For a InnoDB only DB, which of these elements can be removed?

So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming ...
2
votes
1answer
252 views

Drupal 7 Uses InnoDB, so do I need other configs?

If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff ...
2
votes
2answers
135 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 ...
0
votes
0answers
56 views

InnoDB best index practises

I have an InnoDB table with around ~3.7m rows in it. Here are my current indexes: ...
1
vote
1answer
150 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 ...
0
votes
1answer
176 views

Is using the alpha version of Percona 5.6 safe? [closed]

I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
0
votes
1answer
129 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 ...
2
votes
1answer
1k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
1
vote
1answer
47 views

INNODB real-time locks?

How can I view real-time locks in innodb when information_schema.innodb_locks is not available?
2
votes
1answer
359 views

Minimize locking on UPDATE with multiple WHERE columns

I have a problematic query that's causing a lock timeout: UPDATE <some_table> SET row1=<some value> WHERE row1 IS NULL AND row2 > <some value> ORDER BY row2 LIMIT 100 ...
4
votes
1answer
939 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 ...