Nicola
On Sunday, 29 August 2021 at 04:46:23 UTC+10, Nicola wrote:
Derek,
I've gone full speed with some benchmark scripts up and running
Excellent.
against
a pretty default ASE installation.
Bad idea. The one (benchmark) contradicts the other (simple default db).
You will be better off:
1. getting a default db geared up to a real db
__ non-default; proper distribution of tables across devices;
2. tranlog set up for purpose; etc);
__ depending on db being (a) Development (no recovery) to ... ((g) high-end Production (instant recovery)
__ [a] can be done via config ... somewhere about [c] you need a stored proc for aut-dump-when-full
3. set up monitoring (which you need for diagnostics)
__ loop between [1][2][3] until you have no problems
4. and then benchmarking, which will use [3].
So count this as [1], with those setps in mind.
I have created two devices, one for
databases ("userdbdev") and one for the transaction log ("userlogdev").
My test database was created with:
create database scratch on userdbdev = '1g' log on userlogdev = '1g'
Question, have you read the manuals and understood how the tranlog works ?
a. a newbie would set the tranlog at 10% the data size. (not scientific, but ok)
b. the tranlog needs to be dumped regularly, based on what/how/where you wish to obtain recovery. It is not a simple or binary decision.
c. for a benchmark or production you need an purpose-written sp (because it uses site-specific resources and setting)
d. I have high-activity Production dbs with 500gb data and 100mb tran-log. Ie. the tran-log size is dependent on (i) activity, and (ii) max tran size [which should be small], and (iii) how it gets dumped, which is that sp.
Eg. mine are set up for transfer to an DisasterRecovery db (for cut-over if DR ever happens), so the tranlog gets dumped far more frequently than necessary per activity.
For plain per-activity dumping, for a benchmark, which means somewhat geared up, but not full Production, use a tranlog size of 100mb or 200mb. And deal with i the issues, and get it bedded down, so that the benchmark simulates a real world scenario. (
That is what I do for a true benchmark.)
After several runs of my scripts,
I have started to get this error:
The transaction log in database scratch is almost full. Your transaction
is being suspended until space is made available in the log.
But that is reporting the tran-log in db_name “scratch” not “userdbdev” is full.
That is correct operation, but you are missing the set-up, and of course understanding.
You need to read:
_ minimum: Commands manual for DUMP DB and DUMP TRAN, there are good sections for understanding
_ better: System Admin Guide 1, complete manual
_ minimum: System Admin Guide 1, ch 3 (1p4): READ THIS RIGHT NOW
_ minimum: System Admin Guide 1, ch 8 (10p): READ THIS RIGHT NOW
In Unix, everything is case-sensitive.
SQL is case-insensitive, except for object names. The convention is to use uppercase for commands.
1. How do I flush the transaction log?
After ANY db is set up, the first thing is, you need a DB-DUMP, to recover the whole DB if necessary.
You must ensure that the logsegment is actually on the log-device, and not on the data-devices.
“flush”
It depends on exactly what you want ...
-- [A] Proper manual DB-DUMP, file_path needs to have space-used-in-db
DUMP DATABASE <db_name> to <db_file_path>
GO
-- [B] Proper manual TRAN-DUMP (very first step for understanding):
-- file_path needs to have space-used-in-tran-log
DUMP TRAN <db_name> to <log_file_path>
GO
But you are much better off defining DUMP-DEVICES.
-- [C] When you get 1105 or “log suspended”, it is usually too late, so you need:
DUMP TRAN <db_name> WITH TRUNCATE_ONLY
-- or worse:
DUMP TRAN <db_name> WITH NO_LOG
GO
Which clears the 1105 or “log suspend”, but now recovery on that db is from a DB-dump-file only. So you must DUMP DATABASE to produce a fresh one.
-- [D] Development db: set db_name to TRUNCATE tranlog automatically
-- tranlog can be very small
USE userdbdev
GO
master..sp_dboption userdbdev, "trunc log on chkpt", true
GO
CHECKPOINT
GO
==[E] Set up any db (C) to be automatically tran-log dumped when “full”. That means thresholds on the logsegment, and a threshold stored proc to be executed when the threshold is reached. There can be more than 1 threshold, more than one escape
determination. Eg. for tempdb, I TRUNCATE; for production I DUMP, for emergency in tempdb, I kill tasks (which clears their data usage).
Truncate means truncate, not dump. Therefore TRANSACTION recovery is no longer possible. Which means only DATABASE recovery possible (LOAD DATABASE <db_name> FROM <db_file_path>).
If all goes well, even reasonably, you will never have to LOAD TRAN FROM <log_file_path>. It will be DUMPED or TRUNCATED as planned, and they will be in a planned location if need be, but never get used.
Mostly, you will be doing things in a DB, and then DUMP DATABASE at the close of the DDL set up, before the activity (such as a benchmark).
When you have the benchmark tables full (eg. some set up as planned), you might DUMP DB before hammering it with UPDATE Xacts.
2. How do I monitor the size of the transaction log?
USE <db_name>
GO
sp_helpdb <db_name>
GO
(The nuance between USE vs <db_name>..sp_something will be understood later.)
sp_helplog
sp_helpsegment -- without parms
sp_helpsegment logsegment
sp_helpthreshold -- without parms
sp_helpthreshold logsegment
3. How do I avoid the above message in the first place?
(Sorry, I have the details in the reverse order. See above.)
You can’t work from the err msg backwards.
Sybase is not a toy database server (PoopGres is a toy non-server). It takes database recovery seriously. Eg. you might have just LOADed a production database, into a disaster recovery or UAT database container, and have started using it, and your log
is not set up (accident). So that tran-log is actually required for recovery, and must not be lost. Hence it is operating correctly.
You have to take responsibility as a Sybase DBA (welcome to the club).
Any and all config and settings re recovery must be made explicitly.
The first elemental thing a Sybase DBA has to do, is manage the tran-logs of ALL dbs. (For context, the second elemental thing is to manage tempdb usage.) Sybase provides various options, and methods. The problems can be eliminated/reduced by
Standards; good practice; etc. And that too, depends on (a) proper education, (b) extent of need per database, and (c) your ability to code auto-administration scripts and stored procs. Which avoids manual commands upon problem situations arising.
Starting with the notion that you can just install Sybase and code a benchmark will be a disaster, I expected that you would read at leaset the SAGs, and get to know what a real server does, how to administer it.
Otherwise the problems will be exposed via err msgs, and then you are reacting, and reacting without set up or knowledge. And this media is not the way to help you reasonably.
Generally, you have to set up, in this order:
1. think about how you want your DATA vs LOG distributed.
2. think about how you want your RECOVERY to be done (Dev vs Benchmark vs Production).
3. set up DEVICES accordingly
__ I recommend (instead of your 100gb), which must be RAW PARTITIONS (not filesystem-files):
____ 8 x 128MB for DATA (for parallelism)
____ 1 x 128MB for Non-Clustered Indices
____ 1 x 128MB for TRAN-LOG for EACH database SEPARATELY
4. set up your DATABASES accordingly, ala sp_dboption
5. either
__ (D) no recovery (Dev: TRUNC-LOG-ON-CHKPT) or
__ (A) reasonable recovery (Benchmark) or
__ (B) minimum-production-recovery (Production)
6. for (A)(B), set up a threshold at least on the logsegment, to automatically DUMP TRAN
----
a pretty default ASE installation.
Bad idea, if it is intended for a benchmark. Fine, if it is intended for dev; learning. Then you must read the SAGs, get some experience, including handling situations such as this, and then sp_configure each and every config parm that is deemed
relevant to a reasonable benchmark config.
Same as setting up your DEVICES correctly. If you don’t, the benchmark will be meaningless.
----
General
1. For admin tasks, most people use a 3rd party DBA Admin tool, such as DBArtisan.
Sybase does have a free DB Admin tool, free with a licence for ASE, a very nice GUI (not as amazing as DBArtisan which I have), but I don’t know if it is included in teh dev version (“express edition”). It is called something like “enterprise
manager”, look for it.
If you don’t, you are operating 35 years behind other Sybase DBAs, at isql + scripts level.
2. For SQL coding, most people use an IDE, such as SQLProgrammer. There are many.
When I go on short assignments, if the cust does not have [1][2], I download and use Komodo.
3. You have to learn and understand DEVICES and DATA STRUCTURES. Otherwise the benchmark would be nonsense.
First, the SAGs.
Second, a logical overview:
__
https://www.softwaregems.com.au/Documents/Article/Sybase%20Data%20Storage/1%20Data%20Storage%20Unit.html
Cheers
Derek
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)