4
votes
2answers
4k views

When should I think about upgrading our RDS MySQL instance based on memory usage?

It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
3
votes
4answers
10k views

Optimized my.cnf for high-end and busy server

Curretly my main culprit of poor performance is MySQL and I'm not a 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 ...
13
votes
4answers
19k views

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
4
votes
1answer
2k 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 ...
4
votes
3answers
1k views

Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out ...
3
votes
3answers
3k 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 ...
11
votes
3answers
7k views

Is it possible to restore sql-server bak and shrink the log at the same time?

We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
10
votes
2answers
416 views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
6
votes
3answers
3k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
5
votes
1answer
2k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
5
votes
2answers
2k views

Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

I need to keep track of deleted items for client synchronization needs. In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - ...
5
votes
2answers
2k 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 ...
4
votes
3answers
17k views

Cannot GRANT privileges as root

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
4
votes
3answers
2k views

Changed max_allowed_packet and still receiving 'Packet Too Large' error

I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
1
vote
2answers
2k views

Stress test MySQL with queries captured with general log in MySQL

Is there any tool available which can do stress testing using the log file created by MySQL general log? After a lot of search on google I found few stress testing tools which only use some benchmarks ...
6
votes
3answers
556 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
5
votes
3answers
2k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
4
votes
2answers
4k views

How to refresh AD security group on Sql Server permissions

I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP. I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
4
votes
1answer
4k views

Mysql Master-Master Replication Topologies on >2 machines

I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote. Let's call my main server A, the secondary server B, and a remote, development ...
2
votes
1answer
536 views

query cache hit value is not changing in my database

The query cache hit value is not changing in my database only for some queries.If I write select * from drugs_info limit 1000; the query cache hit has changed, but if I execute the following ...
2
votes
2answers
748 views

How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
2
votes
2answers
773 views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
55
votes
5answers
4k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
50
votes
6answers
16k views

What are the differences between NoSQL and a traditional RDBMS?

What are the differences between NoSQL and a traditional RDBMS? Over the last few months, NoSQL has been frequently mentioned in the technical news. What are its most significant features relative to ...
32
votes
4answers
12k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
27
votes
7answers
10k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
12
votes
7answers
14k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
52
votes
8answers
4k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
18
votes
3answers
27k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
10
votes
3answers
11k views

Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

I've read an article that mentioned we can achieve 60,000 inserts per second by LOAD DATA IN FILE statement that read from csv files and insert it into database. Why should it differ from normal ...
24
votes
8answers
9k views

Tool to generate large datasets of test data [closed]

Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
18
votes
4answers
6k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
39
votes
5answers
12k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
24
votes
20answers
1k views

How to dive into an ugly database?

I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial ...
15
votes
3answers
17k 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?
19
votes
1answer
20k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
29
votes
7answers
2k views

The smallest backup possible … with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
12
votes
7answers
16k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
13
votes
3answers
1k views

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query. The MSDN docs say that the general logical ...
7
votes
1answer
1k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
7
votes
3answers
29k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
11
votes
3answers
5k views

SQL Server 2008 R2 “Ghost Memory”?

We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
10
votes
1answer
5k views

SQL query for combinations without repetition

I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n. Extended sample input and result so input ...
5
votes
1answer
3k views

Best solution for cross-datacenter MySQL master-slave replication

We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service. System ...
8
votes
2answers
7k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
8
votes
1answer
3k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
7
votes
1answer
9k views

Performance implications of using OPENQUERY in a view

Please see this http://stackoverflow.com/questions/7753380/performance-implications-for-using-openquery-in-a-view question on stackoverflow, it's a cross over subject and would love a dba's POV
6
votes
1answer
2k 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 ...
6
votes
1answer
3k views

MySQL Workbench Database Sizes

I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using. Does anyone know of an easy way to figure this out? If nothing else, the default location ...
6
votes
4answers
3k views

MySQL Replication - Introduce new Slave to replication

I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL ...

15 30 50 per page