57
votes
3answers
45k 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 ...
29
votes
3answers
38k 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 ...
65
votes
8answers
47k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
13
votes
2answers
23k 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 ...
24
votes
6answers
21k 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 ...
37
votes
4answers
45k 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: ...
21
votes
3answers
24k 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 ...
65
votes
6answers
49k 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 ...
20
votes
3answers
25k 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 ...
5
votes
3answers
1k 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 ...
4
votes
2answers
3k 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 ...
8
votes
1answer
398 views

How to move a database from SQL Server 2012 to SQL Server 2005

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)? I know I cannot: restore a backup of the database on SQL Server 2005 detach & attach I ...
4
votes
1answer
5k 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 ...
6
votes
2answers
3k 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 ...
14
votes
3answers
18k 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 ...
18
votes
5answers
26k 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 ...
46
votes
11answers
6k 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 ...
13
votes
2answers
5k 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 ...
9
votes
1answer
5k 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 ...
5
votes
3answers
7k 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 ...
4
votes
3answers
6k 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?
9
votes
2answers
4k 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 ...
23
votes
3answers
18k 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 ...
19
votes
3answers
36k 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?
15
votes
1answer
2k 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 ...
4
votes
2answers
5k 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 ...
5
votes
1answer
3k 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 ...
50
votes
14answers
92k 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, ...
66
votes
14answers
20k 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 ...
14
votes
8answers
5k 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 ...
5
votes
1answer
5k 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 ...
18
votes
2answers
3k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
7
votes
3answers
3k 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.
36
votes
5answers
3k views

Is there a name for this database structure?

Noob question, sorry if it's too basic for this exchange. We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one ...
15
votes
3answers
5k 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 ...
5
votes
2answers
8k 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 ...
4
votes
2answers
7k 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, ...
23
votes
5answers
6k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
8
votes
1answer
5k views

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. What I did was: Shutdown SQL Server 2000 (express) service on old machine, Move datafiles (mydatabase.mdf and mydatabase.ldf) to new ...
3
votes
2answers
2k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
9
votes
2answers
2k views

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
3
votes
5answers
20k views

How to access a SQL Server database from other computer connected to the same workgroup?

I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open ...
14
votes
1answer
16k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files 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 ...
26
votes
7answers
8k 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 ...
12
votes
3answers
6k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
8
votes
3answers
43k 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 ...
7
votes
2answers
1k views

Limit connection rights on SQL Server

I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part ...
3
votes
1answer
15k 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 ...
2
votes
2answers
1k views

MySQL general log

Is there away to have MySQL dump its log to a remote MySQL server? I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.
18
votes
3answers
28k 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 ...

15 30 50 per page