An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
7
votes
3answers
291 views
Performance benefit of taking unused databases offline
I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
3
votes
1answer
713 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
1
vote
0answers
92 views
Why is this table structure so slow to CRUD?
I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records.
-- Table "mail_queue" DDL
CREATE TABLE ...
2
votes
1answer
207 views
exist (select * from my table)
My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.
Query (1):
IF ( SELECT COUNT(UnitTrustCounterId)
FROM ...
2
votes
1answer
147 views
Performance impact of setting PostgreSQL to log all statements
PostgreSQL 9.2.2 on Windows 7 64-bit.
The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
5
votes
1answer
313 views
Oracle aggregate functions slows query down massively
I have a table with ~12M rows of data in it. Here is the table structure:
SYSTEM_ID
BATCH_ID
MEASUREMENT_INDEX,
PARAMETER_ONE,
PARAMETER_TWO
The primary key is made up of the first three columns. I ...
1
vote
1answer
107 views
Deriving formulas for input/output
I'm currently enrolled in a DBS class and am having problem with an assignment. I've searched around and have been unable to understand what it is I'm meant to be doing with this derivation formula.
...
3
votes
2answers
138 views
Tablestructure for fast inserts/deletes with foreign keys
Current Situation
We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product.
Example:
id | competitors | some infos about competitor | ...
3
votes
3answers
235 views
High insert rate on MySQL
I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
1
vote
1answer
605 views
Oracle 11g: “With Clause” very slow
I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
3
votes
2answers
271 views
Increase Oracle buffer cache gets slower
So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
3
votes
1answer
221 views
Performance effects of database file size and available space
I have set the autogrowth and maxsize properties to:
By 10 MB, Unlimited
When I look at the Properties -> General Tab by right-clicking the database in SSMS, I see the file size is 2249MB with 6.1 ...
9
votes
1answer
624 views
SQL Server Cache Flush and Disk I/O
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
0
votes
2answers
159 views
MySQL Benchmark on FreeBSD and Ubuntu [closed]
I'd like to benchmark two db servers, one is running Ubuntu the other FreeBSD. Unfortunately we are unable to install SysBench on the FreeBSD OS. Could anyone recommend any alternatives? Or should I ...
2
votes
0answers
442 views
Oracle high Paging Faults
What are the reasons that lead to terrible high Paging faults.
The following is the topas information and background about the Environment.
OS: AIX 6.1
Softwares: Oracle 11gR2
Paging Space:30720 ...
2
votes
3answers
470 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
0
votes
3answers
2k views
sql server instance using maximum cpu usage
I have a SQL server 2005 instance installed on the server machine with two vm
Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware
It is running asp.net application ...
3
votes
1answer
213 views
SQL Server Row Count sp_spaceused rows discrepancy
I am researching a performance problem with an application running on SQL Server 2005. I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered ...
3
votes
1answer
222 views
Will one nvarchar column affect query performance?
I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. ...
3
votes
1answer
375 views
Is a clustered index locked while a (clustered index) scan is in progress?
The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.
The problem leading me to this question is that I have a view joining several ...
0
votes
0answers
277 views
Oracle 10g - Sudden change in execution time for specific query
After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went ...
1
vote
2answers
150 views
Derby/SQL - How to increase performance when left joining and retrieving varchar columns
Through trial and error I found that if I do a left join such as:
SELECT
firsttable.id,
secondtable.id,
secondtable.varcharColumn
FROM
firsttable
LEFT JOIN
secondtable
ON
...
0
votes
0answers
87 views
Please help configure my Mysql for 12GB 4cores server
I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
-3
votes
2answers
291 views
Optimisation sub query SQL Server
I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 ...
0
votes
2answers
73 views
MySQL query optimisation advice for search query
We are currently in the process of trying to re-write a very large and nasty dynamic search query. The search term for this query was 'red christmas napkins' and these 3 keywords have been added to ...
0
votes
0answers
39 views
mysql select query optimization [duplicate]
Possible Duplicate:
mysql select query optimization
I have two tables, testa and testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
...
-3
votes
0answers
31 views
Doubt :: my sql performance tuning [duplicate]
Possible Duplicate:
MySQL performance tuning
I would like to optimize my MySQL DB and I identified some important points as follows:
enabling query cache,
using memory storage (I know ...
2
votes
0answers
80 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
0
votes
2answers
49 views
How to modify MySQL 5.5 settings that were set during the installation?
Especially the one that defines how MySQL is gonna use server's CPU. I'd like to boost performance. Also, what are other ways to boost MySQL performance? (Maybe some code optimization hints?) Anyway. ...
5
votes
3answers
138 views
SQL Server how to test filegroup IO
We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN.
In order to determine ...
1
vote
0answers
164 views
Finding the bottleneck by analyzing Munin graphs
We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
3
votes
4answers
424 views
Is it advantageous to scale out using multiple SQL Server instances on the same server?
For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server?
What will I gain and what will I lose? ...
4
votes
1answer
130 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
13
votes
2answers
424 views
Does varchar size matter in temporary tables?
There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work ...
3
votes
1answer
93 views
which design better when use foreign key instead of a string to store a list of id [duplicate]
Possible Duplicate:
Is using multiple foreign keys seperated by commas wrong, and if so, why?
I'm building online examination system. I have designed to table, Question and GeneralExam. The ...
10
votes
1answer
486 views
SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?
Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.
Interested to hear from others who have found usage cases where ...
1
vote
1answer
536 views
InnoDB Performance Tuning
I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem.
I'm a RoR developer and our team is currently short a DBA, so we're ...
5
votes
3answers
303 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
0
votes
0answers
52 views
Long disk queue when no traffic
I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 ...
1
vote
2answers
280 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
3
votes
4answers
621 views
mysql : inner join takes 3 minutes
I had a similar question open on SO for postgres - now having the same issue with mysql..
I have two tables -
Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B)
Table B : 50k rows, Id, ...
0
votes
1answer
183 views
INNER JOIN between 2 tables vs WHERE on one table performance
Suppose I have two tables:
CREATE TABLE `doctors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`clinic_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
...
6
votes
2answers
293 views
Adding a Clustered index to a HEAP table on someone else's app
We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
0
votes
0answers
48 views
How to store massive quantities of machine generated data?
I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances. ...
1
vote
2answers
433 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
1
vote
2answers
174 views
PHP-MYSQL messaging system performance problems
I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?
I think, ...
3
votes
2answers
116 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
0
votes
0answers
159 views
Optimizing MySQL for a shared hosting setup
This is my current setup:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
...
7
votes
1answer
874 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
2
votes
0answers
719 views
Table Lock on INSERT - How to optimize
we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
