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 ...
16
votes
6answers
10k views
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an ...
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:
...
12
votes
2answers
4k views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the ...
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 ...
35
votes
9answers
3k views
Files - in the database or not?
What is the best place for storing binary files that are related to data in your database? Should you:
Store in the database with a blob
Store on the filesystem with a link in the database
Store in ...
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
855 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
687 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 ...
41
votes
9answers
9k views
Why is Database Administration so hard?
I know a lot of Database Administrators and they are all over 28-29 years old.
Is all database administration like that? I mean, is this about getting experience more than at least 7-8 years?
Or is ...
10
votes
8answers
2k views
Must-read Books for DBA [closed]
This question is almost mandatory in any SE site for basic reference.
Please post only books with permanently value for DBAs in general.
The post should be a must read book, the classical ones, the ...
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 ...
16
votes
3answers
11k views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
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
13k 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, ...
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 ...
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, ...
22
votes
7answers
4k views
Is it better to store images in a BLOB or just the URL? [duplicate]
Possible Duplicate:
Files - in the database or not?
I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch ...
14
votes
2answers
4k 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 ...
29
votes
7answers
2k views
What are the arguments against or for putting application logic in the database layer?
NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
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 ...
35
votes
4answers
5k views
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these ...
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 ...
11
votes
3answers
2k views
Schema design - best practices?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
4
votes
2answers
1k views
Which DBMS is good for super-fast reads and a simple data structure?
I am developing a product that, as a part of it's operation, must track a large number of files/directories. The idea is to store stat information in a database then, on boot, create watches for each ...
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
736 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 ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
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 ...
