InnoDB is the MySQL ACID-compliant storage engine.
2
votes
2answers
90 views
Negative numbers in InnoDB Status
There are some negative numbers displayed in the InnoDB Engine Status output.
Anybody has encountered this or know why this happens ?
----------------------
BUFFER POOL AND MEMORY
...
0
votes
0answers
42 views
What would this non-'unique index with a unique search condition` query lock?
I am not sure about this part of the transaction manual
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
1
vote
1answer
34 views
Column order for better index
let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, ...
0
votes
1answer
57 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 ...
3
votes
2answers
115 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
3
votes
1answer
369 views
Very Slow MySQL Queries, even with indexes
I have a relatively large 4-deep relational data setup like this:
client_applications: (potentially 1,000's of records)
- ...
- account_id
- ...
0
votes
0answers
41 views
row locking or avoiding duplicate insertion on primarykey/otherfield combo
i have a table that stores additional data pertaining to files/images saved in files elsewhere, each entry can be associated with another type in the system,
for users there may be one or more ...
1
vote
2answers
395 views
Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…?
I manage a PHP/MySQL website :
max 500 simultaneous users / average of 4000 per hour in update/read
1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM)
InnoDB Storage engine on 100 tables for 1.4 Go ...
0
votes
0answers
87 views
table is full in innodb
One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached ...
2
votes
1answer
492 views
innodb_flush settings on Amazon RDS
Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent ...
1
vote
1answer
183 views
MySQL InnoDB logfiles resized - review mysql-err.log for potential problems
I restarted mysqld this morning, to increase the size of the innodb logs and increase the amount of memory allocated to the innodb buffer pool. (MySQL 5.1.53-Community on Ubuntu)
To increase the ...
4
votes
2answers
456 views
Amazon RDS I/O requests - 1 query = 1 billable I/O?
I have an InnoDB database that I'd like to migrate onto Amazon RDS.
My current implementation, hosted on my own server, shows roughly 8M queries per month.
The RDS site say that
I/O Rate $0.10 ...
1
vote
2answers
109 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
I'm going to be converting this table to InnoDB in a production environment.
The DB is on a cloud server with ...
0
votes
0answers
30 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
I'm going to be converting this table to InnoDB in a production environment.
The DB is on a cloud server with ...
0
votes
2answers
137 views
MySQL InnoDB locking on combined UPDATE-JOIN statements
on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors:
Statements writing to a table with an auto-increment column after selecting
from another table ...
0
votes
0answers
57 views
How to efficiently model this simple data structure?
catId(INT) | userId(INT) | catName(VARCHAR)
0 1 Cat1
1 1 Cat2
2 2 U2Cat1
3 2 ...
1
vote
1answer
230 views
InnoDB temporary table location
I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink ...
9
votes
3answers
11k 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
1answer
74 views
Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace
I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). ...
5
votes
2answers
222 views
Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search
Followings are what I need to do on my project:
1- If a search term is new and unique, then add the search term
2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
3
votes
1answer
80 views
Which MySQL table to use for read-only, bulk-loaded tables?
So I have a workload on 2 key tables in my application with the following characteristics:
READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
1
vote
1answer
142 views
Changing InnoDB data directory prevents MySQL service from starting
I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data.
I modified the "my.ini" adding the following line after "[mysqld]":
...
1
vote
2answers
782 views
How much memory do I need for innodb buffer pool?
I read here that
You need buffer pool a bit (say 10%) larger than your data (total size
of Innodb TableSpaces)
On the other hand I've read elswher that innodb_buffer_pool_size must be up to ...
1
vote
1answer
515 views
MariaDB and XtraDB/InnoDB plugins
In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the ...
1
vote
1answer
164 views
MySQL 5.5 - determining correct write[read]_io_threads on high-end system?
Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server.
Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write.
Currently we are running on the default ...
2
votes
1answer
294 views
MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!
So I wanted to test MySQL recovery from Innodb corruption.
file-per-table is enabled.
I have a few test db's on the MySQL instance.
I shutdown mysql. Manually edited/added random characters to an ...
3
votes
1answer
344 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
2
votes
1answer
586 views
MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?
When reading online about innodb_log_file_size, it says
The combined size of the log files must be less than 4GB
So what happens if it needs to be larger?
In books and online, it says to set ...
3
votes
2answers
122 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
2
votes
1answer
189 views
Restore InnoDB tables / database using only data files
I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine.
I have run DROP DATABASE dbName statement by mistake. I have no backup of that ...
5
votes
3answers
6k views
Any better way out of MySQL InnoDB log “in the future”?
I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
1
vote
1answer
263 views
What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?
2M rows inserted using LOAD DATA INFILE to innodb takes 7.5min, and profiling shows 99% of that time is in "System lock".
Does this tell me anything useful?
mysql> set profiling=1;
...
1
vote
1answer
276 views
Very poor insert performance MySQL / Percona Server
I'm running a Percona-Server instance off an Ubuntu server install. I'm using an application that needs to access this database, and its having VERY poor performance. Once the database has been ...
1
vote
1answer
178 views
MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)
We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).
We are loading changes to this table in a nightly batch job using LOAD ...
0
votes
0answers
219 views
InnoDB row level locks without SELECTs?
I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:
Each client runs the following ...
6
votes
1answer
2k views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
2
votes
2answers
169 views
Database space doesn't match ibdata1 size
I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.
In the MySql workbench, when I query for the databases used space, with this query
SELECT ...
4
votes
2answers
4k views
Why is MySQL is creating so many temporary tables on disk?
Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were ...
4
votes
2answers
566 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
1
vote
1answer
287 views
Is there a progress indicator for OPTIMIZE TABLE progress?
MySQL 5.1.4x (Windows) | Innodb
I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use
OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;
to reduce the ...
1
vote
1answer
151 views
Set up replication and migrate to InnoDB with low downtime
Current situation is this:
I have a large legacy DB using only MyISAM tables, the size of whole
DB is 4GiB, one of the tables contains over 10 million rows, so it is
pretty large.
The database is ...
3
votes
1answer
308 views
Mysql Server optimization tips needed! (InnoDB)
I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
0
votes
1answer
121 views
Backup and trimming data idea
Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.
We would like to maintain ...
50
votes
8answers
23k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
2
votes
1answer
321 views
mysqlcheck to optimize innodb tables — performance/scheduling
I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.
Is there a ...
0
votes
2answers
128 views
How long can Deadlock info hold in innodb status page?
When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp
------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31
Questions
Any idea how long Deadlock ...
1
vote
2answers
494 views
jdbc+mysql: “Deadlock found when trying to get lock; try restarting transaction”
After recovering from a disk-full problem, I started getting these errors in my logs:
java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at ...
4
votes
1answer
124 views
Which MySQL storage engine is the most similar to ARCHIVE when that is not available?
I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
5
votes
4answers
497 views
MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preface
Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
0
votes
0answers
29 views
“Disk leak” with xtrabackup intrementals/innodb
I have several Percona Mysql databases being backed up using Xtraback. Aside from the mysqldb these are all innodb databases. These are setup on a weekly full backup schedule with multiple daily ...
