Linked Questions

-1 votes
1 answer
828 views

How important is InnoDB versus MyISAM? [duplicate]

Does it really matter if the default storage engine is InnoDB? My default storage engine is MyISAM
EGB's user avatar
  • 1
64 votes
4 answers
139k 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?
jcho360's user avatar
  • 1,999
34 votes
3 answers
30k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
Zenklys's user avatar
  • 451
38 votes
5 answers
57k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
jollyroger's user avatar
10 votes
2 answers
70k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
32bitfloat's user avatar
13 votes
3 answers
40k views

Cache MySQL database in memory

I am having problems with a website with 600MB of MySQL database. The website is way too slow. I noticed the bigger the MySQL database gets, the slower it goes. When it was 5MB, the website was very ...
grant tailor's user avatar
4 votes
2 answers
37k views

MySql - Clean ibdata1

I have a database whose entire size is ~44GB, out of which ibdata1 is ~35GB. That doesnt make sense, since the size of the data shouldn't be more than 10GB. I use the following query to get an ...
Ran's user avatar
  • 1,533
7 votes
4 answers
8k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
Rick James's user avatar
  • 1,271
8 votes
2 answers
38k views

MySql recommended hardware

Our company servers are currently hosted on a VPS and we've decided to move into dedicated servers. When choosing the best hardware for a DB server, what should we invest more resources: better CPU (...
Ran's user avatar
  • 1,533
6 votes
2 answers
10k 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 ...
user3731's user avatar
  • 205
4 votes
4 answers
16k views

Optimized my.cnf for high-end and busy server

Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and ...
alfish's user avatar
  • 2,964
5 votes
2 answers
17k views

MySQL performance impact of increasing innodb_buffer_pool_size

I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB. My queries are responding well now i.e under mili seconds. ...
JP Chauhan's user avatar
  • 1,351
10 votes
2 answers
16k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
Kilian's user avatar
  • 201
4 votes
1 answer
24k views

MySQL Performance Tuning for MyISAM

I need to performance tune a mysql server and need help. I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
rho's user avatar
  • 41
8 votes
3 answers
4k views

MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

I am creating a module where every user often gets a record into a table for 10 to 300 seconds. When the time expires a record gets deleted. The case is: there will be a lot of users and records will ...
Aivaras's user avatar
  • 183
11 votes
2 answers
7k 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 ...
Andrew's user avatar
  • 642
2 votes
1 answer
23k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Current my.cnf Show engines output Server has two 6-core Xeon processors (2 threads per core = 24 threads) What would be the best ...
tschelik's user avatar
7 votes
1 answer
8k 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 ...
Andrew's user avatar
  • 642
6 votes
2 answers
6k views

mysql transaction vs lock

I have a question regarding MySQL locking and transaction management. My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
matang's user avatar
  • 235
6 votes
2 answers
4k views

MySQL warm procedure

How do you usually warm up your database ? I run a similar queries to this for every table in DB: SELECT * FROM ip_log ORDER BY ID; SELECT ip, member_id FROM ip_log ORDER BY ip, member_id; Is that ...
Katafalkas's user avatar
7 votes
3 answers
21k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
Olle's user avatar
  • 71
3 votes
2 answers
20k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
Alireza's user avatar
  • 3,636
6 votes
2 answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE `...
user5793's user avatar
4 votes
3 answers
2k views

Database Performance Tuning

I have a MySQL backend, MS Access front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. ...
David's user avatar
  • 539
3 votes
1 answer
8k views

MySQL high CPU usage (MyISAM table indexes)

I have a problem with an inherited MySQL database. From time to time mysqld uses up to 2300% CPU.. The only solution is to service mysql stop and run an myisamchk -r on a table. After the indexes have ...
tschelik's user avatar
6 votes
1 answer
4k 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 ...
jcho360's user avatar
  • 1,999
5 votes
1 answer
1k views

Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
MrD's user avatar
  • 163
3 votes
2 answers
14k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON (`Business`...
Surjit Sidhu's user avatar
1 vote
2 answers
9k views

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached......
PavelBY's user avatar
  • 111
4 votes
1 answer
1k views

What are the concepts that need to be gone through for MySQL DBA?

I am a MySQL Developer, besides I am handling some DBA activities like backup, restore, mysql installation, etc at a top level. Now I wanted to shift to DBA, So I wanted to know in depth, like which ...
Phanindra's user avatar
  • 1,007
6 votes
2 answers
910 views

Estimating distribution of row access in mysql

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
David Cournapeau's user avatar
4 votes
2 answers
811 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
Jack Douglas's user avatar
  • 39.8k
2 votes
1 answer
4k views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%)...
Gabriel Santos's user avatar
1 vote
2 answers
4k views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
Cratylus's user avatar
  • 1,003
0 votes
2 answers
6k views

Best Innodb Buffer Pool Size for 8GB RAM?

I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size. That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina ...
Jose Agustin Reinoso's user avatar
0 votes
2 answers
3k views

Row size too large (> 8126)

I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT. Now, when entering data into one such row, I get mysql_error() = Row size too large (> 8126). Changing some ...
anjanesh's user avatar
  • 279
5 votes
1 answer
887 views

Speeding SQL queries for a large(?) WordPress database?

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here). The plugin is great ...
Tal Galili's user avatar
2 votes
2 answers
1k views

Any problem will all InnoDB and one MyISAM table?

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
brian's user avatar
  • 21
1 vote
2 answers
974 views

MySQL UPDATE Performance Issue

I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects. The table in question is pretty small, with only 5396 records. However, I'm seeing queries like this show up in ...
jrallison's user avatar
  • 113
2 votes
1 answer
1k views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
matt's user avatar
  • 203
4 votes
1 answer
469 views

Are InnoDB tables harder to backup and slower?

I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines ...
esther h's user avatar
  • 143
4 votes
1 answer
524 views

Will a change from MyISAM to InnoDB require SQL query changes in MySQL?

I am planning to change some large MyISAM tables in my MySQL database into InnoDB. This is because I am having performance problems where large and complex updates (updating a few million rows in one ...
David Scholefield's user avatar
6 votes
2 answers
801 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
Zbynek's user avatar
  • 324
3 votes
1 answer
1k views

Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

I learned that innodb_buffer_pool_size makes InnoDB behave like an in-memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or ...
giorgio79's user avatar
  • 1,407
1 vote
1 answer
1k views

MySQL 5.5 vs 5.6 with WordPress

We have a moderately busy WordPress blog that runs currently on MySQL 5.5, and I'm wondering if I would experience any significant performance upgrades by moving it to MySQL 5.6? Anyone know? I'd ...
Michael's user avatar
  • 13
1 vote
1 answer
2k views

What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?

In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows ...
Bryan Agee's user avatar
1 vote
1 answer
565 views

For 768M RAM server is 617M allocation in my.cnf good or bad?

I have a write-intensive web app, and I bought a VDS with 768M RAM Is this my.cnf good for my purposes? I specified bulk_insert_buffer_size to 126M and some other parameters as below: [mysqld] ...
Alireza's user avatar
  • 3,636
1 vote
2 answers
2k views

Changed MyISAM to InnoDB, huge performance loss [closed]

So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss. Here is my.cnf: innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_buffer_pool_instance=2 ...
Marc Alexander's user avatar
1 vote
1 answer
987 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 ...
David Parks's user avatar
2 votes
1 answer
1k views

Doubts about Converting MyISAM to InnoDB (master/slave and more)

I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process. Since I have a Master/Slave structure, do I need to manage this and change ...
jjadams's user avatar
  • 21

15 30 50 per page