Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

1
vote
1answer
612 views

Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

Lately we run into performance inssues and I started tracking some recommended sql, memory, disk and cpu counters. I informed myself about the recommended values and numbers to look at. One of the ...
0
votes
2answers
139 views

Database is slow once in a while

I am executing a web service for 100 times. The average response time of this service is 1.5 seconds. But when I look at the response time of each execution individually, there are a few executions ...
2
votes
1answer
117 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
2
votes
1answer
5k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
1
vote
1answer
30 views

Select two aggregate functions on the same column using different criteria - performance

Dear stack exchange users, first question here. The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many ...
0
votes
2answers
42 views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
1answer
275 views

Optimize MySQL query_cache_min_res_unit

My query cache settings are the following: mysql> SHOW GLOBAL STATUS LIKE 'Q%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ ...
0
votes
2answers
49 views

Do I need to reindex my table in mysql after delete?

I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
1answer
51 views

Query optimization for multiple joins postgres

ER Diagram Brief description The database to be used is postgres. The database might contain upto 2 million AccountHolder records. Since the AccountHolder table is accessed and updated frequently ...
0
votes
0answers
16 views

Intermittent MySQL 5.17 issues - extremely slow queries

I am experiencing major issues with intermittent mySQL issues. To give some background the website runs optimally for the majority of the time, however 3-4 times a day queries that should take a split ...
0
votes
0answers
16 views

PostgreSQL buffer page size limitations - rebuild with larger size?

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error. This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
0
votes
1answer
106 views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
1
vote
2answers
73 views

Identifying cause of high I/O

I'm trying to diagnose the bottleneck for high-write I/O activity on a percona master (with replication to a slave). The pattern of behaviour is roughly cyclic and regular: 60 mins of 75-90% ...
0
votes
1answer
1k views

How to change page size in an existing sybase database

I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page ...
0
votes
1answer
108 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
0
votes
1answer
71 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
0
votes
1answer
143 views

SQL SERVER – Find Most Expensive Queries

I am running the following script to find most expensive queries. However I want to narrow it down to only queries ran on Saturday 18th October 2014 Is there any way to do this? SELECT TOP 50 ...
0
votes
1answer
34 views

What do these pstart and pstop values mean?

I'm attempting to tune a query and I want to be able to tell if partition pruning is happening. On one part of the query, the I'm getting a Pstart and Pstop value = "KEY": | Id | Operation ...
0
votes
2answers
102 views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
0
votes
0answers
65 views

Window function sorting performance

For background, I am on an instance of SQL Server 2012 Enterprise Edition. I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in ...
0
votes
1answer
32 views

MySQL: What do we mean by 0 value in in_use column of show open tables statement?

If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean? Are these tables available in cache but not used by any thread?
0
votes
0answers
20 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
0
votes
0answers
23 views

How to handle too many instances of multiple tables?

If there is a table accessed by multiple DB threads ( for example 100) at a time then how many instances of that table will be opened? If I run show open tables and see that in_use column value of a ...
0
votes
0answers
40 views

MySQL: What should I do if too many tables opened during peak hours?

In Peak hours: When I run below statement, I found around 100 tables out of 250 have opened. show open tables where in_use>0; There are around 10 tables for which in_use value are >50. Storage ...
1
vote
1answer
24 views

Do threads in cache still use memory?

I was studying about how and how much memory allocated to each connection thread and found below MySQL tutorial: http://dev.mysql.com/doc/refman/5.1/en/memory-use.html There is a point mentioned as ...
2
votes
1answer
128 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
0
votes
0answers
17 views

SSIS Performance tunning [duplicate]

I have few questions on SSIS Performance tunning. Suppose My system has 8GB RAM and I have installed SQL Server 2012 along with DataTools. SQL Server uses 7GB memory. Will SSIS uses the 7GB memory or ...
1
vote
0answers
60 views

DB Design: Looking for Performance improvement with a Column used in all SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running ...
-1
votes
1answer
63 views

