Hot answers tagged

126

Just turn on timing by entering: \timing


37

Timing can be turned on with \timing at the psql prompt (as Caleb already said). If you are on 8.4 or above, you can add an optional on/off argument to \timing, which can be helpful if you want to be able to set timing on in .psqlrc - you can then set \timing on explicitly in a script where plain \timing would otherwise toggle it off


16

The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times. Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to go that route, here is a related ...


8

A hard drive's IOPS tells you how many I/O operations that particular drive can perform per second. Since random reads, random writes, sequential reads, and sequential writes have different performance characteristics, if you're looking at a single number, that implies that this is a weighted average of these four numbers that represents some workload. ...


7

For Microsoft SQL Server, the lowest-intrusive tool is Performance Monitor, aka Perfmon. Here's my tutorial on grabbing Perfmon counters for SQL Server and analyzing them: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/


7

The SQL Profiler, execution plan, IO/Cpu statistics are your best friend at checking the performance of your code. You will see if there are any differences in performance between the 2 versions of functions. In this question - Tuning exercises - you can find some basic and advanced references about tuning.


6

Benchmarking or Tuning tool? IMHO, there's no tool that will be specific to the latter unless you have a super generic usage. You need to identify your usage pattern and tune your database hosts to accommodate. If you're write-heavy, you will have a different configuration than a read-heavy scenario. Bottom line, your tuning follows your applications usage. ...


6

I have done the following to reproduce the output you are seeing from dbt2. Installed Centos6.4 Installed MySQL 5.1.69 via YUM Installed Innotop to view in real time all queries and open transactions Ran the bench as you did: [root@centos-cef dbt2-0.37.50.3]# scripts/run_mysql.sh --connections 20 --terminals 5 --time 300 --warehouses 3 --verbose ...


4

Here's a recent blog post (2013-07-01) that shows how to use tpcc-mysql and graph results: http://www.mysqlperformanceblog.com/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/ This is written by Michael Rikmas, who is a super nice guy, and a long time Percona support engineer.


4

I think MONyog can handle some of your request. MONyog MySQL Monitor and Advisor is a "MySQL DBA in a box" that helps MySQL DBAs manage more MySQL servers, tune their MySQL servers and fix problems with MySQL database applications. MONyog not only finds problem SQL it has 200+ monitors and advisors as well which suggests what parameter you ...


4

Not sure about the TPC-DS, but I know that HammerDB will perform a TPC-H(ad hoc DSS) benchmark test against PostgreSQL. If you're looking for a benchmark, but not specifically a TPC-DS, this is a good tool for testing.


3

I would say using a local server would be better. At least that way you know the hardware and can simulate it as close to the production server as possible. With a cloud-based server you have no idea what the underlying hardware is, and with some services like Azure you will have to take the 3x write policy into account, different network connectivity, ...


2

Quest has a database benchmarking tool. You can run a synthetic TPC-C, H, E test against your MySQL database. It will not suggest configuration changes, but it does make testing those changes a bit easier. Benchmark Factory Community


2

You can use Spotlight a Toad application Spotlight® on MySQL diagnoses MySQL problems in real time by graphically displaying all database activity in an intuitive user interface, enabling you to respond quickly to issues that need attention.


2

In all probability, your query takes half a second to run, but it takes a lot more time to display all the results. The latter depends on the speed of your network and that of your computer and is not really relevant from a performance tuning perspective. To answer your question, the query execution time (0.5 seconds) is the execution time.


2

While I can't speak to measuring specific metrics like CPU, storage throughput, etc. in your environment, I have found HammerDB to be a good tool for running a good, standardized benchmark against your databases. This tool runs an unofficial TPC-C benchmark against your database. You can configure for concurrent user connections and workloads. Using this ...


2

Apologies for the late response to this, but there is a key reason as to why you are not observing an expected smooth behavior: You have a client-server model violation when you eliminate think time. The client server model is built upon a foundation that not all user are using system resources at the same time. This is different than the mainframe model ...


2

i have used PostGIS for over a decade now and I can tell you for sure that there is no match for it in the NoSQL world. how many rows do you have? how large is the thing? Mongo is definitely not going to make you happy. I am pretty sure you did something fishy on the PostgreSQL side to even consider using Mongo. Let us fix it ...


1

I strongly believe that only the "local" record is set to trust. try something like that: local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust this should work for all kinds of local connections.


1

I would collect queries from Master via slow log and then reply them on the slave using http://www.percona.com/doc/percona-toolkit/2.1/pt-log-player.html to do this you will need: Enable full slow query logging. You need to be careful with the IO load if your server is really busy. You may want to do it during low time (say at night) Collect good amount of ...


1

The MariaDB has a lot of optimizer features not found in mySQL (especially 5.5, but still compared to 5.6) See this link for a comparison of optimiser features. There is also a benchmark results page which shows MariaDB faster in a lot of tests. So in the end MariaDB has more options when choosing an execution plan.


1

If you could change the WHERE clause for the sake of the benchmark, do one of two things: OPTION #1 : Table Scan SET PROFILING=1; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SHOW PROFILES; OPTION #2 : Change WHERE xxx to WHERE 1=1 SET PROFILING=1; ...


1

SQLIO is technically deprecated - it's been replaced by the newer utility Diskspd. However, the same basic answer applies. If you look at an existing server and guess how much storage throughput it's USING, then look at the counters: IOPs - Physical Disk Reads/Sec and Writes/Sec IO size - Physical Disk Read Bytes/sec divided by Reads/sec (and same for ...


1

You can download fix error "STATE" and endcoding dump data to database at https://github.com/grahn/tpcds-kit


1

This is trickier than it looks. I/O per second is a difficult thing to pin down. Just because the file system sees so many I/Os per second, doesn't mean that MySQL sees the same thing - there is such a thing as I/O buffering. I would look into MySQL 5.6 - in particular the performance schema (P_S)(Marc Alff wrote the P_S (*)). One SQL SELECT could read from ...


1

The specification TPC BENCHMARKTM H, Standard Specification, Revision 2.17.1, can be found here. See section "1. LOGICAL DATABASE DESIGN / 1.5 IMPLEMENTATION RULES". Indexes can be created on combinations of fields defined in 1.4.2.2 and 1.4.2.3 and on date fields. Indexes may only contain fields of one table. So indexes can be built on the following ...


1

I recommend pgFouine. It is a good way of analyzing your Postgres for any given snapshot. I've used it for a couple of projects with great success. http://pgfouine.projects.pgfoundry.org/ Also, for general ideas on how to optimize Postgres. I subscribe to the Instagram blog and have personally gleamed a lot of inspiration from it. In particular to the ...


1

The BENCKMARK function only works on expressions. What qualifies as an expression? Stored Function (has a return value) ENCODE function on a String 1 + 2 (that expression gives an integer) ALTERNATIVE What you need is to simulate the BENCHMARK function yourself. Here is some sample code for you to try drop database if exists mybmark; create database ...


1

It really depends on what you are modeling. For mysql there are a few different definitions for simultaneous users. Number of database connections, including idles that only take up memory. Number of running queries. Number of queries active in the database engine. This is typically ignored. With a typical web application, you may have a hundred ...


1

I got the same error, the first thing I tried was to erase query1.tpl from the templates.list, but then I got a similar error for query2.tpl but referring to the substitution '_END'. I looked at the template and realized that the variable was never used, then I looked in the QGEN.doc guide and read that the variables are automatically set for use of ...



Only top voted, non community-wiki answers of a minimum length are eligible