Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

1
vote
0answers
336 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
350 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
34 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 ...
0
votes
1answer
383 views

Optimize MySQL query_cache_min_res_unit

My query cache settings are the following: mysql> SHOW GLOBAL STATUS LIKE 'Q%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ ...
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 ...
5
votes
2answers
264 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 ...
1
vote
1answer
199 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 ...
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 ...
2
votes
1answer
148 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 ...
2
votes
2answers
116 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
948 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
117 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
55 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 ...
1
vote
1answer
259 views

InnoDB engine is not loading after tuning parameters

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below ...
0
votes
1answer
122 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
106 views

Big Mysql table optimization

I have a table that I use to cache google translate API answers. Table structure is orig_string and group are indexes Now it has more that 1million rows with 35000 distinct values in group field. ...
0
votes
0answers
23 views

Performance One Row With Total Vs Multiple Rows with Seperate Values?

Not 100% sure how to explain this situation but here it goes: I am building an Inventory Database that all items received are entered into and a label is created for each Container, that is going to ...
0
votes
1answer
48 views

MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue. So, I have gone ...
1
vote
0answers
85 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
0
votes
0answers
128 views

Remote scan in OpenXMl

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML. Something about this is causing a Remote Scan. Any ideas on how ...
0
votes
2answers
194 views

Instead of static list, using a table select result for IN key

I use a static list for some queries WHERE clause like WHERE STypeID IN ('12', '13', '14', '265') This list will be changed in the future; new values will be added. When list changed, I don't want ...
1
vote
1answer
355 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 ...
4
votes
1answer
719 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
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
2answers
173 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 ...
1
vote
1answer
109 views

Get maximum number with combination of columns [closed]

I have table with columns like AutoID, Number, Name, City, State, Country. What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country. ...
1
vote
0answers
46 views

performance improvement - left join

How do I rewrite to improve performance of the below query. select S.col1, S.col2, MaxDDate from s_tbl s left join (select col1, MAX(DDate) MaxDDate from F_tbl group by col1 ...
2
votes
1answer
538 views

SQL Server : How to make “Update Statistics” faster?

We have a table with a 1 column Primary Key on and an UNIQUE Non-Clustered Index with 16 columns. This table has more than 20695780 Rows and on every day new records inserted ...
0
votes
0answers
174 views

MySQl: slow query is not using index in joining

Below is the configuration of my DB server: Apache version : Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8 PHP version : 5.3.13 MySQL version : mysqlnd 5.1.11 Windows Server 2008 Standard ...
2
votes
1answer
467 views

Slow SELECT against clustered index [closed]

I have a table which will hold 10 million rows. I'm seing performance issues when quering it, and wonder if I can somehow improve the schema or query, or if I need to look into sharding. I've used SQL ...
0
votes
1answer
79 views

Is it any good for performance to have master and slave on the same server

There are similar questions on this site, but they mostly cover server failure and data backup aspects. I'm concerned only as far as query performance. If master and slave share the same resources, ...
0
votes
1answer
142 views

Which index types do not support online index rebuilds?

In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds? and why? and which activity-operations can prevent online index rebuilds?
1
vote
0answers
53 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
2
votes
2answers
234 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
1
vote
2answers
871 views

Should I load the data into Temp table or keep calling it from the normal table? [closed]

I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
2
votes
1answer
456 views

MySQL Performance Issues

Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that ...
3
votes
1answer
867 views

How to troubleshoot db/app latency during transaction log backup

I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups. ...
2
votes
1answer
414 views

What database and setup can handle several million inserts per minute? [closed]

We have come to a point where for a few of our customers' systems, at peak times, events are generated at that crazy rate. That is, millions of new events per minute (between 1M and 2M). The ...
2
votes
1answer
2k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
1
vote
2answers
6k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
3
votes
1answer
759 views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
4
votes
1answer
297 views

Tips on improving/refactoring slow query?

Let's play lotto! Ok. I have a numbers table with 80 rows (numbers 1-80): create table dbo.numbers (knum tinyint primary key identity); I have a table with the drawid, and each number drawn. ...
4
votes
1answer
338 views

SQL Server 2008: Measure tps / select statements per second for a specific table?

There is a discussion how to measure activity against an entire DB: http://stackoverflow.com/questions/5019593/sql-server-2008-transaction-per-second-for-specific-database My question is narrower. ...
2
votes
2answers
6k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
3
votes
3answers
853 views

Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
2
votes
0answers
68 views

Performance of database with transactional data

There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and ...
3
votes
2answers
3k 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 ...
0
votes
0answers
121 views

InnoDB tuning with 1G of ram limit

I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
5
votes
1answer
520 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 ?