Hello,

We are using a 3rd party product running under Sybase 8 (dbsrv8.exe) and we are experiencing extreme slowdowns at all of our 42 POS terminals at peak periods (breakfast, lunch & dinner).

The vendor had rebuilt our database back in December and since then, there have been intermittent "freezes" across the board. They are unable to determine what the cause is.

I've spent the last month monitoring performance and resources on the server and I see some telling activity.

Only in the last couple of days have I convinced the vendor to give me SA access to the database, but I would love some help in pinpointing the issue so that I may go back to them and ask that they fix it.

I'm not a DBA. I'm a programmer and analyst and have extensive experience with Oracle and MySQL.

Here's what I see on the server end:

  • Hundreds (approx. 500) of TCP connections to dbsrv8.exe. Sometimes up to 40 or 50 connections from a single POS terminal.
  • Erratic disk activity. Fluctuations from 10,000 to 250,000,000 MB/Sec all pointing to dbsrv8.exe and the associated db.
  • Memory is pretty stable at 16Gb.
  • CPU is also very erratic. When I had 4 CPUs, utilization would climb to 80 or 90% and often peak at 100%. Since I increased to 8CPUs I see a peak of 60 to 80%.

Although I have a VNC view of a dozen POS terminals, there is nothing that is obvious on the performance monitor when one or more terminals freeze up.

Now it's time for me to do some database monitoring and see where the bottlenecks may be and for that I would appreciate a bit of help.

Please let me know if I can provide more information.

If someone can point me in the right direction, that would be great.

Thank you,

Elie

asked 13 Sep, 13:04

etordj's gravatar image

etordj
264
accept rate: 0%

edited 13 Sep, 13:05

Just a slight question w.r.t. 16 GB RAM: AFAIK SQL Anywhere 8 has never been available as 64-bit software, so are you using an AWE cache to use that much cache?

(14 Sep, 03:51) Volker Barth

What OS are you using?

What is the application architecture? I assume one central ASA 8 database server with 42 client processes sending client/server requests across TCPIP.

(14 Sep, 08:29) Breck Carter

The OS is Windows Server 2012 R2. I haven't (yet) made any server configuration changes to SQL Anywhere until I know more, so I don't know how much of the RAM is allocated to Cache.

The database size is about 18.2Gb and the recently truncated transaction log file is now 73.5Mb.

The POS Clients communicate with the SQLA server on ports 2804, 2805, 1184, 2439, etc... but, because the vendor assumed it was a network issue on our side, we opened up the ACLs and permitted any traffic on any port between the 42 clients and the server.

(14 Sep, 10:01) etordj

Foxhound is the only database performance monitor that works with target databases running on SQL Anywhere 8.

It does require SQL Anywhere 16 or 17 to be installed to support Foxhound's own database, and SQL Anywhere 8 doesn't provide as many performance statistics as later versions, but...

...the following screenshot of a busy SQL Anywhere 8 database with over 100 connections gives an idea of what's available:

permanent link

answered 13 Sep, 16:43

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 13 Sep, 16:43

Great! I'll give it a look.

The scary thing in all this is that there is no Dev, Test or QA environment. This production-only environment is what I adopted. It's on my to-do to clone this server in the near future.

That being said, is there any risk of installing Foxhound and SQLA 16 on the same server as SQLA 8. Will they play nice together?

(14 Sep, 10:04) etordj

Hmm. SQLA 17 is $1,000 (can't do that) and SQLA 16 Dev is difficult to find.

(14 Sep, 10:16) etordj
Replies hidden

There's also a v17 dev. edition available.

(14 Sep, 11:15) Volker Barth
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:

×248

question asked: 13 Sep, 13:04

question was seen: 117 times

last updated: 14 Sep, 11:15