Questions tagged [benchmark]
The benchmark tag has no usage guidance.
71
questions
0
votes
0
answers
29
views
Performance impact when querying without partition key on a partitioned table
Assuming I partition a table of large size (say 6TB) by month. We are serving very high traffic of around 300 RPS.
Sample schema
Create table mydata(
id int not null,
date_created not null,
uuid_val ...
0
votes
0
answers
83
views
How to calculate hitrate of shared buffer in postgresql, using TPC-C workload?
I use TPC-C(benchmarksql 4.0) to test postgres. I set up warehousenum=1000, the whole database is about 100GB, and I set shared_buffers=10GB in postgresql.conf. I also set terminalWarehouseFixed=false ...
0
votes
1
answer
161
views
pgbench: generating a random padded string
I have the IMDb database in PostgreSQL 16, and I am trying to generate random strings for performance benchmark. The relevant table is title_basics with the primary key column tconst, VARCHAR(9). ...
- 5
0
votes
1
answer
255
views
Can this slow sysbench mysql result be "normal" for consumer hardware?
I have 4 different MySQL 8.0.30 servers on Rocky Linux 9, in one I replaced NVME SSD hoping it will perform better.
I made an image with "Macrium Reflect" software and copied exact same ...
- 195
1
vote
1
answer
33
views
Could cloning / imaging SSD cause MySQL data to be more fragmented?
On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times ...
- 195
0
votes
1
answer
400
views
MySQL | Measure overall query cost (performance, cpu usage, memory usage)
The Problem
You have a MySQL query, let's say a SELECT and you need to identify its overall cost in all aspect (Host Resource Usage, Query speed / performance ...) so that your result can be ...
- 145
0
votes
1
answer
208
views
How to generate each query in a separate SQL file using TPC-DS dsqgen tool?
I'm using TPC-DS dsqgen to translate its templates to runnable queries.
I tried the following argument variations to save each query in a dedicated .sql file, but they all save the runnable query in ...
- 135
0
votes
1
answer
73
views
How to evaluate Database benchmarks ? What to consider given a specific example?
I'm trying to figure out which database to use for a project which is supposed to implement a temporal property graph model and I am looking into some benchmarks for that. I found some papers which ...
- 101
1
vote
0
answers
737
views
Why is this `pgbench` benchmark faster on postgresql 11 than on postgresql 14?
To check postgresql performance, I ran the following benchmark with varying postgresql versions and filesystems:
pgbench --initialize --scale=50 bench
# Run for 3 minutes
postgres pgbench --client=10 -...
0
votes
1
answer
171
views
Does Greg Rahn's "join order benchmark" reflect real world OLAP join scenarios?
Greg Rahn's "join order benchmark" has dozens of inner join queries on the IMDB database.
Does this benchmark and the database itself reflect common real world OLAP join scenarios? How does ...
- 1
3
votes
1
answer
113
views
Why is FROM VALUES massively faster than visiting a table once?
Given this setup,
CREATE TABLE t(foo)
AS SELECT 'foobar'::text;
Why is this query,
EXPLAIN (ANALYZE, TIMING OFF)
SELECT
generate_series(1, 1000000),
upper(foo)
FROM ( VALUES ('foobar') ) AS t(foo)...
- 62.1k
1
vote
1
answer
53
views
Why does a \timing always show faster times when run from within a script than on the terminal?
Let's say I have a very simple command like
SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
If I open up a terminal and I run \timing and then the above query, I'll consitantly get ...
- 62.1k
0
votes
1
answer
846
views
Galera cluster slightly slower than single database?
I recently set up a MariaDB galera cluster for our production. I used sysbench to benchmark the cluster against the old database which is on a single server.
On my PRD Galera Cluster I got the ...
- 33
2
votes
2
answers
2k
views
Postgres REINDEX time estimate
I've got an older DB (postgres 10.15) that's not yet been upgraded. One problematic table had a few large indexes on it, some of which were corrupt and needed reindexing. Since it's not on version 12+,...
- 170
0
votes
1
answer
72
views
How to benchmark Switchable Optimizations in MySQL
I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches.
I have already ...
- 233
0
votes
2
answers
137
views
Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?
Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select?
(assuming unique alphanumeric index and no auto-...
- 101
0
votes
1
answer
148
views
What to expect from a benchmark?
I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration. Meaning, that even before I start tuning PostgreSQL ...
- 123
0
votes
0
answers
53
views
Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?
I'm benchmarking MariaDB 10.4 with TATP. The InnoDB config file has the following parameters set:
[mysqld]
innodb_data_home_dir=/usr/local/mysql/data
innodb_undo_directory=/usr/local/mysql/data
...
- 31
0
votes
0
answers
177
views
Is querying by a PRIMARY KEY in MySQL faster than other queries?
I have two queries that return on a dataset of 200,000 records with apparently the same performance. The first looks up a primary key, and the second a varchar(32) with no special constraints.
Am I ...
1
vote
1
answer
3k
views
Is mongodb upsert the right option for write intensive use case?
I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates ...
- 27
4
votes
3
answers
9k
views
Speed difference between Drop table and Truncate table in Postgres
I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm ...
- 140
3
votes
1
answer
1k
views
How to benchmark disk performance from within SQL Server?
I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted ...
- 133
1
vote
1
answer
856
views
pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)
Problem
I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
- 323
7
votes
3
answers
2k
views
Replay (re-execute) MySQL SELECT queries from a log file
MySQL Benchmarking
I would like to evaluate the performance of a different MySQL instance by re-executing real queries from log files. I am aware of tools like mysqlslap, that produces random and ...
- 221
3
votes
0
answers
213
views
Automating performance tests in PostgreSQL
I want to check the performance of specific queries in a Postgres database on a regular basis. Also, I want to be able to check the change in performance of different queries with any change in my ...
- 31
0
votes
3
answers
873
views
SQL Server memory benchmarking - how to fill up memory?
Recently I was asked by my VMWare colleague how to cause a (non-production and bare install) SQL Server to use up the memory of a machine.
The solution I found in this forum post does not cause the ...
- 66
3
votes
1
answer
926
views
How to benchmark query parsing & planning in Postgres?
I'm trying to benchmark the query planning and parsing steps in PG:
However, it seems that the query planner is using some sort of cache - only the first execution of EXPLAIN ANALYZE seems to report ...
- 323
2
votes
2
answers
46
views
Benchmarks for custom database
I am looking for an open source benchmark tool that can easily be extended to custom databases. The reason is I have created a new NoSQL DBMS (called Borrego) that support both JSON queries like ...
- 131
-1
votes
1
answer
1k
views
Benchmarking Mongodb Queries Without cache
I want to benchmark some Mongodb queries by calculating their execution time but I could not find a way to do so without caching : The first time I execute a query it takes a decent amount of time and ...
- 1
5
votes
2
answers
4k
views
How do I get the Query Timing with sqlcmd?
Running SQL Server 2017, using the interactive REPL client sqlcmd how do I get the time for Query Execution?
You can sp_BlitzErik showing this in his answer here,
SQL Server Execution Times:
CPU ...
- 62.1k
1
vote
0
answers
261
views
Understanding query variance in exec time in PostgreSQL?
I want to execute a set of queries many times through psql and to study running/execution time of each query. Of course, I want to run them many times to reduce effect of external factors through ...
- 233
19
votes
2
answers
18k
views
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
- 62.1k
0
votes
1
answer
4k
views
Measuring IOPS in SQL 2014
We have a LANDEsk migration going on where several servers are being consolidated into one SQL 2014 Cluster. What would be the best way to calculate IOPS for such a scenario? The data is still being ...
- 625
1
vote
3
answers
626
views
Any script to auto test tune and benchmark mysql config?
I'm looking or willing to do a script to benchmark mysql config variations for a specific system.
A script that would change certain mysql config values and do a sysbench, save results, change ...
- 11
1
vote
1
answer
203
views
Is it necessary to create primary and foreign keys in order to use tpc-h benchmark?
I'm doing some tests with tpc-h benchmark. The tpch-h benchmark simulates a data warehouse environment. The most important things here are the queries against the data warehouse. Therefore, does it ...
- 101
1
vote
0
answers
98
views
Slow query speed using workstation processed joins with 100 Mbps connection speed?
In a department where they were complaining that a report was being run slowly, I jumped on one of the empty machines there and tried it out. It took a good 6 and 15 seconds to run.
Run from a ...
- 1,702
3
votes
1
answer
20k
views
get query execution time in command prompt [duplicate]
I'm doing this query select * from rentals. This table has more than 10,000 rows and it takes more than 4 minutes to retrieve the results.
But I get this time after I get the results: 10000 rows in ...
- 33
0
votes
1
answer
88
views
Perfmon Counters that relate to SQLIO
I am trying to benchmark a new virtual SQL server. this will be the first time I have virtualized a production SQL server. I don't think this is a supper busy server. 100-200 users, of varying ...
0
votes
1
answer
658
views
Tuning postgresql.conf when benchmark
Description:
We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here is our detailed information:
Operating System: Centos 6 x64
...
- 1,942
1
vote
1
answer
898
views
MySQL Read/Write Benchmark
I'm trying to benchmark Read/Write speed in MySQL 5.5. Currently I have a stored procedure that insert rows with random numbers to a table and I'm measuring time of execution before and after ...
3
votes
1
answer
2k
views
Are there any known benchmarks/statistics on UnQLite?
I came across UnQLite the other day in Stack Overflow and it has me intrigued for some software I'm working on. However, very few tests have been published on how well it performs. There are plenty of ...
- 131
1
vote
3
answers
1k
views
How to tell MySQL to ignore all indexes for a given query? [duplicate]
I've been benchmarking several queries and learning about indexes by using the explain function, profiling and the SQL_NO_CACHE option.
My usual benchmarking session goes something like this:
SET ...
- 123
0
votes
0
answers
51
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.
...
- 1,523
0
votes
1
answer
2k
views
Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?
Problem
Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark.
The VM and db's are both fresh installs from vendor apt repos with default configs.
...
- 101
1
vote
0
answers
439
views
TPC-DS data generation
Is there a way to generate TPC-DS/TPC-H data using Normal Distribution, i.e., is there a way to specify the distribution type while generating TPC-DS/TPC-H?
EDIT : I believe there is an option for ...
- 271
1
vote
1
answer
482
views
stress-testing MySQL slave with master's queries
A MySQL host in a slave configuration is in the testing phase before being put into production as a primary server for a specific workload. To make an estimate about how it is going to perform, the ...
- 485
1
vote
0
answers
806
views
Sysbench insert data between a given range
I was trying to insert 1,000,000 records of data into 1,000 tables in sysbench. But after installing about 278, sybench just crashed with the following error:
FATAL: failed to execute function `...
- 333
0
votes
1
answer
1k
views
pgbench-tools failed authentication
I'm trying to configure pgbench-tools and went to run it per the README and got this error
-bash-4.2$ ./newset 'Initial Config'
psql: FATAL: Ident authentication failed for user "postgres"
psql: ...
- 1
0
votes
1
answer
444
views
Running identical queries in sysbench
When benchmarking using sysbench and utilizing concurrency (multiple threads) for example on a table with 1,000,000 records, does sysbench run "identical" queries in a loop on the table?
- 333
0
votes
0
answers
2k
views
Benchmarking MyISAM vs InnoDB using Sysbench on MariaDB-10.0.14
I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used:
Create test table in ...
- 333