InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
2answers
88 views

Can CPU cores be separated for MySQL?

Is it possible to separate CPU cores in Mysql? I understand it is possible for Memory as we can tune it in innodb_buffer_pool_size or innodb_additional_mem_pool_size. How about CPU cores in ...
0
votes
1answer
23 views

what is the best configuration for innodb_flush_log_at_trx_commit?

There are 3 different configuration is available for innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit = 0 innodb_flush_log_at_trx_commit = 1 innodb_flush_log_at_trx_commit = 2 Which ...
1
vote
1answer
102 views

MySQL 5.1: Upgrade innodb plugin

My application is running on MySQL 5.1.58 community edition. As I studied on SO websites that After acquisition by Oracle, new InnoDB plugin has been released and MySQL 5.1 with InnoDB plugin is the ...
0
votes
0answers
44 views

MariaDB : Larger innodb_buffer_pool_size causing SQL updates to be slower

Has anyone here encountered such scenario before where and update statement took longer time to run when innodb_buffer_pool_size is bigger. I've run a few tests using the same statements on around ...
0
votes
1answer
37 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
1
vote
1answer
55 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
2
votes
2answers
33 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
1
vote
1answer
70 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
1
vote
1answer
93 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
3
votes
2answers
175 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
3
votes
1answer
40 views

Is the innodb_lazy_drop_table problem solved?

I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to ...
0
votes
2answers
77 views

Recovering mysql innodb table from table.ibd backup file

Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server). Now for a scenario, where some how a table has been ...
1
vote
3answers
241 views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
1
vote
2answers
68 views

Transaction basics. What is the result of 2 transactions running concurrently?

I have a rather basic question on transactions. Assume a table Account with a column amount. Now we have the following code: BEGIN $amount = SELECT amount FROM ACCOUNT WHERE id = 123; $amount = ...
0
votes
1answer
74 views

Why is setting open_files_limit making MySQL slow?

We noticed that adding open_files_limit in /etc/my.cnf is making server slow. Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?
0
votes
1answer
173 views

ib_logfile0 and ib_logfile1 got deleted

After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed: ...
2
votes
1answer
314 views

Why does auto increment jumps by more than the number of rows inserted?

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure: INSERT INTO Bids ...
1
vote
3answers
194 views

What does “InnoDB stores its tables and indexes in a tablespace” mean? [duplicate]

Was reading the mysql engine innoDB tutorial, I came across this sentence "InnoDB stores its tables and indexes in a tablespace" Can any body explain me this what does it means. Thank you :)
1
vote
0answers
89 views

MariaDB Galera Cluster

I have two mariadb servers in cluster that is working galera and xtradb. it is my primary server configuration: [mysqld] datadir=/var/lib/mysql query_cache_size=0 binlog_format=ROW ...
0
votes
1answer
63 views

Is there a table size above which SELECT performance degrades significantly?

In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, ...
1
vote
2answers
95 views

What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

We have few thousand small databases with total .ibd files of about 800000. We noticed that MySQL takes couple of minutes to stop but about two hours to start. For e.g. while MySQL is starting and ...
0
votes
1answer
54 views

The most efficient method to obtain the total entries into a mysql table

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but ...
0
votes
1answer
274 views

how to recover .ibd and .frm files in mysql

so i am developing java application with mysql 5.6.13, few days before i purchased new laptop, from previous laptop i forgot to create dump of application instead i copied folder of my database(.ibd ...
0
votes
1answer
29 views

Check whether MySQL Innodb Rows are compressed

After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?
0
votes
1answer
155 views

Error 1005: Can't create table can't find where the problem is

I have created a EER model using MySQL Workbench (latest release). Apparently all is fine but when I try to do a FORWARD ENGINEER I get the common Error 1005: Can't create table. As suggested in this ...
-1
votes
1answer
19 views

Mysql InnoDB exsited but can't show it

I have a InnoDB table A , While I type "show tables" , I can't see it. Even I type "drop table A" , it responses "table doesn't exist" . While I create a table B which refers from A , table B can ...
1
vote
1answer
59 views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
1
vote
1answer
687 views

Compressing Base64 Encoded Image in Database Field. Table Size too Large

Database Info Database Type - Percona MySQL. Table engine - InnoDB. I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 ...
1
vote
1answer
108 views

Error while importing sql - Specified key was too long; max key length is 1000 bytes

I am trying to import an sql file which shows me the following error: Error SQL query: -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE ...
1
vote
1answer
191 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
0
votes
0answers
22 views

row locking with innodb [duplicate]

What is the most efficient way to lock a specific row for within a mysql transaction. I am currently using for update using select is there another method that im unaware of? if not are mysql locks ...
1
vote
1answer
36 views

mysql multiple database prioritize innodb buffer pool size

With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?
0
votes
0answers
23 views

Temporary table created on disk, with 14mb of database

i have only one database (on one machine) with size 14 mb for temporary table i have this configration max_heap_table_size= 1000M tmp_table_size= 1000M sort_buffer_size=4M join_buffer_size=4M i ...
2
votes
1answer
104 views

row locking within ACID transaction innodb

I have a financial transaction that looks something like this (pseudo): begin select record for update update record(same record as selected) insert another record into another table ...
0
votes
2answers
117 views

MySQL High Availability for InnoDB

I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine. I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over ...
1
vote
1answer
104 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
1
vote
0answers
46 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
0
votes
1answer
168 views

Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a ...
1
vote
1answer
97 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
1
vote
1answer
436 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
0
votes
1answer
87 views

I have delete database from mysql but storage is not freed

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage? I have a mysql database that the total ...
5
votes
1answer
1k views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
212 views

Copying MySQL tables, InnoDB tables not recognized

I have to set up a database copy on my local computer. The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI). I created the database by creating a folder in ...
1
vote
1answer
56 views

How to calculate the space I need for the indexes?

CREATE TABLE info( home CHAR(10) CHARACTER SET latin1 NOT NULL, id TINYINT UNSIGNED NOT NULL, galaxy TINYINT UNSIGNED NOT NULL, galaxy_pos INT UNSIGNED NOT NULL, events TEXT NOT NULL, UNIQUE ...
3
votes
4answers
254 views

innodb DEPENDENT SUBQUERY very slow

Here is some innodb table. It insert,update,query very frequently. tn is product id col is product sub id tindex is product description.(store by each word) date is product add date stored by ...
0
votes
0answers
183 views

MySQL Innodb Concurrent Inserts and Selects - can't use multiple threads on FreeBSD

We have an application that needs to be dumping rows into some tables at a much faster rate than it currently is. It is a dual processor xeon DB server with 48gb of ram and a 4 TB disk RAID 10 (for a ...
0
votes
1answer
118 views

Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
0
votes
0answers
54 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
1answer
84 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
2
votes
0answers
43 views

Count how many locks required for a MySQL (InnoDB) query

I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to ...