high performance test data/test query generator

Hi,

We have lots of mysql servers, master-slave and sharded databases. A
recurring task when a new feature/application comes in to test the
database with real workload. This needs test data and test query
generation. Until now I did this with ad-hoc scripts, I looked for tools
to do this, so far I found nothing.

The closest thing to this was benerator, but it doesn't generate test
queries. I need to measure write performance too (or concurrent
read/write performance), so it would be good if I can tell the tool to
generate bulk inserts with n records or generate a csv file or generate
single insert... statements, so I can simulate the application's
workload (of course I can do this with the application itself, but in
development stage it is important to see how the database itself performs).

Does such a tool exists? No problem if it is mysql only. If not, I think
I will write one.

Peter Boros

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: high performance test data/test query generator

I use jmeter too, but it can't generate the test dataset (if I have to
write this, I plan that it will create a jmeter test case with the
generated test data). Usually my ad-hoc script generates csv files (to
load initial data) and jmeter test cases.

Michael Dykman wrote:
> On Wed, Jun 3, 2009 at 9:41 AM, petya wrote:
>> Hi,
>>
>> We have lots of mysql servers, master-slave and sharded databases. A
>> recurring task when a new feature/application comes in to test the database
>> with real workload. This needs test data and test query generation. Until
>> now I did this with ad-hoc scripts, I looked for tools to do this, so far I
>> found nothing.
>>
>> The closest thing to this was benerator, but it doesn't generate test
>> queries. I need to measure write performance too (or concurrent read/write
>> performance), so it would be good if I can tell the tool to generate bulk
>> inserts with n records or generate a csv file or generate single insert...
>> statements, so I can simulate the application's workload (of course I can do
>> this with the application itself, but in development stage it is important
>> to see how the database itself performs).
>>
>> Does such a tool exists? No problem if it is mysql only. If not, I think I
>> will write one.
>>
>> Peter Boros
>
> It is by no means the only such tool, but I have often use JMeter as
> an all-purpose load-generation tool. It has good support for database
> testing among other things..
>
> http://jakarta.apache.org/jmeter/usermanual/build-db-test-pl an.html
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: high performance test data/test query generator

Anyone else? You guys don't simulate realistic workload in benchmark
just do sysbench or something like that? If there were a tool for that
(which can handle data generation, initial database generation, and
query generation, maybe it's output will be a jmeter test case) would
you do so?

Peter

petya wrote:
> I use jmeter too, but it can't generate the test dataset (if I have to
> write this, I plan that it will create a jmeter test case with the
> generated test data). Usually my ad-hoc script generates csv files (to
> load initial data) and jmeter test cases.
>
> Michael Dykman wrote:
>> On Wed, Jun 3, 2009 at 9:41 AM, petya wrote:
>>> Hi,
>>>
>>> We have lots of mysql servers, master-slave and sharded databases. A
>>> recurring task when a new feature/application comes in to test the
>>> database
>>> with real workload. This needs test data and test query generation.
>>> Until
>>> now I did this with ad-hoc scripts, I looked for tools to do this, so
>>> far I
>>> found nothing.
>>>
>>> The closest thing to this was benerator, but it doesn't generate test
>>> queries. I need to measure write performance too (or concurrent
>>> read/write
>>> performance), so it would be good if I can tell the tool to generate
>>> bulk
>>> inserts with n records or generate a csv file or generate single
>>> insert...
>>> statements, so I can simulate the application's workload (of course I
>>> can do
>>> this with the application itself, but in development stage it is
>>> important
>>> to see how the database itself performs).
>>>
>>> Does such a tool exists? No problem if it is mysql only. If not, I
>>> think I
>>> will write one.
>>>
>>> Peter Boros
>>
>> It is by no means the only such tool, but I have often use JMeter as
>> an all-purpose load-generation tool. It has good support for database
>> testing among other things..
>>
>> http://jakarta.apache.org/jmeter/usermanual/build-db-test-pl an.html
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: high performance test data/test query generator

