MySQL : Open-Source, Relational Database Management System
16
votes
3answers
16k 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 ...
50
votes
8answers
23k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
10
votes
1answer
10k views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
22
votes
3answers
22k 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:
...
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 ...
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 ...
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 ...
8
votes
1answer
2k views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
4
votes
1answer
2k views
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
my.cnf (5.5 and 5.1 is the same) :
back_log=200
max_connections=512
max_connect_errors=999999
key_buffer=512M
max_allowed_packet=8M
table_cache=512
sort_buffer=8M
read_buffer_size=8M
thread_cache=8
...
43
votes
7answers
23k 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?
4
votes
3answers
856 views
Problem with MySQL subquery
Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing?
If I write it in this ...
12
votes
2answers
3k views
How do I properly perform a MySQL bake-off?
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
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
17k 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 ...
12
votes
1answer
688 views
MySQL : Why are there “test” entries in mysql.db?
Recently, I posted an answer to a question about mysql.db.
Then, I got to thinking I should ask everyone this question:
I have noticed for years that upon installation of MySQL 5.0+, mysql.db is ...
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 ...
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 ...
11
votes
3answers
2k 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 ...
4
votes
1answer
3k views
MySQL high availability, failover and replication with Latency
We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
17
votes
5answers
12k 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 ...
6
votes
3answers
14k views
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
I use the root account created the account 'a'@'%'. But I can't use the it to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see ...
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, ...
34
votes
14answers
48k views
How can I move a database from one server to another?
How can I move MySQL tables from one physical server to another?
Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.
I want to move it to a new server, ...
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 ...
14
votes
2answers
5k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
37
votes
4answers
2k views
Storing vs calculating aggregate values
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema ...
5
votes
1answer
6k views
Is there any best way to reduce the size of ibdata in mysql.?
I have some Production servers whose ibdata increases in size day by day.
It has already consumed 290GB of space.
The tables in the servers are mostly InnoDB and there are high read and write ...
5
votes
2answers
5k views
How do I output MySQL logs to syslog?
I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS).
I found information that output error log to syslog.
[mysqld_safe]
syslog
But I want to use syslog for logging general logs and ...
5
votes
3answers
2k views
From where does the MySQL Query Optimizer read index statistics?
I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.
4
votes
4answers
1k 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 ...
3
votes
1answer
737 views
MySQL master binlog corruption
We recently made some changes to our infrastructure and now I can't keep MySQL replication running. The slave complains of a corrupt binlog and resetting it doesn't help. I keep seeing entries like ...
3
votes
2answers
1k views
Trigger in MySQL
Please help me for few question in SQL..
I want to create the trigger which prevents insertion in the reservation table of flights (numflights), which does not exist.
Tables
...
1
vote
1answer
626 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
2answers
5k 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.
...
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 ...
15
votes
3answers
4k views
Advantages and Disadvantages to using ENUM vs Integer types?
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.
Is it better for this column's data type to be an int/bool (1 or zero) or to ...
7
votes
3answers
1k views
Is MySQL Replication Affected by a High-Latency Interconnect?
We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master.
The bandwidth between the datacenter is pretty high (in ...
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 ...
7
votes
3answers
2k views
Dynamic SQL in MySQL stored routines
According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
3
votes
1answer
479 views
Cannot drop anonymous user from mysql.user
I am trying to DROP the anonymous users from my mysql.users database. However, I have been getting odd behavior. When I enter the command:
DROP User ''@'WOPR';
I was getting a generic error ...
14
votes
5answers
3k views
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
5
votes
2answers
6k views
Modify DEFINER on Many Views
I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.
Got error: 1449: The user ...
4
votes
2answers
1k views
Scaling Percona datacenters: setup and replication
Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.
We have already scaled alot of our application stack: we offloaded ...
4
votes
1answer
6k views
Best way to setup master to multi master replication
ORIGINAL QUESTION:
I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers ...
3
votes
3answers
1k views
MySQL slow log configuration
Is there a way of making MySQL slow logs to start new log file every day ?
At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...
8
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 ...
4
votes
2answers
1k views
Running multiple instances on the same host
I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with:
mysqld_multi
MySQL Sandbox
This should be a long term solution for an ...
