InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
2answers
199 views

Are MySQL transactions under-utilised?

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related. Scenario : Suppose I have a series of queries which creates a user ...
1
vote
1answer
89 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
0
votes
2answers
83 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 ...
2
votes
0answers
113 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
1
vote
1answer
98 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 ...
2
votes
2answers
1k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
2
votes
1answer
7k views

MySQL: InnoDB keeps crashing - how to recover?

# free -m total used free shared buffers cached Mem: 48289 35288 13000 0 347 30399 -/+ buffers/cache: 4541 43747 ...
1
vote
2answers
1k views

The best MySQL Settings for 32GB RAM on a Dedicated Server

I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm. I have the following configuration on my MySQL: [client] port = 3306 socket = ...
0
votes
0answers
43 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 ...
4
votes
4answers
2k 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 ...
1
vote
1answer
52 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
574 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 ...
15
votes
3answers
24k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
3
votes
1answer
36 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 ...
1
vote
3answers
209 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 ...
0
votes
2answers
75 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
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 = ...
1
vote
2answers
94 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
2answers
803 views

Percona Xtradb Cluster : How to speed up insert?

I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 ...
1
vote
3answers
696 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
2
votes
1answer
294 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
187 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
81 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
61 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, ...
11
votes
3answers
5k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
0
votes
1answer
52 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 ...
1
vote
1answer
438 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 ?
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
3answers
335 views

MySQL InnoDB Sorting Issue

I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation: "All indexes other than the clustered ...
-1
votes
1answer
17 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 ...
0
votes
1answer
144 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 ...
0
votes
1answer
4k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
1
vote
1answer
618 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
57 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 ...
20
votes
3answers
24k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
1
vote
2answers
429 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
1answer
103 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
178 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 ...
2
votes
1answer
101 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 ...
1
vote
1answer
33 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
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 ...
2
votes
1answer
14k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
1
vote
1answer
641 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
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 ...
1
vote
1answer
100 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 ...
3
votes
4answers
245 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 ...