--0016e64c1ad0d88293046b848631
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I find it difficult to use a general tool to generate a lot of data specifi
to my application. There are many tools out there to execute queries against
a MySQL server it seems and log/report the performance. However, finding a
tool that is able to generate a lot of data that fits into your schema seems
to be the problem.

I would use a tool that does this.



On Thu, Jun 4, 2009 at 6:05 AM, petya wrote:

> Anyone else? You guys don't simulate realistic workload in benchmark just
> do sysbench or something like that? If there were a tool for that (which can
> handle data generation, initial database generation, and query generation,
> maybe it's output will be a jmeter test case) would you do so?
>
> Peter
>
>
> petya wrote:
>
>> I use jmeter too, but it can't generate the test dataset (if I have to
>> write this, I plan that it will create a jmeter test case with the generated
>> test data). Usually my ad-hoc script generates csv files (to load initial
>> data) and jmeter test cases.
>>
>> Michael Dykman wrote:
>>
>>> On Wed, Jun 3, 2009 at 9:41 AM, petya wrote:
>>>
>>>> Hi,
>>>>
>>>> We have lots of mysql servers, master-slave and sharded databases. A
>>>> recurring task when a new feature/application comes in to test the
>>>> database
>>>> with real workload. This needs test data and test query generation.
>>>> Until
>>>> now I did this with ad-hoc scripts, I looked for tools to do this, so
>>>> far I
>>>> found nothing.
>>>>
>>>> The closest thing to this was benerator, but it doesn't generate test
>>>> queries. I need to measure write performance too (or concurrent
>>>> read/write
>>>> performance), so it would be good if I can tell the tool to generate
>>>> bulk
>>>> inserts with n records or generate a csv file or generate single
>>>> insert...
>>>> statements, so I can simulate the application's workload (of course I
>>>> can do
>>>> this with the application itself, but in development stage it is
>>>> important
>>>> to see how the database itself performs).
>>>>
>>>> Does such a tool exists? No problem if it is mysql only. If not, I think
>>>> I
>>>> will write one.
>>>>
>>>> Peter Boros
>>>>
>>>
>>> It is by no means the only such tool, but I have often use JMeter as
>>> an all-purpose load-generation tool. It has good support for database
>>> testing among other things..
>>>
>>> http://jakarta.apache.org/jmeter/usermanual/build-db-test-pl an.html
>>>
>>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e64c1ad0d88293046b848631--

Re: high performance test data/test query generator

--000e0cd2e6e66c9b73046bb3a723
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I usually simulate the load of the whole stack, by benchmarking the
application directly using a tool such as jMeter. Loading MySQL specifically
with read/write patterns similar to those of the real application can be
useful, but quite hard to accomplish.

One easy way of doing that is to enable logging in your server and capture
the statements executed over a day or so, then extract the SQL statements
into a separate file. You can use then use a tool such as mysqlslap
(available since 5.1) to execute those statements in a number of iterations
with a given concurrency on your benchmarking box.

On Thu, Jun 4, 2009 at 2:05 PM, petya wrote:

> Anyone else? You guys don't simulate realistic workload in benchmark just
> do sysbench or something like that? If there were a tool for that (which can
> handle data generation, initial database generation, and query generation,
> maybe it's output will be a jmeter test case) would you do so?
>
> Peter
>
>
> petya wrote:
>
>> I use jmeter too, but it can't generate the test dataset (if I have to
>> write this, I plan that it will create a jmeter test case with the generated
>> test data). Usually my ad-hoc script generates csv files (to load initial
>> data) and jmeter test cases.
>>
>> Michael Dykman wrote:
>>
>>> On Wed, Jun 3, 2009 at 9:41 AM, petya wrote:
>>>
>>>> Hi,
>>>>
>>>> We have lots of mysql servers, master-slave and sharded databases. A
>>>> recurring task when a new feature/application comes in to test the
>>>> database
>>>> with real workload. This needs test data and test query generation.
>>>> Until
>>>> now I did this with ad-hoc scripts, I looked for tools to do this, so
>>>> far I
>>>> found nothing.
>>>>
>>>> The closest thing to this was benerator, but it doesn't generate test
>>>> queries. I need to measure write performance too (or concurrent
>>>> read/write
>>>> performance), so it would be good if I can tell the tool to generate
>>>> bulk
>>>> inserts with n records or generate a csv file or generate single
>>>> insert...
>>>> statements, so I can simulate the application's workload (of course I
>>>> can do
>>>> this with the application itself, but in development stage it is
>>>> important
>>>> to see how the database itself performs).
>>>>
>>>> Does such a tool exists? No problem if it is mysql only. If not, I think
>>>> I
>>>> will write one.
>>>>
>>>> Peter Boros
>>>>
>>>
>>> It is by no means the only such tool, but I have often use JMeter as
>>> an all-purpose load-generation tool. It has good support for database
>>> testing among other things..
>>>
>>> http://jakarta.apache.org/jmeter/usermanual/build-db-test-pl an.html
>>>
>>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=amr.mostafa@gmail.com
>
>


