InnoDB is the MySQL ACID-compliant storage engine.
16
votes
3answers
17k 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 ...
51
votes
8answers
24k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
23
votes
4answers
24k views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
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 ...
4
votes
2answers
2k views
Will these two queries result in a deadlock if executed in sequence?
This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or ...
6
votes
2answers
2k views
Trouble deciphering a deadlock in an innodb status log
We are accessing MySQL from the Microsoft ADO.NET connector.
Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
2
votes
3answers
3k views
Is innodb_file_per_table advisable?
We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave ...
43
votes
6answers
24k views
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
5
votes
1answer
2k views
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction".
I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
8
votes
3answers
18k views
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
10
votes
3answers
9k views
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
8
votes
1answer
3k views
Clarification on MySQL innodb_flush_method variable
Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
3
votes
2answers
2k views
Query runs a long time in some newer MySQL versions
I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
17
votes
5answers
13k views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
4
votes
2answers
3k views
Using multiple cores for single MySQL queries on Debian
I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four.
I am doing expensive joins on large tables, ...
2
votes
1answer
6k 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 ...
4
votes
2answers
6k views
How do you remove fragmentation from InnoDB tables?
I have a Database having number of tables.
I want to delete some records from the tables say the no of records are more than 20K or 50K.
The All the Tables are InnoDB. And file_per_table is off.
...
1
vote
1answer
647 views
MySQL InnoDB data file directly on a disk slice (fixed-size)?
It seems that ibdata1 is like a disk slice. In Solaris (UNIX) UFS filesystems (the common one before ZFS), one would separate their disk c0t0d0 into slices, say a 10GB drive would be cut into three ...
4
votes
3answers
6k views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
5
votes
2answers
1k views
Any gotchas at all with converting from MyISAM to InnoDB?
I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
2
votes
2answers
2k views
Scheduled optimization of tables in MySQL InnoDB
What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
4
votes
2answers
5k 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 ...
9
votes
5answers
8k views
MySQL server has gone away obstructing import of large dumps
I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a
ERROR 2006 (HY000) at line 7758: MySQL server ...
5
votes
2answers
1k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
1
vote
1answer
1k views
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
4
votes
2answers
2k views
What to set innodb_buffer_pool and why..?
I have 170GB of InnoDB index and Data.
I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.
So what should be the optimal ...
1
vote
2answers
3k views
MySql - Clean ibdata1
I have a database that its entire size is ~44GB, out of it ibdata1 is ~35GB that doesnt make sense, the size of the data shouldnt be more then 10GB.
i use the following query to get an esstimation of ...
2
votes
2answers
693 views
Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction.
This is done in order to "lock ...
6
votes
1answer
2k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
6
votes
4answers
4k views
How to improve InnoDB DELETE performance?
So I have this audit table (tracks actions on any table in my database):
CREATE TABLE `track_table` (
`id` int(16) unsigned NOT NULL,
`userID` smallint(16) unsigned NOT NULL,
`tableName` ...
3
votes
1answer
396 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 ...
1
vote
1answer
1k views
16 Cores 12 GB RAM server MySql Configuration - my.cnf
The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
9
votes
2answers
2k views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
3
votes
1answer
895 views
How large will a MySQL database be relative to the dump file?
I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
2
votes
1answer
968 views
Mysql Memory table getting many locks
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.
This table has the following activity:
1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of ...
5
votes
3answers
1k views
Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
I need to design database to store information contained in millions of log files generated by devices on trial on network.
I have designed 5 different tables to store log file information. But, as ...
4
votes
1answer
486 views
What are your use-cases for HandlerSocket?
There's a bit of hype around: HandlerSocket
It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in.
My question is, ...
3
votes
1answer
315 views
Which mysql storage engine to choose?
I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).
The theories say:
Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
5
votes
3answers
3k views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
4
votes
2answers
5k views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
10
votes
4answers
8k views
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
4
votes
1answer
1k views
Capabilities of InnoDB INSERT Performance
Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id ...
7
votes
2answers
233 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
5
votes
1answer
378 views
Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?
I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
5
votes
2answers
3k views
Ibdata usage and Recommendations?
I have a Production server
On November 5,2011 the ibdata size was 100G.
In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.
I ...
3
votes
3answers
635 views
InnoDB vs MyISAM with many indexes
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a ...
3
votes
2answers
788 views
What happens when InnoDB hits its tablespace autoextend max?
The latest version of High Performance MySQL (3rd ed.) recommends that one:
...place an upper limit on the tablespace's size to keep it from growing
very large, because once it grows, it doesn't ...
1
vote
1answer
774 views
importing myisam 5.0 database into a 5.5 innodb server
I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
7
votes
1answer
2k 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 ...
