InnoDB is the MySQL ACID-compliant storage engine.
1
vote
0answers
15 views
Storing history of full/partial tables in MySQL
I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.
I wonder if it's efficient to store a lot of rows ...
1
vote
0answers
18 views
Allocating 8GB memory to MySQL on a 64bit system
Specs -
MySQL 5.0.x , Redhat 5.9 , Physical memory - 16GB
I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error -
...
0
votes
1answer
43 views
Large INSERTs performance optimization
I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are send ...
0
votes
1answer
27 views
TokuDB/InnoDB Question
What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.
That's okay to answer of one engine.
TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
...
1
vote
1answer
110 views
What is causing the Periodic High Disk IO writes on MySQL Apache Server
I've already read all the posts related to this subject but couldn't find any answers. Here are my settings. Server has 4GB of RAM but top shows only 2.2GB are used. I've set the ...
0
votes
0answers
34 views
How much RAM for a large database on a dedicated MySQL InnoDB server?
Hereunder my hardware config:
CPU: 1x Intel Xeon X3470 Quad core @ 2.93GHz
RAM: 4x 8GB DDR3
Disks: 2 x SATA 320GB for the system & 4 x SAS 300GB for the DB
I've upgraded from 12Gb to 32Gb and ...
0
votes
2answers
43 views
SHOW TABLE STATUS very slow on InnoDB
Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ ...
0
votes
0answers
5 views
InnoDB Tablespace critical error in great need of a fix
Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG
Question: Could the error shown on the screenshot be the reason why my site is very slow?
0
votes
1answer
23 views
Drop partition not freeing disk space
I am running MySql innodb with innodb_file_per_table.
As a daily script, I create a new partition for a set of tables, as well as drop yesterday's partitions. I have noticed that through the df ...
0
votes
2answers
30 views
InnoDB Tablespace critical error in great need of a fix
Link to screenshot :
http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG
(The value reported is outside the allowed range - Byte free, free, gauge, warn, critic)
Question: ...
3
votes
3answers
3k views
How to use insert delay with the InnoDB engine and use less connection for insert statements?
I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
0
votes
2answers
122 views
MySQL backup InnoDB
I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
0
votes
2answers
261 views
How to increase fast backup and restore of 500GB database using mysqldump?
There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.
Backup and restore ...
0
votes
1answer
93 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
1answer
20 views
InnoDB Failure of some kind
I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked.
InnoDB is listed under SHOW ENGINES;
Any idea ...
0
votes
0answers
394 views
1286 - Unknown storage engine 'InnoDB'
I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
0
votes
1answer
24 views
Ubnutu/MySQL - Error “Unable to locate package innotop”
I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29.
Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop"
root@server1:~# apt-get install ...
2
votes
1answer
154 views
How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB
I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
0
votes
1answer
114 views
How I prevent deadlock occurrence in my application?
I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am ...
1
vote
2answers
194 views
MySQL-5.5 InnoDB memory issue
Version in use is mysql-5.5.24.
In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version.
I am ...
1
vote
1answer
56 views
Configuring MySQL (my.cnf) for MyISAM and InnoDB
I want to convert some (not all) the MyISAM tables to InnoDB.
Here is my current my.cnf: pastebin.com/FW8YXtLQ
What would be the best configuration? (need to add InnoDB settings, byecause there are ...
1
vote
2answers
343 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
1answer
165 views
What is the actual difference between innodb_autoinc_lock_mode 0,1,2
I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.
I see in "0" option, it locks the entire table and does the ...
1
vote
1answer
181 views
Queries getting stuck on very simple COUNT queries
process list:
| 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | ...
1
vote
1answer
35 views
MySQL - how can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
0
votes
1answer
58 views
Lock wait time out exceed restart transaction
I had got error on the lock wait time out so below I got 3 samples taken. One I took before the increase innodb_lock_wait_timeout to 120. So is there anything else I must tweak based on the logs ...
1
vote
1answer
149 views
increasing mysql table open cache?
I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?
Thanks!
1
vote
1answer
85 views
select count(*) in mysql 5.5 innodb— rewrite advice?
I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...
select count(*)
from mails3
join questions using (question_id)
where ...
3
votes
1answer
57 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
2
votes
4answers
186 views
TokuDB not much faster than MySQL
I have converted a MySQL database with 80.000.000 rows to TokuDB.
Now when I run:
select count(id) from xxx where active=1
it takes 90% of the time of the normal MySQL request.
What do I have to ...
2
votes
2answers
48 views
Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?
Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ?
For example :
/etc/init.d/mysql stop
rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql
1
vote
2answers
111 views
MySQL information_schema doesn't update
I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.
After I run the query delete from abc.test, I want ...
1
vote
1answer
36 views
Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?
Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that ...
0
votes
0answers
38 views
is it good to use combined primary key for MySQL InnoDB clustered index?
I'm trying to build a aggregated reader website of multiple forums.
Because most queries are likely to be within same time periods for written_time column,
I'm thinking about taking advantage of ...
3
votes
4answers
725 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 ...
3
votes
3answers
85 views
Issue after moving the ib_logfile1 and ib_logfile0 files
I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
0
votes
2answers
177 views
How to tune mysql to be inmemory-like
I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).
Is it possible to force ...
1
vote
1answer
168 views
How to run a cold backup with Linux/tar without shutting down MySQL slave?
I run the following before tar-ing up the data directory:
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
0
votes
3answers
48 views
Using MySQL InnoDB as an Archive
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
0
votes
0answers
7 views
ibdata deleted and innodb error [duplicate]
By accident I have deleted ibdata1 file in MySQL directory because the MySQL wasn't functioning correctly so after reading so many forums I found a post asking to delete the 3 files in MySQL directory ...
0
votes
0answers
40 views
InnoDB errors after having deleted “ibdata1”
By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly.
After reading so many forums, I found a post that recommended deleting the 3 files ...
1
vote
1answer
79 views
Mysql DB server hits 400% CPU
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.
- load average 40 to 50
- CPU % - 400%
- idle % - 45%
- wait % - ...
1
vote
1answer
235 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
40 views
MySQL InnoDB does not return the last row on select with DESC order
We have a table scheme like following:
CREATE TABLE IF NOT EXISTS `offers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`campaign_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`price` double ...
2
votes
1answer
157 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
3
votes
1answer
194 views
MySQL: Can't find .ibd files after turning on innodb_file_per_table
Question:
This a question about MySQL. Let me give the specifics of my setup:
Mac OSX 10.6.8
MySQL 5.5.28
Workbench 5.2.44 for all administration
InnoDB engine exclusively
In order to manage ...
5
votes
1answer
260 views
MySQL hogging memory
An installation of MySQL 5.6.10 on a virtualized Ubuntu 12.04 is exhibiting massive memory hogging. The mysqld process claims the entire available memory within a couple of hours of uptime and forces ...
14
votes
3answers
15k views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
2
votes
1answer
207 views
MySQL - Installing InnoDB Plugin
I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61.
I want to upgrade the master server to a stronger machine with the following:
2 processors - 6 cores each
48GB of RAM
To ...
0
votes
0answers
28 views
MM Replication and Zabbix in slave, Error code 1032
I have multi-master replication and using Zabbix for server monitoring. Since the replication is row based, I get this error in slave everytime which also breaks my replication. The error I get is:
...