--
Amr Mostafa
PHP Team Leader, Egypt Development Center
amr.mostafa@egyptdc.com http://egyptdc.com
+(2012)1700502 +(202)24052355/6

--000e0cd2e6e66c9b73046bb3a723--

Re: high performance test data/test query generator

That only works if the application is already in production and gets a
certain load. For a new application (for example the database is
designed but the application is not ready yet) this doesn't work.

Amr Mostafa wrote:
> I usually simulate the load of the whole stack, by benchmarking the
> application directly using a tool such as jMeter. Loading MySQL specifically
> with read/write patterns similar to those of the real application can be
> useful, but quite hard to accomplish.
>
> One easy way of doing that is to enable logging in your server and capture
> the statements executed over a day or so, then extract the SQL statements
> into a separate file. You can use then use a tool such as mysqlslap
> (available since 5.1) to execute those statements in a number of iterations
> with a given concurrency on your benchmarking box.
>
> On Thu, Jun 4, 2009 at 2:05 PM, petya wrote:
>
>> Anyone else? You guys don't simulate realistic workload in benchmark just
>> do sysbench or something like that? If there were a tool for that (which can
>> handle data generation, initial database generation, and query generation,
>> maybe it's output will be a jmeter test case) would you do so?
>>
>> Peter
>>
>>
>> petya wrote:
>>
>>> I use jmeter too, but it can't generate the test dataset (if I have to
>>> write this, I plan that it will create a jmeter test case with the generated
>>> test data). Usually my ad-hoc script generates csv files (to load initial
>>> data) and jmeter test cases.
>>>
>>> Michael Dykman wrote:
>>>
>>>> On Wed, Jun 3, 2009 at 9:41 AM, petya wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> We have lots of mysql servers, master-slave and sharded databases. A
>>>>> recurring task when a new feature/application comes in to test the
>>>>> database
>>>>> with real workload. This needs test data and test query generation.
>>>>> Until
>>>>> now I did this with ad-hoc scripts, I looked for tools to do this, so
>>>>> far I
>>>>> found nothing.
>>>>>
>>>>> The closest thing to this was benerator, but it doesn't generate test
>>>>> queries. I need to measure write performance too (or concurrent
>>>>> read/write
>>>>> performance), so it would be good if I can tell the tool to generate
>>>>> bulk
>>>>> inserts with n records or generate a csv file or generate single
>>>>> insert...
>>>>> statements, so I can simulate the application's workload (of course I
>>>>> can do
>>>>> this with the application itself, but in development stage it is
>>>>> important
>>>>> to see how the database itself performs).
>>>>>
>>>>> Does such a tool exists? No problem if it is mysql only. If not, I think
>>>>> I
>>>>> will write one.
>>>>>
>>>>> Peter Boros
>>>>>
>>>> It is by no means the only such tool, but I have often use JMeter as
>>>> an all-purpose load-generation tool. It has good support for database
>>>> testing among other things..
>>>>
>>>> http://jakarta.apache.org/jmeter/usermanual/build-db-test-pl an.html
>>>>
>>>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=amr.mostafa@gmail.com
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org