Discussion :

[jfmerveille]

Bonjour,

Les performances de mon Dataserver Sybase sont un peu justes et je souhaiterai savoir comment optimiser les performances du dataserver Sybase ; ajuster les paramètres de celui-ci (dans le fichier de config)

J’ai pensé à modifier les 2 paramètres suivants :
default network packet size = 16384
max network packet size = 16384

D’avance merci pour vos idées


Architecture
Serveur HP N4000
6 CPU à 550MHZ
10 Go de RAM
SGBD sur 450 Go de Disk
OS : HP Unix 11.00 + Sybase 12.0.8

Fichier de configuration Sybase actuel
[Configuration Options]
[General Information]
[Backup/Recovery]
recovery interval in minutes = DEFAULT
print recovery information = DEFAULT
tape retention in days = DEFAULT
[Cache Manager]
number of oam trips = DEFAULT
number of index trips = DEFAULT
procedure cache percent = 12
memory alignment boundary = DEFAULT
global async prefetch limit = DEFAULT
global cache partition number = DEFAULT
[Named Cache:default data cache]
cache size = DEFAULT
cache status = default data cache
cache replacement policy = DEFAULT
local cache partition number = 16
[4K I/O Buffer Pool]
pool size = 400.0000M
wash size = 61440 K
local async prefetch limit = DEFAULT
[16K I/O Buffer Pool]
pool size = 3000.0000M
wash size = 61440 K
local async prefetch limit = 20
[Meta-Data Caches]
number of open databases = DEFAULT
number of open objects = 5000
open object spinlock ratio = DEFAULT
number of open indexes = 5000
open index hash spinlock ratio = DEFAULT
open index spinlock ratio = DEFAULT
[Disk I/O]
disk i/o structures = DEFAULT
page utilization percent = DEFAULT
number of devices = 245
disable disk mirroring = DEFAULT
disable character set conversions = DEFAULT
enable unicode conversions = DEFAULT
size of unilib cache = DEFAULT
[Network Communication]
default network packet size = 2048
max network packet size = 65536
remote server pre-read packets = DEFAULT
number of remote connections = DEFAULT
number of remote logins = DEFAULT
number of remote sites = DEFAULT
max number network listeners = DEFAULT
tcp no delay = 1
allow sendmsg = DEFAULT
syb_sendmsg port number = DEFAULT
[O/S Resources]
max async i/os per engine = DEFAULT
max async i/os per server = 256
[Parallel Query]
number of worker processes = 64
memory per worker process = 1536
max parallel degree = 13
max scan parallel degree = 6
[Physical Resources]
[Physical Memory]
total memory = 3670016
additional network memory = 256000
shared memory starting address = DEFAULT
max SQL text monitored = 4096
[Processors]
max online engines = 5
min online engines = DEFAULT
[SQL Server Administration]
default database size = DEFAULT
identity burning set factor = DEFAULT
allow nested triggers = DEFAULT
allow updates to system tables = DEFAULT
print deadlock information = DEFAULT
default fill factor percent = DEFAULT
default exp_row_size percent = DEFAULT
number of mailboxes = DEFAULT
number of messages = DEFAULT
number of alarms = DEFAULT
number of pre-allocated extents = 31
event buffers per engine = 20000
cpu accounting flush interval = DEFAULT
i/o accounting flush interval = DEFAULT
sql server clock tick length = DEFAULT
runnable process search count = DEFAULT
i/o polling process count = DEFAULT
time slice = DEFAULT
deadlock retries = DEFAULT
cpu grace time = 2000
number of sort buffers = 20000
number of large i/o buffers = 32
size of auto identity column = DEFAULT
identity grab size = DEFAULT
page lock promotion HWM = DEFAULT
page lock promotion LWM = DEFAULT
page lock promotion PCT = DEFAULT
housekeeper free write percent = DEFAULT
enable housekeeper GC = DEFAULT
partition groups = DEFAULT
partition spinlock ratio = DEFAULT
allow resource limits = DEFAULT
number of aux scan descriptors = DEFAULT
SQL Perfmon Integration = DEFAULT
allow backward scans = DEFAULT
row lock promotion HWM = 20000
row lock promotion LWM = 19000
row lock promotion PCT = DEFAULT
license information = DEFAULT
enable sort-merge join and JTC = DEFAULT
abstract plan load = DEFAULT
abstract plan dump = DEFAULT
abstract plan replace = DEFAULT
abstract plan cache = DEFAULT
text prefetch size = DEFAULT
number of histogram steps = DEFAULT
[User Environment]
number of user connections = 100
stack size = DEFAULT
stack guard size = DEFAULT
permission cache entries = DEFAULT
user log cache size = 4096
user log cache spinlock ratio = DEFAULT
enable HA = DEFAULT
enable DTM = DEFAULT
allow remote access = DEFAULT
lock shared memory = DEFAULT
allow sql server async i/o = 0
[Lock Manager]
number of locks = 8000000
deadlock checking period = DEFAULT
freelock transfer block size = DEFAULT
max engine freelocks = DEFAULT
lock spinlock ratio = DEFAULT
lock address spinlock ratio = DEFAULT
lock table spinlock ratio = DEFAULT
lock hashtable size = 4096
lock scheme = DEFAULT
lock wait period = DEFAULT
read committed with lock = DEFAULT
[Security Related]
systemwide password expiration = DEFAULT
audit queue size = DEFAULT
curread change w/ open cursors = DEFAULT
allow procedure grouping = DEFAULT
select on syscomments.text = DEFAULT
auditing = DEFAULT
current audit table = DEFAULT
suspend audit when device full = DEFAULT
max roles enabled per user = DEFAULT
check password for digit = DEFAULT
minimum password length = DEFAULT
maximum failed logins = DEFAULT
unified login required = DEFAULT
use security services = DEFAULT
msg confidentiality reqd = DEFAULT
msg integrity reqd = DEFAULT
dtm detach timeout period = DEFAULT
secure default login = DEFAULT
[Extended Stored Procedure]
esp unload dll = DEFAULT
esp execution priority = DEFAULT
esp execution stacksize = DEFAULT
xp_cmdshell context = DEFAULT
start mail session = DEFAULT
[Error Log]
event logging = DEFAULT
log audit logon success = DEFAULT
log audit logon failure = DEFAULT
event log computer name = DEFAULT
[Rep Agent Thread Administration]
enable rep agent threads = DEFAULT
[Component Integration Services]
enable cis = DEFAULT
cis connect timeout = DEFAULT
cis bulk insert batch size = DEFAULT
max cis remote connections = DEFAULT
max cis remote servers = DEFAULT
cis packet size = DEFAULT
cis cursor rows = DEFAULT
cis rpc handling = DEFAULT
[Java Services]
enable java = DEFAULT
size of process object heap = DEFAULT
size of shared class heap = DEFAULT
size of global fixed heap = DEFAULT
[DTM Administration]
enable xact coordination = DEFAULT
xact coordination interval = DEFAULT
number of dtx participants = DEFAULT
strict dtm enforcement = DEFAULT
txn to pss ratio = DEFAULT
dtm lock timeout period = DEFAULT
[Diagnostics]
dump on conditions = DEFAULT
maximum dump conditions = DEFAULT
number of ccbs = DEFAULT
caps per ccb = DEFAULT
average cap size = DEFAULT
[Monitoring]
Q diagnostics active = DEFAULT
sql text pipe active = DEFAULT
sql text pipe max messages = DEFAULT
plan text pipe active = DEFAULT
plan text pipe max messages = DEFAULT
statement pipe active = DEFAULT
statement pipe max messages = DEFAULT
errorlog pipe active = DEFAULT
errorlog pipe max messages = DEFAULT
deadlock pipe active = DEFAULT
deadlock pipe max messages = DEFAULT
wait event timing = DEFAULT
process wait events = DEFAULT
object lockwait timing = DEFAULT
SQL batch capture = DEFAULT
statement statistics active = DEFAULT
per object statistics active = DEFAULT

[mpeppler]

Avant de commencer à modifier les configs il faudrait exécuter des sp_sysmon durant les périodes d'activités pour voir où se situent les problèmes.

Ensuite on pourra réflechir aux modifications qui pourraient être envisagées.

Michael

[remi.miage]

Je te conseillerai avant de changer quoi que ce soit de surveiller ton activité et de tenter de trouver où est la charge ?.
Lorsque tu observers les ralentissements, y a-t-il un pic de cpu, d'io, de paquets réseaux, ou d'attente en vérouillage ?

La mise en place de Monitor Server et d'Historical Server peut également t'aider à collecter des infos sur ton serveur et ainsi faire ton diagnostique.

[Fabien Celaia]

Déjà, le default packet size devrait être assez proche de la trame réseau (1024, c'est souvent pas mal)
Après, libre à vous de tester avec un max packet size plus haut, mais dans ce cas, n'oubliez pas de passer le additional network memory