Tag Info

Hot answers tagged

17

Personally, for a common query the 2nd and subsequent executions matter more. Are you testing disk IO or query performance? Assuming your query runs often and is critical, then you want to measure that under real life conditions. And you don't want to clear prod server caches each time... If you want, you can DBCC DROPCLEANBUFFERS clears buffer pool ...


14

OK, I'll bite and take a contrarian view. First off, I would say you should never start by doing something that you know will lead you into trouble. If you'd like to call this applying best practices, go ahead. This is as far as being proactive should go. After that, time's (and money's) a-wasting so get on with it and deliver your product. Instead of ...


13

Both, but mostly proactive It's important to test during development against realistic volumes and quality of data. It's unbelievably common to have a query running on a developers 100 or 1000 rows then fall flat with 10 million production rows. It allows you to make notes too about "index may help here". Or "revisit me". Or "will fix with new feature xxx ...


13

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them. For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note ...


12

You're going to be doing 3 types of tuning, 1 reactive and 2 proactive. Reactive Out of the blue, some query starts causing you problems. It could be because of an application bug or feature, a table growing in excess of expectations, a traffic spike, or the query optimizer getting "creative". This could be a middle-of-the-night oh-crap-the-site's-down ...


12

The best tool (if you can find it) is DataFactory. (Sadly out of print). I've generated absolutely delightful (and quite authentic-looking) datasets from it. Generatedata.com is... acceptable, but doesn't scale very well. DataGenerator is something to keep an eye on. And while DTM Data Generator is clunky and a poor substitute for DataFactory, it exists ...


9

Quest offers a free version of their Benchmark Factory that works for Oracle, SQL Server, and MySQL: http://www.quest.com/benchmark-factory/ I recorded a video on getting started with it back when I worked for Quest: http://www.toadworld.com/Videos/tabid/831/TID/559/cid/161/Default.aspx You can call your own stored procs, replay traces, run TPC benchmark ...


9

RedGate has a tool similar to what you're looking for, but it's destination is intended to be MS SQL Server. http://www.red-gate.com/products/sql-development/sql-data-generator You might also check out the following article: http://www.sqlservercentral.com/articles/Advanced+Querying/jointestdata/197/


8

Red Gate SQL Data Generator?


7

SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities. A very brief outline of the tools: ReadTrace converts Profiler traces from .trc ...


7

Here is the first thing I thought about Setup 3 DB servers (identical HW/OS configs) each installed with MySQL Percona MariaDB Get a Fourth Server installed with MONyog (eval version lasts 30 days) Register the 3 DB servers in MONyog Use the Performance Metrics Charts in MONyog or Set Up Your Own Charts in MONyog Use SysBench against all 3 DB Servers ...


5

I believe, that the best lies in the middle. Natural keys overview: They are make data model more obvious because they are came from object area, and not from somebody's head. Simple keys (one column, between CHAR(4) and CHAR(20)) are saving some extra bytes, but you need to watch for their consistency (ON UPDATE CASCADE becomes critical for those keys, ...


5

Yes it is a locking issue. According to this page (non full) VACUUM needs SHARE UPDATE EXCLUSIVE access which is blocked by the lock level you are using. Are you certain you need this lock? PostgreSQL is ACID compliant so concurrent writes are in most cases not a problem as PostgreSQL will abort one of the transactions if a serialization violation would ...


4

One tool you can use is SQLQueryStress. For a quick start provide: the stored procedure the iterations and the number of threads. Then start the stress testing. Metrics like CPU seconds, logical reads, etc... will be shown.


4

In a perfect world all tuning would be done in the design phase proactively and nothing would be reactive, but the world isn't perfect. You will find that test data sometimes isn't representative, test cases will have been missed, loads will be unexpectedly different, and there will be bugs that cause performance issues. These situations may require some ...


4

I'll answer this the same way I did when this was asked for mysql : You're often better off doing this yourself, or the data may be too random, and won't have a similar distribution to what your real data will look like. (eg, indexes are good for truly random data ... not so much when there's a limited number of permutations or some values occurs more than ...


4

You can use Visual Studio database projects with Data Generator Plans. A nice video tutorial can be watched here: How Do I: Generate Test Data using Visual Studio Team System Database Edition?


4

An excellent resource is of course the Redgate SQL Toolbelt, in particular their Data Generator SQL Data Generator is a fast, simple tool for generating test data. Using table and column names, field length, data types and other existing constraints SQL Data Generator immediately provides sensible generators. These can then be customized to meet ...


4

A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the You'll want to log queries that aren't using indexes Update In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify ...


4

One thing you could do to compare these two versions is to rename one of them to f_old and execute them both inside the same batch, while including the actual execution plan and IO statistics. From there, it is a simple matter of comparing the plans and IO stats of each. One thing you need to be wary of is that in your first statement you had IF @b > 0. ...


4

This should be very helpful to you as this is the defacto industry benchmark for databases, TPC-C, http://www.tpc.org/tpcc/spec/tpcc_current.pdf Even if you do not use execute this benchmark, the information contained within the test definition document should provide you with tons of insight into setting up your own benchmark for testing your database ...


4

First, I'd step back and ask what measurements you plan to collect during the test. If you're counting logical reads by query, for example, then you don't need to free the cache. I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be ...


4

Increasing the number of autovacuum processes and reducing the naptime will probably help. Here is the configuration for a PostgreSQL 9.1 that I use on a server that stores backup information and as a result gets a lot of insert activity. http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html autovacuum_max_workers = 6 # ...


4

You can do this by piping the query into psql, then grepping the output, like so: postgres@winterspring:~$ cat foo.sql \timing on select * from foo; postgres@winterspring:~$ psql < foo.sql | grep "^Time:" Time: 0.505 ms postgres@winterspring:~$ Redirect just the timing to a file if needed: postgres@winterspring:~$ psql < foo.sql | grep "^Time:" ...


4

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings: SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, ...


4

In general, SQL Server uses B+Trees for indexes. The expense of an index seek is directly related to the length of the key in this storage format. Hence, a surrogate key usually outperforms a natural key on index seeks. SQL Server clusters a table on the primary key by default. The clustered index key is used to identify rows, so it gets added as included ...


4

Simple down to 3ms accuracy: SELECT GETDATE(); -- run query SELECT GETDATE(); On SQL Server 2008 and above you can use SYSDATETIME() which has much greater accuracy. You can also set a variable equal to the current time, run your query, and use DATEDIFF to determine the delta in whatever granularity makes sense (however this won't work across batches). ...


4

I typically generate my own, using some known data as input -- if it's too random, it's not always a good test; I need data that's going to be distributed similarly to my final product. All of the larger databases that I have to tune are scientific in nature -- so I can usually take some other investigation as input, and rescale it and add jitter. (eg, ...


3

You can not compare the performance of 2 databases by just looking at their sql plans. The same queries can have very different plans. When the versions are different all kinds of computations for the cost are different. The plan that a query follows in execution could very well be different than the plan you get from explain plan from your client. The only ...


3

For MySQL, there's the official benchmark suite. Alternatively a tool like sysbench. I'm currently in the process of testing out sysbench, but I've been having some trouble!



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