HammerDB. How to generate a compressible data?

[vbienf]

Hi all!

I didn't find any information how to generate compressible data with HammerDB. F.e. for sysbench I using for each string specific mask:

• sysbench.rand.string("name-@@@@@"),
• sysbench.rand.string("street1-@@@@@@@@@@"),
• sysbench.rand.string("street2-@@@@@@@@@@"),
• sysbench.rand.string("city-@@@@@@@@@@"),
• etc...

link to source - https://github.com/Percona-Lab/sysbench-tpcc/blob/master/tpcc_common.lua

This mask helps to generate compressible data for my test.

How I can add a specific mask to string to generate data with HammerDB?

[sm-shaw]

Hi, Yes, we follow the TPC-C specification for random string generation, but if I understand the question you are saying that the default random strings from the specification do not compress very well when using solutions to compress the table data because they are well ... random and in some cases non-uniform.

So the question is, is it possible to make the generated data less random, so it will compress better. It is interesting that sysbench does this. It is not something we plan to do, however it is all open source, so you can change the generation of data as you wish. If you look in the modules directory you can find the common data generation functions in tpcccommon-1.0

Using the example below for generating the address as you use, here is the function that does that:

  #ADDRESS
  proc MakeAddress { chArray chalen } {
    return [ list [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 2 2 $chArray $chalen ] [ Makezip ] ]
  }

and make address gets called here in the build script:

proc District { mysql_handler w_id DIST_PER_WARE } {
    set globArray [ list 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z ]
    set chalen [ llength $globArray ]
    puts "Loading District"
    set d_w_id $w_id
    set d_ytd 30000.0
    set d_next_o_id 3001
    for {set d_id 1} {$d_id <= $DIST_PER_WARE } {incr d_id } {
        set d_name [ MakeAlphaString 6 10 $globArray $chalen ]
        set d_add [ MakeAddress $globArray $chalen ]
        set d_tax_ran [ RandomNumber 10 20 ]
        set d_tax [ string replace [ format "%.2f" [ expr {$d_tax_ran / 100.0} ] ] 0 0 "" ]
        mysql::exec $mysql_handler "insert into district (`d_id`, `d_w_id`, `d_name`, `d_street_1`, `d_street_2`, `d_city`, `d_state`, `d_zip`, `d_tax`, `d_ytd`, `d_next_o_id`) values ('$d_id', '$d_w_id', '$d_name', '[ lindex $d_add 0 ]', '[ lindex $d_add 1 ]', '[ lindex $d_add 2 ]', '[ lindex $d_add 3 ]', '[ lindex $d_add 4 ]', '$d_tax', '$d_ytd', '$d_next_o_id')"
    }
    mysql::commit $mysql_handler 
    puts "District done"
    return
}

So for a similar effect you could either change Make Address or the build functions to prepend the random data with name-, street1- etc.

Or for a really simple case of making things non-random change the character array (globArray) to have less options to select random data from, e.g. instead of all the alphabet, reduce the available options to fewer letters.

[sm-shaw]

Hi,

It's easier than that, we have the string replace command that does what you need. So for example to overwrite the street and city strings with street1-, street2-, and city- you can do something like this:

% puts [ string replace "myrandomstring" 0 8 "street1-" ]
street1-tring
%

so string replace original-string start and end and new string

doing this you would change the module as follows:

 proc MakeAddress { chArray chalen } {
#    return [ list [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 10 20 $chArray $chalen ] [ MakeAlphaString 2 2 $chArray $chalen ] [ Makezip ] ]
    return [ list [ string replace [ MakeAlphaString 10 20 $chArray $chalen ] 0 8 "street1-" ]  [ string replace [ MakeAlphaString 10 20 $chArray $chalen ] 0 8 "street2-" ] [ string replace [ MakeAlphaString 10 20 $chArray $chalen ] 0 5 "city-" ] [ MakeAlphaString 2 2 $chArray $chalen ] [ Makezip ] ]
  }

This would give output as follows for all addresses in customer and district for all databases and make it less random e.g.

No you don't need to compile anything, it all gets compiled into bytecode at runtime so just change the source.

Still interesting why sysbench would see it necessary to do something like this by default thought.

[vbienf]

Nice! Thank you very much @sm-shaw for your answers!

According to this link https://www.percona.com/blog/tpcc-like-workload-sysbench-1-0/ they want to be able to evaluate different compression methods in InnoDB and MyRocks. This is a different DB engines for MySQL, that supports compression.

