Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

0
votes
3answers
463 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
97 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
132 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
0answers
28 views

SQL Server 2012: query to see queries that are consuming more resources [duplicate]

I'm trying to find/build a query to show me those queries that are consuming more resources in SQL Server so that I can optimize them or at least have a statistical idea of what is really happening. ...
0
votes
0answers
31 views

Performance issue after reorganizing table in MYSQL

There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based ...
2
votes
1answer
135 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 ...
0
votes
2answers
174 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 ...
0
votes
0answers
33 views

PostgreSQL Performance - Many Large Databases

I run an internal (development) database server containing 25 databases each ~71GB in size. There are usually between 50 and 100 active connections at any given time. What can I do to help improve ...
2
votes
1answer
39 views

Can I throttle individual databases instances on one SQL Server 2005 box

I am running a very old server that has a database that is being permanently retired within 14 months. My company will not invest anymore in hardware or software for this system as we are moving to a ...
2
votes
2answers
77 views

How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

+----+---------+---------+-----------------+ | id | view_id | user_id | ordinal_ranking | +----+---------+---------+-----------------+ | 1 | 10 | 1 | 1 | | 2 | 10 | ...
1
vote
1answer
30 views

maximum allocate RAM for MS SQL Server

I have MS SQL Server with different size of RAM. So I would like to control the values which are set for the RAM. Somewhere I read a 'rule' to calculate the maximum of RAM. These SQL Server are only ...
2
votes
0answers
41 views

How to identify whether a log backup or job executing a SP is causing High Page Reads/sec

For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST. I kept the counter settings to monitor the same and found ...
0
votes
1answer
28 views

How bigger page size effect at performance?

I know in case of Cluster index bigger page size improve peformance for range and multipoint query. Does bigger page size can effect to degrade performance when we have non cluster index .Because ...
2
votes
1answer
34 views

Does SQL Server redistribute data after adding an additional data file?

Imagine a database about 30GB in size using 3 data files on the same disk. Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three ...
3
votes
1answer
56 views

reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

I would like to know how to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing it. My intention is to define which I/O goes ...
4
votes
1answer
122 views

Table with clustered Index consuming max memory causing low PLE alerts

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day: Server: SQL server 2008R2 Databases : ...
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
2answers
572 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 ...
0
votes
1answer
17 views

What is exactly a uniform server in a galera cluster setup?

In many manuals about performance tuning and avoid Galera's MySQL cluster limitations they write the below suggestion. It is possible for a slow/overloaded master node to affect performance of ...
2
votes
0answers
48 views

SQL aggregate function performance in a table with many records that is well-indexed

My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information. There are three tables: ...
3
votes
3answers
300 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
0
votes
0answers
29 views

Query plan reads vs statistics io reads

I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the ...
0
votes
1answer
61 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
4
votes
2answers
64 views

Using Database Engine Tuning Advisor in production

I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will ...
1
vote
1answer
357 views

Different thread states in query execution

My MySQL 5.1 based Intranet system is facing very bad performance during high load. When observed through MonYog, I found below thread states: We can simply understand the different thread state by ...
1
vote
0answers
19 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 ...
0
votes
1answer
60 views

Is my SQL Job causing the High Disk usage?

I have a concern regarding High Average disk queue length for one of my server with below configuration: Version: SQL 2005 with SP3, standard edition. windows: version 2003 r2 SP2 No Processors= 4 ...
1
vote
0answers
33 views

Why does retrieving fewer rows take longer? [duplicate]

I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on: SQL Server ...
0
votes
1answer
96 views

OS Paging too high in sql server

For one of my servers with below configuration: Total RAM: 32 GB CPU: 8 Max Server Mem: 22.5 GB Min Server:: 4 GB Edition: Enterprise Edition with 2k8R2 installed on SP3 ...
1
vote
1answer
1k 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 ...
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
46 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
1answer
389 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
91 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
102 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
26 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
24 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 ...
1
vote
2answers
137 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
726 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
70 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 ...
1
vote
2answers
233 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
78 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
76 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
39 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
30 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
90 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
27 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
149 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
73 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 ...