InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

2
votes
1answer
202 views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` ...
0
votes
1answer
714 views

Putting a Select statement in a transaction

What is the difference between these 2 queries: start transaction; select * From orders Where id=1; UPDATE orders SET username="John" Where id=1; commit; And without transaction: select * From ...
1
vote
2answers
791 views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
3
votes
1answer
3k 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 ...
4
votes
3answers
4k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
-1
votes
1answer
133 views

Query mysql database through memcache

I have been trying to use memcache with MySQL 5.6 but I can not figure out how to do this. I have add an entry into innodb_memcache.containers so I can query the database through memcache. What I ...
2
votes
1answer
3k views

MySQL: Row size too large (> 8126)

Here's the issue. I have a table which has 300 columns in it, each up to 255 bytes (there IS a business justification for this). When I try it create it using VARCHAR(255), I go past the limit for ...
3
votes
1answer
119 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 ...
2
votes
1answer
220 views

List of variable defaults for InnoDB engine

I would like to list the defaults for the global variables that are innodb specific. The problem is that the following commands doesn't list any innodb variables: /usr/sbin/mysqld --no-defaults ...
2
votes
0answers
174 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
1
vote
1answer
80 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
3
votes
1answer
522 views

What is log sequence number ? How it is used in MySQL?

Can anybody give an example or scenario of log sequence number(LSN) used in MySQL
0
votes
1answer
244 views

Information about Disk Storage MySQL

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate : used and free size of ...
1
vote
1answer
155 views

I have accidently deleted ibdata1 on an ubuntu server

I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it? the mysql service has restarted since the deletion.
0
votes
0answers
466 views

MySQL always crash on start, ibdata1 bigger than 4GB

I'm having a problem with MySQL when testing my production data. Whenever I started MySQL (mysqld), it always crash. My suspect is on my InnoDB data file (ibdata1) since the size is already bigger ...
1
vote
1answer
184 views

Implementing a Record Keeper/Rollback System for a MySQL Database

What's the best way to create a record keeping table for all actions in my database? I have a number of tables inside one database and I want to create another that records all movements in every ...
1
vote
1answer
243 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
0
votes
3answers
347 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 ...
0
votes
1answer
372 views

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality?

I've got a complex multi-join query where the optimizer won't pick an index on a TIMESTAMP column even though it results in a smaller index/table scan. If I force the index on the TIMESTAMP column, ...
1
vote
2answers
5k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
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, ...
0
votes
1answer
2k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
-1
votes
2answers
2k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
0
votes
0answers
31 views

How to add column/alter structure to big table in MySQL [duplicate]

'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
2
votes
2answers
2k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
2
votes
1answer
154 views

How big can innodb_doublewrite_file grow?

When I use the enable innodb_doublewrite_file ... How big can this file grow ? How can I calculate the optimal disk size, if it would be used only for this file?
0
votes
1answer
343 views

Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
0
votes
0answers
48 views

Federate engine to help overcome distributed transaction?

Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we ...
0
votes
2answers
188 views

how important are mysql's innodb logs?

I'm basically concerned about the following two settings, these are my default settings : innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 I haven't changed them for 2 reasons : one, ...
2
votes
1answer
283 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
1
vote
1answer
164 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
0
votes
3answers
297 views

Convert the table PROCESSLIST from InnoDB to MYISAM

I use MySQL. My database's name is "db", and I have a table called "Test". I want recover the last update, so I used this query: SELECT update_time FROM information_schema.tables WHERE ...
2
votes
1answer
5k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
0
votes
1answer
80 views

MySQL HDD activity with empty process list

What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app. Thanx.
2
votes
1answer
74 views

Which hardware you suggest?

I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be ...
0
votes
1answer
1k views

InnoDB tables inaccessible after reboot

After reboot each time, I can't see my InnoDB table. However, when use command mode I can see my tables, but cannot access to it. mysql> show tables; +----------------------+ | ...
1
vote
2answers
262 views

mysql innodb_flush_log_at_trx_commit clarification

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years) So ...
3
votes
1answer
292 views

mysqldump freezing on a specific table

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command: mysqldump -uxx -pxx ...
1
vote
1answer
580 views

MySQL - Export two columns from large table without causing a lock

I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I ...
0
votes
1answer
2k views

MySQL dynamically optimize innodb tables without “file per table” setting

We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
0
votes
1answer
94 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
0
votes
2answers
227 views

what happens when InnoDB logspace is too small

I've just seen several mysql errors in my log: InnoDB: ERROR: the age of the last checkpoint is X InnoDB: which exceeds the log group capacity Y I understand, that I do have to increase the ...
1
vote
1answer
319 views

Why InnoDB ALTER TABLE is so slow? [closed]

The machine has a 4-core hyper-threaded Intel i7 670 @ 2.8GHz, 16G RAM, 8G of which is InnoDB buffer pool. The database resides on Intel SSD disk. DBMS is MariaDB 5.5.30 x64 running under Windows ...
0
votes
0answers
65 views

InnoDB data back in 3 months after OS shutdown

I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash ...
1
vote
1answer
533 views

Sorting Result state taking very long time

I am using a MySQL 5.1.69-log DB for analysis purpose. I've designed my database in a star schema. All tables are using InnoDB. My innodb_buffer_pool_size=2Gb This is the query I am running : ...
1
vote
1answer
125 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
1
vote
2answers
783 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
2answers
277 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
3
votes
1answer
4k views

How to solve “The table … is full” with “innodb_file_per_table”?

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
1
vote
1answer
750 views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...