Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

0
votes
2answers
109 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
2answers
238 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
2answers
149 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
122 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
136 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
1answer
45 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 ...
0
votes
1answer
48 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 ...
0
votes
1answer
77 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
46 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
1answer
115 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
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 ...
-1
votes
1answer
80 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 ...
2
votes
0answers
36 views

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

First question, so be gentle ;) I would like to know how to be able to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing ...
2
votes
0answers
152 views

High Cardinality, Time Series Data.. what DB should I use?

I have a system that I'm building based on ElasticSearch. It works great, except I'm having trouble with one of my facets. I've got about 10 million documents, and each document has 1 to 200 ...
1
vote
0answers
13 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 ...
1
vote
0answers
66 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
vote
0answers
38 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 ...
1
vote
0answers
57 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 ...
1
vote
0answers
295 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
259 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 ...
1
vote
0answers
81 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` ...
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 ...
1
vote
0answers
64 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 ...
1
vote
0answers
586 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
0
votes
0answers
15 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
0answers
20 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
22 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
0answers
67 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
0answers
27 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
26 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
65 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 ...
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
0answers
61 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 ...
0
votes
0answers
99 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
22 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
0answers
123 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
0answers
42 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 ...
0
votes
0answers
17 views

MySQL: How logging is useful in performance improvement

My application is facing performance hit. Its a MySQL-5.1.55 version. I am using Windows 2008 dedicated server with 8 GB for the database. There is only one database in this whole system. Till now, I ...
0
votes
0answers
311 views

MySQL: How to get locked tables list at a time

Is there any way or any query exist in mysql which list down all the locked tables at a time. I have dedicated Windows 2008 DB server with 8GB memory. In my MySQL5.1 based application, suddenly we get ...
0
votes
0answers
171 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 ...
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 ...