We have a massive MS SQL Server production environment with thousands of tables in dire need of index tuning & pruning. Any suggestions on the best ways to test proposed changes outside of production? Obviously, we can (and do) stand up a DEV database server where we can make changes, but that DEV copy won't reflect the real-world demands being made on the tables. We also have a production mirror that uses transaction log shipping, but mirrors are always read-only (unless you fail them over to primary), so we cannot test index changes there. **Is there any way to ship transaction logs to an instance that isn't a read-only mirror?** Obviously that could encounter problems, especially if schema changes are made in the test environment. Even with only index changes, you could have query timeouts and other inconsistent behavior. But that's exactly what we are looking to do: Given a set of index changes, how do real-world queries, under real-world load, perform? Are they faster? Do they fail? A few test queries won't suffice. We run all kinds of nightly, weekly, and monthly processing jobs that hit the database hard. We also have a lot of external scripts and services that query and update the database in ways we don't always anticipate. Given the scope of the changes that are needed, I am very hesitant to make any live production index changes without verification.

I want to have a load test on my postgresql database named dvdrental.While using postgres default parameters in postgresql.conf file , my database performance is pretty better than other situations.My node is about 8GB of RAM and 4 cpu cores. The query I used for pgbench is in a file named sql_query.sql : SELECT account.first_name, s1.time as time, random() * (100-0) + 0 AS cpu_usage, random() * (30-26) + 26 * 1024 AS average_mhs, random() * (90-50) + 50 AS temperature, random() * (100-0) + 0 AS fan FROM generate_series( '2018-10-14', '2021-10-15', INTERVAL '1 day') AS s1(time) CROSS JOIN( SELECT account_id, first_name, last_name FROM account limit 100 ) account ORDER BY account.account_id, s1.time; While account is a table in dvdrental database with account_id , first_name and last_name columns. So the command I use for pgbench is : pgbench -f sql_query.sql -n -r -c 50 -T 400 -j 4 dvdrental The result I get is: pgbench (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1)) transaction type: sql_query.sql scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 4 duration: 400 s number of transactions actually processed: 3038 latency average = 6497.161 ms initial connection time = 79.758 ms tps = 7.695669 (without initial connection time) statement latencies in milliseconds: 6210.604 SELECT then I tried to change parameters to : shared_buffers = "2GB", effective_cache_size = "6GB", maintenance_work_mem = "512MB", checkpoint_completion_target = 0.9, wal_buffers = 16MB, default_statistics_target = 100, random_page_cost = 1.1, effective_io_concurrency = 200, work_mem = "20971kB", huge_pages = off, min_wal_size = "1GB", max_wal_size = "4GB", max_worker_processes = 4, max_parallel_workers_per_gather = 2, max_parallel_workers = 4, max_parallel_maintenance_workers = 2; And I get this result: pgbench (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1)) transaction type: sql_query.sql scaling factor: 1 query mode: prepared number of clients: 50 number of threads: 4 duration: 400 s number of transactions actually processed: 2838 latency average = 7114.727 ms initial connection time = 409.796 ms tps = 7.027676 (without initial connection time) statement latencies in milliseconds: 7088.570 SELECT Even changing any parameter does not helped.

What tools or best practices exist to demonstrate a new server instance is "as capable" as an older one? We're moving from an EC2 hosted instance of MS SQL Server to an RDS instance, and have been tasked with "proving" that the new platform won't cause any regressions. The naive idea is to somehow replicate a day's worth of load, and record the elapsed time for all the queries. Then, if the worst 0.1% of the queries run no slower, we're good. Is that logic good, or is there a flaw, or is there a better/standard way? What would I need to do on the old server to "capture" a day's worth of load? (Queries, parameter values, session id so that I can simulate the correct number of concurrent connections, login name, elapsed query time, etc). What else am I missing? (Another niave idea was to put a "load balancer" on to "replicate" the queries to both instances, and log the query times. Is that even possible?)

I am troubleshooting query performance issues when running large queries against an AWS Redshift cluster. The majority of the time is spent transferring the data over the network and processing the data in the application. I am not concerned with the amount of time it takes the Redshift cluster to execute the query and start returning data. I am trying to devise a test where I can measure and quantify the maximum throughput of the Redshift cluster over the AWS network to the EC2 instance I'm testing on. I want to discard the result set as quickly as possible as to not add any additional overhead. The end result will be to compare the maximum throughput of the cluster and network, to the performance I am seeing in the application processing the data. Ultimately, this will allow me to better understand where my bottlenecks are and what maximum target I can achieve from Redshift. I already know that the end to end processing speed of the data (33MB/s) seems well below the network and Redshift potential. I want to quantify the upper limit of that throughput. Is there a mechanism or tool through which to do this? I can use Windows or Linux to do this. I would prefer to use an ODBC connection to closely simulate the application and existing driver. Or any other method for which to measure what I am asking about? I've considered jMeter, but I'm not sure this is the most direct and simplest approach. I also tried using SQL Server Import and Export Wizard, but it's really stinking slow and clearly not pushing the network or cluster and it also has additional overhead to save the data to disk.

In my database I have the following a materialized view named materialized_view_test and the data are being feeded from 3 tables:

select 
 *
from
 a_table join b_table on b_table.a_id=a_table.id
 join c_table on b_table.c_id=c_table.id

As you can see the b_table is a pivot table for a_table and c_table. Also my database for testing purpoces has a master-slave replication with the slave to be on a hot stanby replication mode. I try to stress my database in order to investigate which configuration on the following parameters is the optimal:

