Improving the performance characteristics of a database application or system.

learn more… | top users | synonyms

1
vote
1answer
51 views

Tuning MySQL query from explain

I am working on a search query in MySQL. I am getting this from explain: Original screenshot These are my indexes: Table definition: Original screenshot Query: SELECT `t`.`id` AS ...
3
votes
0answers
26 views

Postgres 4x Slower Than It Was

Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why. We have two machines with identical hardware (let's call them A and B): Intel(R) Xeon(R) CPU E5-4640 0 @ ...
2
votes
1answer
127 views

Dropped Index during Database Engine Tuning Advisor

I was analyzing a query using "Database Engine Tuning Advisor". During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after ...
0
votes
2answers
45 views

Finding the longest entries for a given column (nvarchar(100))

I am trying to find the biggest / longest entries of a column which is of type nvarchar(100). I need to understand if I can maybe restrict that column to be only nvarchar(20) or something. Is there a ...
2
votes
1answer
51 views

MySQL (MariaDB - 10.0.16-MariaDB-1 (Debian) strange performance issues

I am running version 10.0.16-MariaDB-1 (Debian GNU/Linux 8.1 (jessie)) on Dual Xeon CPU, total 32 cores with 128 GB of RAM. This is a dedicated DB server and has been recently migrated over from ...
2
votes
1answer
12 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
2
votes
0answers
33 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, ...
0
votes
1answer
55 views

NC Index not being used [closed]

I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it). I'm wanting to count how many records meet the following criteria: ...
4
votes
1answer
40 views

How to increase performance in Cross Database operations?

We currently have a SQL Server 2008R2 being accessed by a legacy ASP application. Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into ...
1
vote
1answer
19 views

Accurate PG database size for comparing representations

I have a Java/JDBC program that takes a sample file and imports the data into the database, shredding it across multiple relations. The program does this multiple times for several different ...
0
votes
0answers
50 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 ...
2
votes
2answers
37 views

Setting shared_buffers in postgresql.conf does not seem to take effect

We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.) In postgresql.conf, we have: shared_buffers = 4096MB kernel shm values are set nice and high: [root@green ...
0
votes
1answer
32 views

Feasibility of Automated and Continuous Performance Tuning in the future [closed]

First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, ...
6
votes
1answer
111 views

Disk I/O and PAGEIOLATCH_XX

We have been having CPU issues with one of our servers lately and while we have been looking into this we have also noticed queries running slowly with waits of PAGEIOLATCH_XX. In particular, a ...
0
votes
1answer
148 views

Different execution plans during INSERT at @table variable and #temporary table

I faced with strange situation: inserting of ~800 000 rows into table variable is much more faster than inserting (with the same select) into temporary table. INSERT is under stored procedure. SELECT ...
1
vote
0answers
78 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
2answers
55 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
1
vote
1answer
69 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
84 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
24 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 ...
2
votes
1answer
66 views

Postgresql performance issues when issuing many small inserts and updates one at a time

I have a production web server with postgresql database. My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in ...
0
votes
1answer
68 views

Scale MySQL trigger for thousands of inserts per seconds

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256) A row ...
1
vote
1answer
53 views

Why MySQL SELECT performance depends on table size? [closed]

I've got two identical tables with the following structure: CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT ...
1
vote
1answer
93 views

High Page Faults/sec low Page Reads/sec

I currently have Page Faults/sec avergaing 1269 while my Page Reads/sec is averaging 0.3. I am just trying to interpret what this means. I assume I am getting lots of soft page faults in this case ...
0
votes
0answers
26 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 ...
3
votes
2answers
97 views

How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings

Our production server is running SQL Server 2008 SP3. I’ve read a post on “cost threshold for parallelism” - ...
0
votes
0answers
55 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
51 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 ...
1
vote
1answer
89 views

Are scripts like sp_BlitzIndex and the others accurate on restored backups?

I am a developer trying to get a better grasp of things. I am wondering if using the scripts from BrentOzar.com will work on restored backups? For example if I have the DBA give a backup of ...
0
votes
1answer
28 views

Uniq join 3 tables with relationship table not uniq

I am using Postgres. I have 3 tables: table_1 id table_1_2 table_1_id, table_2_id table_2 id The problem is the relationship table table_1_2 is not uniq. I.e., table_1_2 will have the ...
1
vote
0answers
74 views

High performance SQL Server - Poweredge R730xd [closed]

Last year I wanted to move our critical SQL Server solution from Virtual to physical. I liked the Poweredge R720 with it's 2 RAID controllers and favoured filling it with 2.5" Samsung EVO SSDs - not ...
0
votes
1answer
91 views

How to speed up mysql query against a 204 million record table

For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running shell# watch -n 5 free -m without running any activities ...
0
votes
0answers
17 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, ...
1
vote
2answers
801 views

Optimizing queries for 25+ million rows

I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
0
votes
1answer
111 views

Optimizing query for large number of rows in mysql

I have the following database table in MySQL : and is running the following query SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, ...
0
votes
1answer
43 views

Good encryption performance

I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow. I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table). ...
0
votes
1answer
150 views

Extremely long startup time for MongoDB server

I have a MongoDB Server with just over 2 million documents in one collection. Individual documents can contain large amounts of data (some are several megabytes each) but none of that data is in ...
0
votes
1answer
228 views

Large MySQL operations slowing down SELECT queries on unrelated tables

On a fairly busy SQL server, we sometimes need to do large index-building operations (after importing ~6GB tables). The indexes are built using the more efficient "Repair by sorting" method, rather ...
3
votes
2answers
111 views

What perfmon counters should I use to get the fastest restore possible on an idle system?

I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk. I want to restore database as ...
0
votes
0answers
32 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
75 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
114 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
48 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 ...
1
vote
1answer
52 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 ...
0
votes
1answer
43 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
0answers
46 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
2answers
177 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 | ...
2
votes
1answer
75 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 ...
4
votes
1answer
190 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
37 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 ...