I will try to change scripts tpcccommon-1.0.tm and oraolap.tcl and add possibility to create data generation with different compression (2 and 3) and upload it to my repository. If someone need to generate compressible data with hammerDB they can just download that files and change them in main directory locally.

[sm-shaw]

One minor clarifying point - it will be tpcccommon-1.0.tm and oraoltp.tcl for TPROC-C and tpchcommon-1.0.tm and oraolap.tcl for TPROC-H.
Note that changing the common module as would expected affects all databases.

[vbienf]

I understand that. But also in oraoltp.tcl there is a insert queries such us:

proc mk_region { lda } { set sql INSERT INTO REGION (R_REGIONKEY, R_NAME, R_COMMENT) VALUES (:R_REGIONKEY, :R_NAME, :R_COMMENT) ..... set comment [ TEXT_1 72 ]

I didn't find information about "TEXT_1" in tpcccommon-1.0.tm. So, I need to change not only common, but also all tcl files for all databases? Or how I can do that in rights way?

[vbienf]

@sm-shaw , oh, sorry! I found "TEXT_1" in tpchcommon-1.0.tm.

So, in this case I just need to change both files tpcccommon-1.0.tm and tpchcommon-1.0.tm and it will affect to all databases?

[sm-shaw]

Anything you change in tpcccommon-1.0.tm will affect the TPROC-C build and workload for all databases (not just Oracle you are working with). Likewise, anything in tpchcommon-1.0.tm will affect all TPROC-H. Changing the local dbprefixoltp.tcl and dbprefixolap.tcl will only affect that particular workload for that particular database.

[vbienf]

Thanks! I think now I have all information that I need. I will update this topic after finish and testing with this files.

[vbienf]

Hi @sm-shaw !

I finished with first version. I changed scripts and now they generate compressible data like in the sysbench-tpcc ( Compression factor near 3.5 ) for MySQL. Next step it will be change tcl script for Oracle and Postgres.

Can you please check my git-repository ( https://github.com/vbienf/TPC-C-compressions-for-HammerDB ) and review this changes? I need your approve that I changed only data generation and no more.

[sm-shaw]

To set expectations, I don't have the bandwidth to review right now. However, note the workload itself is in stored procedures, so if you changed the data only you should be good to go.

[vbienf]

Nope, I didn't touch stored procedures.
I created new functions in tpcccommon-1.0.tm for generate strings with prefix and compressing, like it was at sysbench:

• MakePrefZip
• MakePrefAddress
• MakePrefName
• MakeCompressString
• MakeItemString

And in dbprefixoltp.tcl-file (now only in mysql - mysqloltp.tcl) I just call these functions instead of "MakeAlphaString". F.e. for customer table:

#set c_first [ MakeAlphaString 8 16 $globArray $chalen ]
set c_first [ MakePrefName "first-" 2 10 $globArray $chalen ]

Data generation that was changed:

1. Table warehouse

• w_name = name-<random string(5)>
• w_street_1 = street1-<random string(10)>
• w_street_2 = street2-<random string(10)>
• w_city = city-<random string(10)>
• w_zip = zip-<random number(5)>

2. Table district

• d_name = name-<random string(5)>
• d_street_1 = street1-<random string(10)>
• d_street_2 = street2-<random string(10)>
• d_city = city-<random string(10)>
• d_zip = zip-<random number(5)>

3. Table customer

• c_first = first-<random string(2,10)>
• c_street_1 = street1-<random string(10)>
• c_street_2 = street2-<random string(10)>
• c_city = city-<random string(10)>
• c_zip = zip-<random number(5)>
• c_data = <1 random char string(300,500)>

4. Table history

• h_data = <1 random char string(12,24)>

5. Table item

• i_name = item-<i_im_id>-<i_price>-<random string(5)>
• i_data = data-<i_name>-<random string(8)>

6. Table stock

• s_dist_01 = <1 random char string(24)>
• s_dist_02 = <1 random char string(24)>
• s_dist_03 = <1 random char string(24)>
• s_dist_04 = <1 random char string(24)>
• s_dist_05 = <1 random char string(24)>
• s_dist_06 = <1 random char string(24)>
• s_dist_07 = <1 random char string(24)>
• s_dist_08 = <1 random char string(24)>
• s_dist_09 = <1 random char string(24)>
• s_dist_10 = <1 random char string(24)>
• s_data = <1 random char string(26,50)>

7. Table order_line

• ol_dist_info = <1 random char string(24)>