on 2019 Jun 26 2:42 PM
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
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...)
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
...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:)
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
No need to rebuild. Just shutdown the database and copy the files to the new server.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.