InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

3
votes
2answers
1k 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
773 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
387 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
60 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
369 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
87 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
213 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
60 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
116 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
454 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
8k 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
74 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
327 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 ...
3
votes
2answers
5k 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
401 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
444 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
380 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
3k 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
1k 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
292 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
231 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
428 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 ...
3
votes
2answers
204 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
59 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
223 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
205 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
189 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 ...
3
votes
1answer
3k 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
56 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
1k 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 ...
3
votes
1answer
2k 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 ...
2
votes
1answer
512 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 ...
0
votes
0answers
669 views

How long would a delete take for 50 million records in MySQL/InnoDB?

How long would a delete take for 50 million records in MySQL with the InnoDB engine? Server Config: MySQL Version 5.0.77 CentOS 5.4 Final 64 bit 16GB RAM RAID 5 Processor Model : Intel(R) Xeon(R) ...
3
votes
1answer
553 views

Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

I learned that innodb_buffer_pool_size makes innodb behave like an in memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or ...
0
votes
2answers
623 views

Bypass MyISAM table lock

I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
0
votes
2answers
13k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
4
votes
1answer
2k views

How I prevent deadlock occurrence in my application?

I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database. All the tables in the database have innodb engine. I also created indexes on each tables. Now I am ...
4
votes
2answers
6k views

Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

My Hardware Spec Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 ...
3
votes
4answers
896 views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
0
votes
2answers
543 views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
0
votes
1answer
347 views

Integer comparison in MySQL. does it cause lots of overhead?

I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to ...
3
votes
2answers
2k views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
0
votes
2answers
360 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 ...
2
votes
1answer
404 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
5
votes
1answer
6k views

Error when dropping InnoDB database

I am trying to drop a database in mysql, but every time I get this error ERROR 2013 (HY000): Lost connection to MySQL server during query even logged in as root. Originally, I had a corrupted ...
3
votes
1answer
100 views

Process sessions foreach minute

I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL). It's for online radio. A listener connects to the stream, and a row is created. Once ...
1
vote
2answers
57 views

Separated databases, separeted datas

We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB). I ...
0
votes
2answers
1k 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 ...
3
votes
2answers
945 views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
2
votes
1answer
552 views

Why is InnoDB auto_increment column skipping over large blocks of unused values?

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time. I turned on query ...