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 not null, Primary key (id,date_created), key uuid_idx uuid_val) Partition by range(to_days(date_created) What will be the performance impact if I query on this table without date_created in the where clause. It will not give a better performance for sure as it cannot do partition pruning but what will the impact if I query it based on index uuid_val as compared to a non-partitioned table with same schema? I tried benchmarking it with 6GB data and querying(500,000 times) by index and not partition key the overhead to search in multiple partitions seemed very less (0.1ms) as compared to running the same query on the non-partitioned table. But why is the overhead so less. Also can I expect such numbers with 6TB data too when serving high traffic? Can I query partitioned table without partition key as I might not have in the flow. My MySQL version is 8.

Before upgrading AWS RDS PostgreSQL version at production, I want to replay 1-hour recorded logs at the test instance. Unfortunately [pgreplay](https://github.com/laurenz/pgreplay) is single-threaded application, limited by single CPU speed and its replays are failing with some weird errors after a few minutes working. On the other side, [pgreplay-go](https://github.com/gocardless/pgreplay-go) requires a different input log format: log_line_prefix='%m|%u|%d|%c| RDS csvlog sample:

2021-09-09 17:00:00.006 UTC,"user","database",27752,"172.30.1.2:34106",613a286d.6c68,13992,
"SELECT",2021-09-09 15:29:49 UTC,229/3866470,0,LOG,00000,
"execute : SELECT ""jobs"".* FROM ""jobs"" WHERE ""jobs"".""deleted_at"" IS NULL
AND ""jobs"".""user_id"" = $1","parameters: $1 = '124765'",,,,,,,,"bin/rails"

pgreplay-go log sample:

2010-12-31 10:59:57.870 UTC|postgres|postgres|4d1db7a8.4227|
LOG:  execute einf"ug: INSERT INTO runtest (id, c, t, b) VALUES ($1, $2, $3, $4)
2010-12-31 10:59:57.870 UTC|postgres|postgres|4d1db7a8.4227|
DETAIL:  parameters: $1 = '6', $2 = 'mit    Tabulator', $3 = '2050-03-31 22:00:00+00', $4 = NULL

Should I just convert one RDS csvlog line into two pgreplay-go lines and that's it? Log parsing logic in pgreplay-go is not trivial as you can see at https://github.com/gocardless/pgreplay-go/blob/master/pkg/pgreplay/parse.go#L220 So I'm not sure that just split of the one line into two lines will be sufficient. Should I add something else maybe?

First, I'm asking this question because I'm studying to be a DBA. Recently I learned a lot about database locking, and then some questions arouse in my mind. Suppose I'm a DBA, I'm in charge of a huge database from a online sales website. Some users reported that the site is slow, and my boss asks me to test the most used queries to see if they are running smooth or not. If I get locking right, if I just run an EXPLAIN ANALYZE on our tb_products I would probably lock it and it can have a negative impact on sales being processed at that time (i.e. some users could not buy their products because I'm "testing" queries). My question is: how can I properly tests queries in a production database? In my (probably naive) way of thinking, what I need to do is to dump the database and its configurations, create a "sample" database with that and do the tests there. But them, if I'm testing locally on my machine, even with the same configs, numbers will differ, as we're testing on another hardware that's not the server hardware, right? I can't depict how can this tests be done when I think of it. It is probably a silly question, but any help to clarify that would be appreciated.

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'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:

autovacuum_vacuum_cost_delay   = 0     #Turn off cost based vacuum
autovacuum_vacuum_cost_limit   = 10000 #Max value
autovacuum_vacuum_threshold    = 50    #Default value
autovacuum_vacuum_scale_factor = 0.2   #Default value

I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane! Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load. Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load? **Update** Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.

I am trying to load test a simple insert stored procedure: CREATE TABLE _test(ID BIGINT) GO CREATE OR ALTER PROCEDURE dbo.test_sp AS BEGIN SET NOCOUNT ON; BEGIN INSERT INTO _test SELECT CAST(RAND() * 10000 AS BIGINT) END END When I execute this stored procedure with the SQL Stress tool, I get *SQL Compilations/sec* equal to *Batch Requests/sec*. Interestingly, *SQL Re-Compilations/sec* is zero. Both *optimize for ad-hoc workloads* and *forced parameterization* are enabled. The picture is the same even if I change my procedure to a simple SELECT 1. I am using Microsoft SQL Server 2016 (SP3) (KB5003279). A profiler trace shows the tool sends a simple EXEC dbo.test_sp

I am conducting a performance benchmark of parameters with numeric values (i.e., innodb_buffer_pool_size, max_heap_table_size, query_cache_size, tmp_table_size) on MariaDB (version 10.11.2) using sysbench. My aim is to observe which parameters influence the performance overall. To establish this, I am using different numeric values of the parameters (i.e., different configurations of MariaDB). However, I found that none of the previously mentioned parameters does affect the performance when executing sysbench, which is supicious. In my experiments, I use one dedicated machine acting as the server and one dedicated machine acting as a client. Both the server and the client are connected via one single network switch (1GBit/s) and use a minimal installation of Debian 11. On the client, I use the oltp_read_write.lua workload from sysbench using 50000 events, 4 threads, and set time to 0. Further, my configuration file for the server is as follows:

[mysqld]
bind-address=0.0.0.0
basedir=
datadir=
skip_name_resolve=ON
server_id=1
max_connections=100
innodb_buffer_pool_size=64M
max_heap_table_size=16M
query_cache_size=0
tmp_table_size=1M

Note that I vary only the values of innodb_buffer_pool_size, max_heap_table_size, query_cache_size, and tmp_table_size throughout my experiments; all other parameters are set to their default value. I use the following different values for these parameters: - innodb_buffer_pool_size: 64M, 128M, 192M, 256M, 320M - max_heap_table_size: 1M, 16M, 32M, 48M, 64M - query_cache_size: 0, 16M, 32M, 48M, 64M - tmp_table_size: 1M, 16M, 32M, 48M, 64M I chose these small values to increase the chance of observing changes in the runtime behavior of the server in my small setup. While benchmarking, I observed a CPU load of about 25% on the server. In ensured that no other services are running except for the server or client process. Furhter, I measure the time needed until the sysbench benchmark is completed. I repeated my measurements for each configuration 5 times and had a relative standard deviation of about 0.1%. The configuration above has a runtime of about 131 seconds. Varying the values of these parameters ends up in a runtime of about 130 seconds; I only observe very slight deviations, but nothing really relevant. I expected to get larger runtime changes. I ensured that the configuration files are correctly created and used; tests with invalid values of these parameters lead to errors while starting the server. Am I missing something important while performing the benchmarks? Or are other parameters with numeric values of more relevance?

We have an onpremise production database . We are planning to move it to cloud. What types of Performance testing can be done ? 1. Is it okay to start with stress testing first? 2. Shall I start with load testing and then do stress testing? 3. Can I do distributed testing for both load and stress test? I am looking for a right approach to do performance testing on 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 MongoDB and DB2 JSON, and generic SQL statements via a JDBC driver. I would like to run some performance tests that I can compare with other existing databases. Any pointers would be helpful. Thanks

Database Application: Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL) Server: Ubuntu 18.04.6 RAM: 32GB CPUs: 8 core The underlying application framework is Drupal and it uses a query builder UI (Views module) to generate queries for reports. Please find the non performant query below. Without the join to the flagging table the query executes under few seconds. I have improved the query based on suggestions by @Rick James and @mustaccio. The query time still exceeds 4 minutes when joined with flagging table.

EXPLAIN SELECT 1 AS expression
FROM
node_field_data node_field_data
LEFT JOIN flagging flagging_node_field_data ON node_field_data.nid = flagging_node_field_data.entity_id AND flagging_node_field_data.flag_id = 'verify_blood_group'
LEFT JOIN node__field_date_of_collection node__field_date_of_collection ON node_field_data.nid = node__field_date_of_collection.entity_id AND node__field_date_of_collection.deleted = '0'
LEFT JOIN node__og_audience node__og_audience ON node_field_data.nid = node__og_audience.entity_id AND (node__og_audience.deleted = '0' AND node__og_audience.langcode = node_field_data.langcode)
WHERE ((node__og_audience.og_audience_target_id IN('30', '229', '5026', '60887', '198081', '350754', '519498', '519499', '566913', '568976', '571016', '642633', '739096', '769874', '770003', '800588', '1051756', '1056092', '1101838', '1465616', '1730929', '2045068', '2269366', '3535017', '1836317', '3387310', '9900000'))) AND ((node_field_data.status = '1') AND (node_field_data.type IN ('donation_record')) AND (node__field_date_of_collection.field_date_of_collection_value BETWEEN '2022-08-27' AND ('2022-09-02' + INTERVAL 1 DAY)));

Please see the Query Explain below.

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node__field_date_of_collection
   partitions: NULL
         type: range
possible_keys: PRIMARY,field_date_of_collection_value
          key: field_date_of_collection_value
      key_len: 82
          ref: NULL
         rows: 22808
     filtered: 10.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: node_field_data
   partitions: NULL
         type: ref
possible_keys: PRIMARY,node__id__default_langcode__langcode,node_field__type__target_id,node__status_type
          key: PRIMARY
      key_len: 4
          ref: ebloodbanking8.node__field_date_of_collection.entity_id
         rows: 1
     filtered: 5.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: node__og_audience
   partitions: NULL
         type: ref
possible_keys: PRIMARY,og_audience_target_id,og_audience_entityid_deleted_langcode_value
          key: PRIMARY
      key_len: 5
          ref: ebloodbanking8.node__field_date_of_collection.entity_id,const
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: flagging_node_field_data
   partitions: NULL
         type: ref
possible_keys: flagging_fid_etid,flagging_fid_uid_etid
          key: flagging_fid_etid
      key_len: 34
          ref: const
         rows: 388428
     filtered: 100.00
        Extra: Using where; Using index

Please find the flagging table describe:

| flagging | CREATE TABLE flagging (
  id int unsigned NOT NULL AUTO_INCREMENT,
  flag_id varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  uuid varchar(128) CHARACTER SET ascii NOT NULL,
  entity_type varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  entity_id varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  global tinyint DEFAULT NULL,
  uid int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  session_id varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  created int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY flagging_fid_etid (flag_id,entity_id),
  KEY flagging_fid_uid_etid (flag_id,uid,entity_id),
  KEY flagging_type_fid_etid (entity_type,flag_id,entity_id),
  KEY flagging_type_fid_uid_etid (entity_type,flag_id,uid,entity_id)
) ENGINE=InnoDB AUTO_INCREMENT=2135664 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='The base table for flagging entities.' |

Show create for the table node__field_date_of_collection

| node__field_date_of_collection | CREATE TABLE node__field_date_of_collection (
  bundle varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  deleted tinyint NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  entity_id int unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  revision_id int unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
  langcode varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
  delta int unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  field_date_of_collection_value varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'The date value.',
  PRIMARY KEY (entity_id,deleted,delta,langcode),
  KEY bundle (bundle),
  KEY revision_id (revision_id),
  KEY field_date_of_collection_value (field_date_of_collection_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Data storage for node field field_date_of_collection.'

Show create for the table node__og_audience

| node__og_audience | CREATE TABLE node__og_audience (
  bundle varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  deleted tinyint NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  entity_id int unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  revision_id int unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
  langcode varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
  delta int unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  og_audience_target_id int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  PRIMARY KEY (entity_id,deleted,delta,langcode),
  KEY bundle (bundle),
  KEY revision_id (revision_id),
  KEY og_audience_target_id (og_audience_target_id),
  KEY og_audience_entityid_deleted_langcode_value (entity_id,deleted,langcode,og_audience_target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Data storage for node field og_audience.'

Show create for the table node_field_data

| node_field_data | CREATE TABLE node_field_data (
  nid int unsigned NOT NULL,
  vid int unsigned NOT NULL,
  type varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  langcode varchar(12) CHARACTER SET ascii NOT NULL,
  status tinyint NOT NULL,
  uid int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  title varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  created int NOT NULL,
  changed int NOT NULL,
  promote tinyint NOT NULL,
  sticky tinyint NOT NULL,
  default_langcode tinyint NOT NULL,
  revision_translation_affected tinyint DEFAULT NULL,
  PRIMARY KEY (nid,langcode),
  KEY node__id__default_langcode__langcode (nid,default_langcode,langcode),
  KEY node__vid (vid),
  KEY node_field__type__target_id (type),
  KEY node_field__uid__target_id (uid),
  KEY node_field__created (created),
  KEY node_field__changed (changed),
  KEY node__status_type (status,type,nid),
  KEY node__frontpage (promote,status,sticky,created),
  KEY node__title_type (title(191),type(4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='The data table for node entities.'

Please find some of the relevant database variable settings.

[mysqld]
default-storage-engine=InnoDB
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 8M
thread_cache_size = 8
interactive_timeout = 60
wait_timeout = 60 # Time in seconds
connect_timeout = 10
max_connect_errors = 10000
tmp_table_size = 32M
max_heap_table_size = 32M

# InnoDB Settings
innodb_buffer_pool_size=18G
innodb_flush_log_at_trx_commit=2
#Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=6G
innodb_log_buffer_size=64M
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=8
innodb_stats_on_metadata=0
innodb_lock_wait_timeout=100
innodb_write_io_threads=8

Please share what changes can be made to make this more performant.Indexes have been added to flagging table. Please share monitoring tools that can help us understand the problems better, database global variable changes that can make this query execution faster. Thanks. **Note**: As suggested by Rick James, changing the data type for column **entity_id** in flagging table from varchar to unsigned int resolved the query performance during joins. Thanks.

Does anyone know of any existing tools/products that accomplish what I'm trying to do? After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is someone else looking for the same thing http://forums.mysql.com/read.php?24,192250,192250 The idea is I'd like to capture all the traffic to my master to save for a replay log against a snapshot of the entire database taken when the monitoring started. Bin logs won't serve what I want since they only include writes. I want read activity to realistically view the effects of proposed changes with "real" production traffic. Real production traffic meaning everything from all applications that are hitting the database to be modified. If there's some application level change, tests running just that app don't account for other activity going on in the system at the time. Running all applications in a test environment aren't guaranteed to have the same state of the database. I could take a test snapshot as a starting point as I fire them up but the applications don't have the ability to do an exact replay of their own activity. I've use the tcpdump script from http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ to monitor activity but this doesn't tell me which queries are coming from which connections. Part of the playback I'm wanting is a multi threaded approach that replays the activity from the same number of threads that were actually in use. I can't afford to turn on general query logging b/c my production master wouldn't be able to handle the performance hit for that. The whole snapshot part of the process is to have a golden start point database to ensure everything is the same during the start of each test run.

The task was assigned to me to check the performance of uploads and query execution separately for "read replica" turned *on* and turned *off*. Surprisingly when "read replica" was turned *on* the performance was better. Note that in both tests, made sure that DB is freshly refreshed. Coming to HA in Postgres, I have verified that the process of archiving WAL logs is happening in DB irrespective of read replica enabled or disabled(I feel it is obvious based on the parameter). Now my question is how is enabling read replica improved the performance of DB activities as a whole. Also, *checkpoint* switching seems similar for both tests, can you put some light on this, please?

I have a dedicated machine with intel i5, 8GB RAM, 7200RPM HDD with Ubuntu 18.04 OS. I have installed only MySQL-5.7.22 in it, see the configuration file's content at the end of this question. I downloaded sample employees database to test the performance of MySQL using mysqlslap. (See How to measure MySQL query performance with mysqlslap ). **On the dedicated Ubuntu:** The following command took 6 seconds to return the result while the result says the maximum number of seconds to run all the queries require 0.286 seconds: mysqlslap --user=root --password --host=localhost --auto-generate-sql --verbose Average number of seconds to run all queries: 0.286 seconds Minimum number of seconds to run all queries: 0.286 seconds Maximum number of seconds to run all queries: 0.286 seconds Number of clients running queries: 1 Average number of queries per client: 0 Similarly, the following command took 1 minute and 53 seconds to return the result while the result says the maximum number of seconds to run all the queries require 1.576 seconds: mysqlslap --user=root --password --host=localhost --concurrency=50 --iterations=10 --auto-generate-sql --verbose Average number of seconds to run all queries: 1.405 seconds Minimum number of seconds to run all queries: 0.760 seconds Maximum number of seconds to run all queries: 1.576 seconds Number of clients running queries: 50 Average number of queries per client: 0 **In the Ubuntu installed in the VMWare:** Now on the **SAME SYSTEM**, as I have dual booted Windows with Ubuntu, I ran the Windows and installed Ubuntu in the VMWare with 2GB RAM & 50GB HDD and with the same file system Ext4, I got the following results with the same mysql configuration: The following command INSTANTLY returned the result, as soon as I typed the password and pressed the Enter key the result was displayed: mysqlslap --user=root --password --host=localhost --auto-generate-sql --verbose Average number of seconds to run all queries: 0.002 seconds Minimum number of seconds to run all queries: 0.002 seconds Maximum number of seconds to run all queries: 0.002 seconds Number of clients running queries: 1 Average number of queries per client: 0 While the following command took 2.5 seconds to return the result: mysqlslap --user=root --password --host=localhost --concurrency=50 --iterations=10 --auto-generate-sql --verbose Average number of seconds to run all queries: 0.154 seconds Minimum number of seconds to run all queries: 0.131 seconds Maximum number of seconds to run all queries: 0.193 seconds Number of clients running queries: 50 Average number of queries per client: 0 Looking at the results, I am lost how the MySQL in the VM perform fast while it's slow in the dedicated system. My question is, what is the possible thing which may be slowing down the MySQL in the dedicated machine? How can I make it as fast as it is in VM? **CONFIGURATION FILE:** In both VM and dedicated system with Ubuntu, I have used the same default MySQL configurations, I haven't changed anything in the configuration files. # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. # Here is entries for some specific programs # The following values assume you have at least 32M ram [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP #max_connections = 100 #table_open_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # log_error = /var/log/mysql/error.log # # Here you can see queries with especially long duration #slow_query_log = 1 #slow_query_log_file = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call. I'm trying to slam the DB to see what the breaking point (# of requests per second) would be. In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app. How can I make this a good test to achieve the test results based on SQL performance? Is there a tool for this that i can give it a stored proc name and param for it to pund my DB? I want to see if the DB can return a min. of 250 responses per second.

I am following the guide for capturing a trace in SQL Server Database Experimentation Assistant here: https://learn.microsoft.com/en-us/sql/dea/database-experimentation-assistant-capture-trace?view=sql-server-2017 I am using DEA version 2.6. The article above states that I should be able to choose the capture trace file size for rolling over files. However, in version 2.6 I do not have the option to set trace file size. As a result, I end up with dozens of 500 MB files at the end of the capture. Then I am expected to sit there and replay each trace file, one by one. Is there any easier way of doing this, or of forcing the trace file to grow larger so I end up with less rollover files? My new capture screen: MS doc capture screen:

I've struggled to find the answer to get a perfect backup and restore for MySQL. I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation - the whole things is like for like - 100% preserved. I tried copying the raw files (.frm, .ibd, .trn, .myi etc) files across but get Table '' doesn't exist in engine. I also tried dBForge to copy the database which worked, but after looking at the fragmentation levels I can see differences, so I think this isn't a like for like (although I'm copying from production so perhaps I saw some anomaly there). mysqldump - Does this do it?

Is there any tool available which can do stress testing using the log file created by MySQL general log? After a lot of search on google I found few stress testing tools which only use some benchmarks for stress test. One solution is using Apache JMeter, but it does not create test plans from MySQL log file and creating custom test plan for all the queries I have is too time consuming.
Or is there a tool which can at least create .sql file from MySQL log?

I am using the Database Experimentation Assistant v2.6 to capture a workload using extended events (.xel) on SQL Server 2017 that is installed on an Azure VM. I'm then replaying it on a Azure SQL Database Managed Instance. The capture and the replay work great. I'm getting multiple .xel files from the replay, 15 from the VM and 7 from the Managed Instance. My issue is when I create the analysis report it only allows me to select 1 .xel file in each of the the "Trace for Target 1 SQL Server" and "Trace for Target 2 SQL Server" fields to compare. My expectation is that I can run one report for the entire replay by selecting the directories the files are in but this does not appear to be an option? When I try to select just the directory it will not allow me to exit the directory window. Below is the screen I'm referring to : I've looked through multiple articles but the only ones I've found, including this one from Microsoft , refer to selecting only one file and normally a .trc file not a .xel file. How can I compare multiple .xel files at once?

I have a table which might be causing excessive delays during insertion of new records. I'd like to run a test against the database, measuring how long it takes to select, insert, and delete from the table. Are there any built-in tools to do this against an SQL Server database table?

When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second. In a Google Search, I could find these commands: DBCC FREESYSTEMCACHE DBCC FREESESSIONCACHE DBCC FREEPROCCACHE In fact, my queries are taking a more realistic time to complete after several executions than before. However, I'm not sure this is the recommended technique. What's the best practice?