on 2014 May 02 8:20 AM
Hi All,
You were all so helpful last time that I thought I would try again with a conundrum I am having:
•SQLA v10.0.1.3831 •Win Server 2008 64bit •DB size 6.5GB •12 GB RAM (max cache allocated 5GB) •4 x quad core •Approx 50 users
The database was migrated to this server from a much lower spec Server 2003R2 machine a few months ago. We had some initial speed problems that Breck Carter tracked down to Intra Query Parallelism which we have turned off and things improved dramatically.
During normal usage the server is dramatically faster than the old 2003 machine, (noticeably when opening windows etc.), however when it is being heavily used by reports there is a huge slowdown such that just opening a customer record can take 30 seconds as opposed to less than 1 second.
I hear you saying "well of course it would be", but we have started up the old server today and taken the current database doing the same tests. Yes opening the customer slows down on the old server as well but we are talking about taking 4 seconds as opposed to 2 seconds. All other processes are affected in the same way i.e. a factor of 20-30 times slower on the new box whereas it was 2-3 times slower on the old box.
We changed the server switches on the new box to take advantage of the increase in resources available and also added in some logging, (this might slow down but presumably not nearly the factors we are talking about). Details below:
//Old Box -x TCPIP(ServerPort=49153) -gp 4096 -ch 2024M -n XXXXSQL "D:\\Database\\Live Database\\spaceman.db"
//New Box -n BLUESQL -x TCPIP(ServerPort=49153) -gp 4096 -c 5G -ca 0 -gb high -z -zr ALL -zo c:\\MensajesDeDBLog\\ZLog.txt -zs 400M -o c:\\MensajesDeDBLog\\XXXSQL.text -os 400M -n BLUETESTSQL "D:\\Database\\Live Database\\spaceman.db" -n SpaceMan -n SpaceMan
It probably isn't a lot to go on and I understand that loading a server with heavy reports is going to slow down progress I just need to try and work out how/if I can bring it back to be more in line with the old server where performance was not radically affected.
Any ideas.
Thanks,
Alasdair
Request clarification before answering.
Thanks all for your responses. The Request Logging was the killer so well done to Dmitri and Mikel.
We had a chance to stop the service again the other day and put back all setting except the logging and everything was still fine, (even better actually due to the 5G cache).
We will still need the logging on the live database at some point, (it was Sybase who asked us for these logs), to pinpoint a problem they are having with database disconnections but we will only use it for short periods not leave it on.
Thanks again everyone.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am just curious, if you log requests to the RAMdrive, and copy them from time to time to the HDD, will the performance still be bad?
IMO it might help, but probably not... modern operating systems and disk controllers do a lot of caching, and the request level log file is a simple sequential write-only file, so chances are the final repository (hard drive versus other) won't make much difference. The far more likely culprit is the extra CPU effort required of the SQL Anywhere engine and operating system to gather and record all the detail, and perform all the high-level file write operations, for every ... single ... client ... request (and there are a lot of them... think FETCH). The ODBC trace facility is a similar feature which crushes performance.
But... it's worth trying, pointing the request level log file at a "RAM drive" or something http://windows.microsoft.com/en-us/windows/using-memory-storage-device-speed-computer#1TC=windows-7
Does an Old and New server use the same SAN? Does and Old and New Db has the same db pagesize, filesystem page size?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.