InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
121 views

innodb .frm files move from one server to another fails

I have a weird issue with an InnoDB table restore. Can you please help me to recover the InnoDB database? In MyISAM it works. we have the database(/mnt/OLD/var/lib/mysql/ims) with 300 Innodb ...
1
vote
1answer
59 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
0
votes
2answers
70 views

Where/how does innodb store tables in files?

I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? ...
1
vote
1answer
55 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
0
votes
2answers
3k views

How to set Innodb Buffer Pool Size [duplicate]

I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user. But no matter what I do it won't change. I have added a value of ...
0
votes
1answer
116 views

How to copy a table in a production server without WRITE lock and without writing to a file

I'm trying to copy a table in a production table. When I'm performing on InnoDB tables: INSERT INTO new_table (SELECT * FROM table) I have a write lock on table. Is it possible to avoid this lock? ...
1
vote
2answers
77 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
0answers
51 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
3
votes
1answer
2k views

InnoDB log sequence number is in the future

130711 23:03:40 InnoDB: Error: page 23 log sequence number 3336825872455 InnoDB: is in the future! Current system log sequence number 235089. InnoDB: Your database may be corrupt or you may have ...
0
votes
0answers
49 views

innodb error, and failt to connect

i'm have a innodb database, and it is corrupt, with mysql shell, when i use leomaxco_vi i'm disconnected: ` show engines; No connection. Trying to reconnect... Connection id: 5 ...
0
votes
0answers
89 views

Need help with my.cnf - Server slows down everyday at the same time

I'm running into a few issues with our new database server. It is a HP G8 with 2 INTEL XEON E5-2650 processors and 32GB of ram. This server is dedicated as a MySQL server (5.1.69) for our intranet ...
2
votes
3answers
2k views

Mysql innoDB write operations are extremely slow

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
0answers
84 views

How does MySQL handle an index if its full size is too large for memory?

Let's assume this (totally artifical) query: select id from T where id%3=0 order by id desc; The index of T (InnoDB) is according to information_schema.tables sized 5 GB and T itself is 3 GB but ...
0
votes
0answers
34 views

Is there any way to set server-wide and schema-wide default row formats for InnoDB tables in MySQL?

Is there a way to set server-wide and database-wide default row formats? Currently it seems that I must specify ROW_FORMAT in CREATE TABLE or ALTER TABLE statements. Am I reading the documentation ...
0
votes
0answers
74 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
1
vote
0answers
239 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
0
votes
1answer
606 views

High average load due to the High mysql cpu usage

i've got 2 warnings about my average load being high in the past week and apparently it has to do something with the mysql my daily visit has been increased about 10-20 thousand (total of 180-150 ...
0
votes
1answer
69 views

MySQL High I/O. Wrong configuration?

Since I switched from MyISAM to InnoDB I have a high I/O. The writes is a flat line, it seems there is a limit for them. This is my current mysql configuration: [mysqld] bind-address = 127.0.0.1 ...
4
votes
1answer
2k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
0
votes
2answers
131 views

Duplicating database on the same server without data loss

I want to duplicate an innodb database on my mysql server for testing another version of the software. Using tools for example phpmyadmin reduce the database around 20 MB and a "lot" of rows are ...
0
votes
0answers
154 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
3
votes
1answer
174 views

Finding swap causes of MySQL

In my centos 6.3 server I have a MySQL 5.5.33 database. It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows. I refactored my schemas and added indexes for my slow logs. My average query ...
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. ...
4
votes
3answers
877 views

Should we need to use Barriers on a production database (MySQL/InnoDB)?

Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended? e.g mount -o barrier=1 /dev/sda /mntpnt Reference: ...
4
votes
2answers
4k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
0
votes
0answers
97 views

Is it possible to optimize a range query so an explain statement would state “using index” instead of “using index condition”?

I have a very large table where specific queries made by my application are timing out. The table looks something like this: LargeTable ------------ Id [BIGINT(20)] UtcTimestamp [BIGINT(20)] Pivot ...
0
votes
0answers
161 views

MYSQL LOAD DATA INFILE taking a long time

I have a MYSQL DB running on a raspberry pi. Now under normal circumstances MYSQL actually runs slightly quicker than it did on my much more powerful desktop. However I am trying to insert 60 million ...
0
votes
0answers
48 views

Lock timeouts on a table with two keys, where only one connection writes for a given value of the first key at a time

I have a table user_rollup_message_list that is keyed by (user_id, message_reference). It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, ...
5
votes
3answers
10k 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 ...
0
votes
0answers
28 views

Accessing Database from Slave server

I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost. ...
2
votes
1answer
46 views

Is there any way to use different InnoDB settings for different databases on the same server?

From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used ...
1
vote
2answers
256 views

MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?

I have several databases all in INNODB, 50GB are BLOB columns, only 700MB are not BLOB data. Tools as mysqltuner ask me to set innodb_buffer_pool_size = 51GB. Does MySQL store the BLOB in RAM? If ...
0
votes
1answer
36 views

MySQL innodb database creation…why do this? [closed]

I am new to administering MySQL databases but the company I work for has a number of servers set up with a physical "innoDB" schema in MySQL. Not sure why someone would do this and the schemas are ...
1
vote
1answer
321 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 % - ...
5
votes
1answer
384 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
3
votes
1answer
102 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
3
votes
3answers
1k 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 ...
1
vote
2answers
51 views

Indexing a reference column

Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server ...
2
votes
1answer
127 views

Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course. What are the advantages and disadvantages of either ...
0
votes
1answer
622 views

Why should I use InnoDB and MySql instead of XtraDB and MariaDB? [closed]

Why should I use InnoDB and MySql instead of XtraDB and MariaDB, except that InnoDB and Mysql happen to be installed by default on my servers? Why isn't MariaDB and XtraDB installed by default ...
7
votes
3answers
8k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
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
84 views

How to UPDATE a table for the level of parents in a column?

I have a table with internal parents as id parent parent_level 1 NULL 1 2 1 2 3 1 2 4 3 3 where parent has a FK to id. How can I update the table ...
2
votes
0answers
477 views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
3
votes
1answer
710 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 ...
0
votes
0answers
135 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
1
vote
1answer
44 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
1
vote
1answer
62 views

Do regular ALTERs weaken the performance?

In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the ...
1
vote
1answer
387 views

MySQL: sysbench test - InnoDB vs Memory tables

I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz) I've created two tables, first one is using InnoDB storage engine ...
2
votes
0answers
92 views

Sleeping connections

I have a dedicated MySQL server(8GB) with following configuration. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql lower_case_table_names = 1 ...