Local Storage performance and optimization

Toad Data Point
1

Hi!

I am using TDP 4.3.

There is a show stopping bug making it impossible to do cross connection queries to compare strings due to lack of support for international character set towards DB2 DB (and probably other databases, I have previously reported this bug towards Sybase and got it fixed, if I remember correct). As a workaround I though I could copy the data to Local Storage and run the SQL there instead. Unfortunately the Local Storage is to slow for this. Probably due to missing indexes so I tried to create a primary key but this crashes due to time out. I have tried to increase the buffer size (to 898 MB which apears to be the max on my PC) but this did not help.
The table I am trying to index has 9.6M records.

Anything I can do to fix these issues with Local Storage?

Best regards,
Arne Hårseth

2

Hi Arne,
thanks for reporting this issue, I did several simple tests in my own environment - create index on 10 million rows table, but can't reproduce this issue. So I think I need to get some more info to do investigation. Could you please help to collect the following info for me?

  1. get the table structure info of the table that you would like to add index on:
    show create table <table_nem>;

  2. connect to local storage and run the sql:
    SHOW SESSION VARIABLES LIKE "%timeout%";

  3. what is the sql that you use to create the index?

  4. could you please send the following file to Winnie.Wu@quest.com
    x:\Users<username>\AppData\Local\Quest Software\Toad Data Point Hub Data\data\<machine_name>.err

Instead of creating the index after the table is created, TDP has provided a feature which allows you to create index at the moment when creating a table. -- Is it possible for you to have a try?

image
image.png888×455 17.2 KB

3

Hi Winni!

I have sent you the information you requested by mail.
The table is created through “Send to Local Storage” from Source table so it created automatically. I don’t think I can intervene this process to create an index?

Arne

4

Hi Arne,
thanks for your information.
table stored in local storage is using MYISAM engine, and I see you are trying to create a Primary key which is always a unique key. I googled and found this known issue: mysql performs extremely slow when creating unique indexes on huge table, so perhaps as a workaround, we can create a normal index instead, please do the following steps:
1) open the local storage script editor
image
2) execute the following 3 queries one by one:
set GLOBAL myisam_max_sort_file_size = 1024 * 1024 * 1024 * 100;
set myisam_sort_buffer_size = 1024 * 1024 * 1024;
ALTER TABLE your_local_storage_schema.your_local_storage_table ADD INDEX (your_key_column);
the queries should be executed in one session, just like this:

image
image.png758×97 10.3 KB

then please go to check if the index has been added.

By the way, I have two questions:

  1. what is the Ram size of your pc
  2. what is the free disk space size of the disk that you put your TDP HUB data(it is usually located at: x:\Users<username>\AppData\Local\Quest Software\Toad Data Point Hub Data, please check x disk free space)
5

if the method above doesn't work, you can have a try "add index when creating the table", which means you need to re-create the table:

  1. connect to your remote source table, and select "local storage"
    image
    2)select the primary key:

image
image.png886×456 14.9 KB

then create the table.

At last, please feel free to contact us if you meet any issue.

6

Thanks for your great suggestions Winnie. Creating index instead of primary key did the trick! :slight_smile:

7

glad to hear that, :grinning:

8

I have now made several indexes which increases the speed but I still have a problem with running the Compare function. After a large number of records has been compared the process seem to stop. I have run such a compare for a whole weekend without a response.
Do you have tip on how to debug this?
Is there for instance a timeout value I can set for Local Storage (MySQL)?
Does Compare produce a log I can check? How do I turn on such logging?

9

Are you comparing any Long, Blob, or very large column types? I see an outstanding issue on this topic. If you are, try excluding them. If not, it sounds like the data compare is locking up. When the data compare has run and is unresponsive, go to the task manager. Click on Toad.exe, right click and select “create dmp file”. This will generate a very large file. You will probably need to open up a support ticket and have support set it up so you can ftp the file to me. This file will tell us where the app is locked.