InnoDB is the MySQL ACID-compliant storage engine.
0
votes
2answers
168 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
60 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
281 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
88 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
225 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
87 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
159 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
916 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
618 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
207 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
319 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
401 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
852 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 ...
4
votes
2answers
131 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
192 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
7k 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
326 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
336 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
199 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
236 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
185 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
588 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
356 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
160 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
364 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
122 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 ...
51
votes
8answers
24k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
2
votes
1answer
350 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
146 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
551 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
130 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
530 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 ...
1
vote
1answer
94 views
Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace
I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
1
vote
2answers
181 views
MySQL Enterprise Backup error: Log scan was only able to reach xxxx
I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.
I get this error:
mysqlbackup: ERROR: Log scan was only able to reach ...
0
votes
1answer
207 views
How to UPDATE a column to SET previous row value to NULL cells?
I have a table as
id value
1 music
2
3 movie
4
5
6 book
7
8
Some values are missing, and I want to assign the value of previous row to produce
id value
1 music
2 music
3 movie
4 ...
3
votes
2answers
245 views
How to split a string to and get info from another table in mysql
I have a system to assign score for each sentence stored in a column:
TABLE 1: word_scores
word score
this 3
is 4
a 5
test 1
another 0
sentence 8
TABLE 2: ...
2
votes
2answers
148 views
Bin logs with high i/o
I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload.
So, my knowledge of MySQL is minimal.
Few days ago I start use MySQL with a basic configuration as back-end for a ...
3
votes
1answer
464 views
Deleting MySQL table with pending transactions
Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?
What happened:
I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
1
vote
1answer
76 views
mysql freeze/crash on JOIN
here is query:
SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
nw.status, src.title as src_title, src.keyword as src_keyword,
src.url as src_url, src.icon as ...
3
votes
3answers
296 views
InnoDB insertion faster
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
6
votes
1answer
228 views
LOAD DATA INFILE blocks on concurrent use against MyISAM tables
I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
5
votes
3answers
6k 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 ...
2
votes
2answers
690 views
Dynamic change to innodb_flush_log_at_trx_commit
This is related to this question. It does help to get better performance for InnoDB tables.
According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change ...
1
vote
0answers
738 views
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ?
updated to answer the 3 questions:
mysql> show variables like 'version%';
...
3
votes
2answers
138 views
Tablestructure for fast inserts/deletes with foreign keys
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:
id | competitors | some infos about competitor | ...
5
votes
1answer
717 views
MySQL INNODB corruption after server crash during concurrent truncate command
My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
