Hi.

We’re going to move a customer database to a new server. It’s approx. 14 GB. Is it necessary to perform an unload/reload at the same time? It’s been 2 years since the last time that was done.

Br,

Bjarne

asked 26 Jun, 13:42

Bjarne%20Anker's gravatar image

Bjarne Anker
520212236
accept rate: 0%


No need to rebuild. Just shutdown the database and copy the files to the new server.

permanent link

answered 26 Jun, 13:46

Mark%20Culp's gravatar image

Mark Culp
24.2k9134286
accept rate: 40%

Thanks.

Just as I thought. Will consider an unload/reload if the performance does not increase significantly. But moving from an old 2008 server to a brand new 2016 server with SSD drives should do the trick.

Br,

Bjarne

(26 Jun, 14:03) Bjarne Anker
Replies hidden

If the hardware is very different, you might think about a re-calibration, aka ALTER DATABASE CALIBRATE...

(27 Jun, 02:47) Volker Barth

Ok, I'll check this out. Thanks.

(27 Jun, 06:40) Bjarne Anker

The main reason to move the database to a new server is as always performance. The issue is that running certain functions in our PowerBuilder application causes other applications to freeze because of write locks on 5-6 tables in the database. It still looks to be a problem, but only from the workstations. Sometimes this operation takes over 25 minutes to complete, and in the meantime there are lots of locks. This happens when we run commit in the application. However, there is no issues at all when running these applications directly on the database server. The same operation finishes in under 1 minute, and the locks are just present for a short while.

I've measured the network speed using iPerf3, and it averages around 510 Mbit/s. They have gigabit networking, so I've told the IT department to look into this. I've also done a sa_index_density() on one of the tables in use:

https://www.dropbox.com/s/ark5jhlp58sd67l/Capture.PNG?dl=0

I'm not sure how to decipher all this data, but it looks like something could be done here? Perhaps an unload/reload? Or reorganize table?

br,

Bjarne

permanent link

answered 27 Jun, 06:52

Bjarne%20Anker's gravatar image

Bjarne Anker
520212236
accept rate: 0%

edited 27 Jun, 06:53

1

Could it be the different machines use different isolation level settings (possibly bound to machine-spcific ODBC settings)? (Minute-long "wait times" are probably more often caused by sub-optimal locking schemes than by hardware performance, methinks...)

(27 Jun, 07:43) Volker Barth

Hi.

It has crossed my mind, yes. Each workstation has their own ODBC in "System DSN". Just now, I found out that most of the workstation is set up with SQL Anywhere 12 ODBC driver. The database is SQL Anywhere 16, EBF 2193. That might be a potential problem? It's been like this for a long time apparently and these performance issues has escalated the last 2-3 months.

You will have to set the isolation level either in the ODBC or in the applications connectionstring, right?

br,

Bjarne

(27 Jun, 07:50) Bjarne Anker
Replies hidden
1

...or via SET [TEMPORARY] OPTION isolation_level = X statements within the application or via according API calls...

Before you change those, I recommend to find out whether they are different, such as:

-- lists all current connections with isolation level
select sci.*,
   connection_property('isolation_level', sci.Number)
from sa_conn_info() sci
order by 1;

I would not expect the client version 12 vs. 16 to matter much here.


SQL Anywhere 16, EBF 2193

That is really old, right:)

(27 Jun, 10:44) Volker Barth
1

Foxhound does a good job of showing which connections are blocked and why, in real time.

Figure 1. The Foxhound Monitor Page Showing An Idle Server With One Blocked Connection

(28 Jun, 11:33) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×43
×15

question asked: 26 Jun, 13:42

question was seen: 180 times

last updated: 28 Jun, 11:33