Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

2
votes
0answers
58 views

PAGEIOLATCH_SH and WRITELOG waits

We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG I have been collecting information from sys.dm_io_virtual_file_stats and can see ...
2
votes
0answers
51 views

MySQL memory is keep on increasing , any reason?

On one of our production host MySQL memory is keep on increasing day-by-day. I tried reboot of MySQL instance but still no avail. System has 16Gb of RAM. TOP output top - 13:05:13 up 35 days, ...
2
votes
0answers
48 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 ...
2
votes
0answers
80 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 ...
2
votes
0answers
256 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 ...
2
votes
0answers
770 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], ...
1
vote
0answers
59 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
1
vote
0answers
32 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
76 views

Mongodb Huge Number of Open Connections and Write Locks

We are seeing around 800 open connections to our Mongodb. To clarify our issue: We have 5 different clients who write the data into Mongodb. Each of these clients - write four different type of ...
1
vote
0answers
52 views

Mariadb improve performance while import dumpfile

I have a dumpfile with millions data inside. I want to decrease time because I waited too long. I imported 10 million data (3 GB) and I waited 1:30 hours. I will import nearly 100 million data (30 GB) ...
1
vote
0answers
62 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
1
vote
0answers
146 views

DB CPU parameter in AWR - Oracle

What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right ...
1
vote
0answers
33 views

Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes?

Why is it, when analyzing a query through Database Engine Tuning Advisor, statistic recommendations disappear after some or all of the suggested indexes are implemented? For example, Query X suggests ...
1
vote
0answers
197 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
1
vote
0answers
45 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
122 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
110 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
76 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
666 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
884 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
112 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
79 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 ...
1
vote
0answers
57 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 ...
0
votes
0answers
40 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
0
votes
0answers
41 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
0
votes
0answers
11 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
0
votes
0answers
366 views

MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

I have a table area which contains ~300,000 rows. It is defined as follows: CREATE TABLE `area` ( `area_id` bigint(20) NOT NULL AUTO_INCREMENT, `boundary` geometry NOT NULL, `name` varchar(255) ...
0
votes
0answers
15 views

server response is getting slow day by day

MySQL server 5.5 > running in Linux Box is getting slow. MySQL server is being used by Java Web application, please help me to figure out the problem.
0
votes
0answers
48 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
0
votes
0answers
29 views

Minimum Oracle table size making IOT advisable?

I have inherited an IOT that doesn't seem to merit it because: only 25k rows fairly pure join table, non-index fields rarely queried total index width only ~125 bytes total row width only ~160 bytes ...
0
votes
0answers
90 views

Why my mariadb slow when I select all from empty table?

I try to find why my database slow when I select all table . That's empty table... MariaDB [kohadata]> select count(*) from biblio; +----------+ | count(*) | +----------+ | 0 | +----------+ ...
0
votes
0answers
49 views

SQL Server File Latency

I've a Database which is in SQL Server 2012. For the last couple of weeks it's very slow and some times not responding to the queries. So I checked the read/write latency and the result is - Read ...
0
votes
0answers
76 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
0
votes
0answers
133 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
0
votes
0answers
43 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
0answers
48 views

Informatica is causing Convert_implicit in SQL Server 2008

In our project a datafile is loaded into SQLServer using Informatica. The source column is defined in Informatica as UniChar.60 The target column in SQL Server is defined as varchar(60) But when the ...
0
votes
0answers
130 views

Tunning MySQL Variables

we have a dedicated mysql server with 10 CPU and 32G of memory After doing some readings came up with those configs : # This was formally known as [safe_mysqld]. Both versions are currently parsed. ...
0
votes
0answers
66 views

Which of sys.dm_exec_query_stats columns should I pay most attention?

I'm trying to tune my database and currently staring into sys.dm_exec_query_stats (with actual queries): SELECT SqlText.text, * from sys.dm_exec_query_stats CROSS APPLY ...
0
votes
0answers
28 views

MySQL 5.5 only use 1 thread and 30% cpu

i have a big issue, every day i need to update or insert like 3.3 milion rows, i have trying to do what ever i can but it still only run like 605 query/s i use VirtualBox so my setup are testing on 1, ...
0
votes
0answers
101 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 ...
0
votes
0answers
239 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 ...
0
votes
0answers
84 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
165 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
0answers
211 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
139 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
63 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
147 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
224 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
234 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 ...