max_standby_streaming_delay
max_standby_streaming_delay
hot_standby_feedback

So I continiously run the following queries on my database over an infinite loop (for convenience lets name the test as *test1*): 1. Set 1:

DELETE FROM b_table where a_id=12 and c_id=33
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

2. Set 2:

INSERT INTO b_table(a_id,b_id) VALUES (12,33);
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

And I toggle between 2 sets into my database by checking a counter whether is odd or even, using a custom script. Afterwads, I read from the materialized view in a purpocely non-optimal written query in order to cause a load:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM b_table join b_table as b_table12 on btable.b_id=b_table12.b_id
   ) as dummy
) as dummy2

But in my database I rearely do any deletes. So I though if I was continiously toggling a boolean flag over an infinite loop in one of my tables (for convenience lets name the test as *test2*):

UPDATE a_table set some_flag = NOT some_flag where a_id=12;
VACUUM (VERBOSE) materialized_view_test;

And then continiously sellecting over it in a infinite loop:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM a_table join a_table as b_table12 on btable.a_id=b_table12.a_id
   ) as dummy
) as dummy2

So does in my situation the test1 can have same results and effects as running into test2 or I need both tests in order to test my replication schema.

I have the following database replication setup: As you can see I do a master-slave replication and I use aload balancer in order to connect into read-only replicas. So I want to test this setup, hence I connect into my database into postgresql read-only replicas via Load Labalcer via psql command. But how I can find out which replica I am connected into? I need that in order to perform a stress test.

We are planing to test the performance of the new DB server. For that we have the below the plan: 1. get production sql queries 2. replay those sql queries against the different DB I was planing to use pt-log-player to play the sql queries and use the pt-query-digest to analyze the result files which gets generated from pt-log-player after execution. So my questions are: 1. how to get the production query in the first place. enabling slow log query is not an option due to performance reason (as our DB admin denied about that) 2. Why pt-log-player is deprecated, was there any bug or whats the reason behind. 3. Or someone can suggest me a complete different toolset to do the perf test. Thanks.

I am testing the performance of an application that links to a DB2 database engine at the back end. Looking at the transaction timer logs, I see that on the 37'th minute into the test run, transactions take a very high time to process. This lasts for maybe a few seconds and then the run proceeds at acceptable speed. I've done three test runs (restoring the data to initial test data, clearing up everything = basically, ensuring each run has the same starting position) and they all experience the same phenomenon: at the 37'th minute, a performance spike:

My current lead is that I notice, on the second run it's more visible, that every 37-ish minutes, there's a "minispike". I think what's going on is the DB2 is doing some regular maintenance every 37 minutes, and the first maintenance job is a bit harder, as the machine hasn't cached a required program or...? I am wondering what advice can I get to investigate this further? Debugging my own application did not show any reason to hang as it does there.
Info:
DB: DB2 10.5.0.7
OS: SLES 12 0
App: Precompiled to bind to DB2 in 64-bit mode

I'm using Locustio service as load testing tool for my app. Without details, I'm using the same aggregation query with simulated 100 users, in this case RPS (requests per second) is about 20. In first 30 seconds every query time is about 10-50ms, but then (when about 65 users active) it increases to 500-1000ms. I'm using MMAPv1, so query is cached for sure, so the question is: why query execution time is increasing? Of course, it's only test case, in real I'm doing different queries, but in that case I wasn't sure that all the queries was cached. That's why I decided to test It only with one query. Sorry for my bad explanation. I think the problem is in connection number, still it's only 100. How cna I optimize the performance of my app? __UPDATE1__ db.serverStatus about connections number: "connections" : { "current" : 20, "available" : 51180, "totalCreated" : NumberLong(32) }, All of my engine settings are defaults This is my db.collection_name.findOne() part, which involved in aggregate query: { "_id" : ObjectId("571764be1e31c8556e80b589"), "ppl_ids" : [ 19, 1, 20, 21, 22, ], } The pipeline of aggregate query itself: {$match: {ppl_ids: {$in: [16,1245,592] } } } The part of db.collection_name.getIndexes(): { "v" : 1, "key" : { "ppl_ids" : 1 }, "name" : "ppl_ids_1", "ns" : "dbname.collection_name", "background" : false },

I'm performing some load tests on my brand new server Apache/PHP/Mysql (Bitnami LAMP stack ). I'm using AWS with 1 EC2 instance behind a loadBalancer. At the moment I'm using loader.io for the tests. I'm testing a very simple API that perform this query: select *,sleep(0.5) from debug limit 1 I added the sleep(0.5) because I wanted to see how the server behave with multiple concurrent connections and I found a bottleneck: if I run "SHOW PROCESSLIST" I can see only 5 process even if I have 10 concurrent user. The load test show that the connections are queued because the response time is growing during the test from 500 milliseconds to several seconds (depending on the duration of the test and the number of concurrent users). I checked select @@max_connections and it's 151 (the default). max_user_connections is 0. What other parameter should I check to increase the number of concurrent connection on my DB? If I run the test with 5 concurrent users, each one get a response in 500 milliseconds. If I add more concurrent users than the response time slow down. If I run the load test on an API that does not access the DB there are no issues even with 400 concurrent users. Monitoring with HTOP I see: > Tasks: 34, 245 thr; 2 running Could be here the issue? Thanks a lot

I'm trying to generate workload from our production databases but rather than running a trace file in production, I would prefer using the AWR tables since we have a license for it. Is it possible? Thank you.