|
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 |
|
No need to rebuild. Just shutdown the database and copy the files to the new server. 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 '19, 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 '19, 02:47)
Volker Barth
Ok, I'll check this out. Thanks.
(27 Jun '19, 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 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 '19, 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 '19, 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.
That is really old, right:)
(27 Jun '19, 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 '19, 11:33)
Breck Carter
|
