Hello,

I am looking for some guidance and recommendations for getting the maximum value out of the server console logs. As of course you know these logs accumulate a huge amount of data but most of it of course turns out to be of no great value as long as nothing is going wrong.

How do those in the Community who are responsible for managing the servers analyze their logs in a time effective manner with an eye towards identifying problems that may be developing but that have not yet risen to a level of high severity? What should a db admin be looking for? I am interested in both recommended best practices as well as specific tools.

Thank you.

asked 14 Jul, 16:29

AlK's gravatar image

AlK
3901527
accept rate: 50%

1

FWIW "gathering / merging / presenting dbsrv.exe -o console logs" has been a personal to-do item for a long time, but never implemented because its value has always seemed outweighed by the difficulty.

> problems that may be developing but that have not yet risen to a level of high severity

The console logs do have some information that's not found elsewhere (e.g., HA diagnostics), and they are the home for many application-generated MESSAGE diagnostics, BUT they are definitely not the first place to look for "developing problems".

[Advertisement]

"Foxhound quickly became an integral part our Sybase 17 troubleshooting. It was fast and easy to set up and is even easier to configure. There are a huge number of options on what it will monitor for you and all can be set up within a minute or two of getting into the product.

We are currently monitoring over 70 Servers and almost 400 databases with it. The history feature allows us to go back and see what was going on in a system at any given time of the day when there are issues. It also does things like auto drop a user that is causing a problem and then email you what was going on when it happened so I can reach out to user and solve issues they are having before anyone really knows there is an issue.

  • Matthew Kelly, DB Engineer II, Ticketmaster

[/Advertisement]

(15 Jul, 08:20) Breck Carter

> I am interested in both recommended best practices as well as specific tools.

Recommendations

The dbsrv -o server message text files should be captured and preserved for all multi-user production servers.

In particular, the High Availability feature writes quite a bit of diagnostic information to those text files, so they absolutely must be preserved for all the partner, arbiter and read-only scale-out servers.

Use the .txt extension for these files, not .log, because they are not transaction log files ( in fact, try not to even speak the word "log" in case the listener is not as knowledgeable as you are :)

In some cases, the -o filename.txt option is sufficient because the file doesn't grow very large... however, it is always safe (and sometimes necessary) to use both options -o filename.txt -os xxM.

Pick the -os xxM value so it's not so small you get a huge number of yymmddxx.slg files, but not so large that you cannot quickly browse the files. Personally, I like -os 10M for use with Jarte (yes, I have upgraded from Wordpad! :)

If you don't want the administrative headache of deleting old yymmddxx.slg files, use -o filename.txt -on xxM instead... you'll never have more than two files (filename.txt and filename.old) but the old filename.old will get overwritten each time the -os xxM limit is reached.

Current Tools

As far as I know, there are no tools specifically designed for managing the server message text files. However, recent messages are preserved inside the server for a while, and there are tools for that.

Note: The recent messages stored internally contain more information than the text files; see the sa_server_messages() procedure for a list of fields.

The SQL Anywhere 17 Cockpit displays messages from a single server as follows:

  • Last 10 error-level messages in reverse chronological order, on the HOME tab. Only the timestamp and message text fields are displayed, and messages with exactly the same timestamp may appear out of order.

  • All message in reverse chronological order, on the MESSAGES tab. All the fields except message id are displayed, but it appears the messages are correctly ordered by message id. There is a LIKE '%whatever%'-style of dynamic filtering on the message text, but not the time, severity, category or database fields.

The SQL Anywhere 16 dbconsole.exe utility displays the server message text fields from a single server in chronological order. It does not display the timestamp, severity, category or database fields, and it does not offer any filtering.

SQL Anywhere 17 does not ship with dbconsole, but the version 16 dbconsole will connect to version 17 servers if all you want to see are connections and messages.

IMO the Cockpit MESSAGES filter is a nice feature, certainly a big improvement over dbconsole. However, pretty much everyone I know still use their favorite text editors for browsing the text files.

Tip: You can get around one of the Cockpit's many flaws by granting yourself permission to use it: grant ROLE COCKPIT_ROLE to dba

DIY Tools

SELECT * FROM sa_server_messages() ORDER BY msg_id DESC is the place to start.

Future Tools

I have started working on a proof-of-concept / prototype that will gather, preserve, merge and display messages being produced by a group of SQL Anywhere servers; e.g., an "HA Group" consisting of arbiter, partner1, partner2 and one read-only scale-out copy database. This would make the dbsrv -o text files unnecessary.

This may become a patch for Foxhound 4, or a future feature in Foxhound 5, or . . . it may become nothing :)

Feature suggestions are welcome: breck dot carter at gmail dot com

permanent link

answered yesterday

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

edited yesterday

I was waiting for the answer from somebody before writing this comment :) Because I don't know how people use SA without Foxhound, but I know how Java&Web developers live.

In the modern world people use things such as logstash. I have to admit that you have to configure this solution to support logs from SA, because they don't have a fixed template (e.g. how Apache HTTP logs look like), but this is what might help in cases when you have millions of lines, hundreds of servers, etc.

(yesterday) Vlad
Replies hidden

> they don't have a fixed template

If you are referring to the structure of the message text field, that's true; the messages are unstructured and (mostly) undocumented.

Are there any existing logstash configurations for similar inputs, that could be used as a model or template? (I couldn't find any)

(yesterday) Breck Carter

This is the sad thing, Breck :( I was talking to one of the SA guys that the logs they are unstructured. It is probably easier to follow the error if you have the source code/book and you can track what the server (either SA, or Mobilink) was doing.

I can only assist you with a small tutorial, that might be a good start if you want to learn Logstash.

Usually, what I saw from other people - the unstructured logs are collected with the filter such as "time, thread and text-whatever-server-writes". When they aggregate those logs from different servers (proxies, application servers, something else) they track them with Kibana.

I do not tell you to stay with Elastic products. But they are somehow free (plus have good tutorials) and they give you the understanding how the modern world works with logs. It is a powerful thing...

I wish relay servers -> outbound enablers -> mobilink servers -> hopefully DBs will have the fixed chain of logs so you can track a single user request up to the backend for the troubleshooting. It is so difficult to track lost or corrupted packets when something happens and everything you see is the error message on the device :) (I don't say that it is impossible to find out the cause of the problem, but you should understand what I mean... things such as howtoopenonemillionlineslogfile.exe).

I hope you liked my comment, or at least found it helpful :)

(15 hours ago) Vlad
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:

×34

question asked: 14 Jul, 16:29

question was seen: 65 times

last updated: 15 hours ago