Very Slow MySql Stored Procedure

I'm working with a web app running on MySQL. It uses stored procedures for all database-related tasks. The original web app and the database were designed by another developer; I'm only maintaining it ...
1
vote
0answers
25 views

Tuning MySQL On Azure Windows Server Setup

I have a MySQL 5.5 running on Azure VM Windows Server 2012. Once per second the log db gets about 2K inserts through LOAD DATA INFILE... once a day I need to run stats and when the stats are crunching ...
7
votes
1answer
128 views

Lower performance on newest servers

We have several db servers on production, 4 of them with very similar hardware configuration. Dell PowerEdge R620, only difference is that 2 newest ones (bought and configured 3 months ago) have RAID ...
2
votes
1answer
99 views

Searching a big table for a few results without scanning the whole table

I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) ...
1
vote
0answers
55 views

MongoDB read performance

I have an ingestion process which loads data into a specific collection at a very high rate. Some of these documents have to be loaded into another system in batches of 100K . Means as soon as I find ...
0
votes
0answers
13 views

Multiple Trace Files or Replay

I am just wondering how useful the replay functionality of SQL profiler is. The issue I have is that I can capture a workload from production with little overhead, however I would not be able to ...
0
votes
1answer
45 views

MySQL: How turning off the logging affect the performance?

OS: RHEL5 DB: MySQL 5.1.73 community edition DB size: ~1GB I have setup a testing environment and turned off all logging - general logs, binary logs, slow query logs etc. I am using Oracle ...
0
votes
0answers
58 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
2
votes
3answers
361 views

Collecting Wait Stats

We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. Below are the wait stats by number of tasks waiting and also wait time per ...
1
vote
1answer
3k views

Optimize PostgreSQL for a lot of INSERTS and bytea updates

What we have (software): PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf) Windows 7 64 bit Hardware: Intel Core i7-3770 3.9 Ghz 32 Gb RAM WDC WD10EZRX-00L4HBAta Drive ...
0
votes
1answer
201 views

Oracle average active sessions high threshold

I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It ...
1
vote
0answers
249 views

Optimization Tips For MYSQL Tuner/Primer

So I have a site that has a crap ton of traffic. I see a decent amount of latency when the site has over 1,000 active visitors on it. I have new relic installed and it seems Im only using 10% of the ...
1
vote
0answers
209 views

What to do with unused RAM on a MySQL database server?

For years I have been running dedicated servers with more or less limited ressources, resulting in unsatisfying performance of the web application (Drupal). Tools like Matthew Montgomery's MySQL ...
0
votes
1answer
33 views

Need DDL for an Oracle Table that would be 1 row = 1 8K Block

For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature ...
5
votes
2answers
262 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
5
votes
1answer
456 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
1
vote
1answer
155 views

Sort Warnings, finding out the query behind them

I'm troubleshooting a SQL Server 2012 that has started timing out as of a few weeks ago. The server is filled with thousands of sort warnings, at a rate of about 15 warnings per minute. To get the ...
4
votes
1answer
641 views

Database under heavier load — all SQL queries start timing out after a period of hours/days

I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it. You can see that everything seemed to be OK until about 3AM this ...
2
votes
2answers
112 views

Need help performance tuning a query

Basically, here is the design of the table (the part that is necessary): id | dt -------- 1 | 2014-05-13 2 | 1900-01-01 3 | 1900-01-01 4 | 2014-05-12 5 | 1900-01-01 So what I am trying to do ...
3
votes
1answer
681 views

Understanding sys.dm_exec_query_stats execution_count vs creation_time

I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV ...
1
vote
1answer
104 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
1
vote
1answer
49 views

DBCC sqlperf(waitstats) contains signal wait time longer than last time waitstats was cleared

I'm trying to troubleshoot some performance problems on our SQL2000 production server. I ran DBCC SQLPERF(waitstats) and noticed the stats hasn't been cleared in a long time. So I cleared them and ...