|
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 |
|
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. 1
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?
(21 May '14, 08:59)
Vlad
Replies hidden
1
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
(21 May '14, 09:12)
Breck Carter
|
|
Does an Old and New server use the same SAN? Does and Old and New Db has the same db pagesize, filesystem page size? Hi, it is on a new SAN, (but in theory a better, newer one). The DB's are exactly the same as far as pagesize, etc., (it is the same database just moved to a new server). Thanks for the reply.
(06 May '14, 05:37)
RADicalSYS
|
In Control Panel >> Power Options, make sure you are on a 'Maximum performace' power plan. 2008R2 tries to use the CPU in an energy efficient way, but this sometimes has unexpected side effects. Try watching the task manager CPU usage when the report is running, are you maxing out one core to 100%? With parallelism turned off, the individual report can only use one of the cores. In the perfmon.exe disk tab, is dbsrv10.exe using a large number of B/sec during the report? Is the database on the same physical drive or a new one? Is it directly attached to the machine? Does the issue occur without the -zr ALL switch? That switch can have a high impact on performance.
First of all, turn off request logging. Sometimes, it simply kills performance outright. I would never turn it on in production.
Thanks for the replies.
Mikel - It looks like the server is on a balanced power plan so we will change that. When running one heavy report we get 1 core maxed out, two reports at the same time 2 cores maxed out, 3 reports doesn't quite max out 3 cores and 4 reports doesn't change much from 3 reports but there is a marked performance difference once we get to the 4 reports for basic tasks in the database. Not a lot of disk activity going on when reporting but with 5GB of cache over 75% of the database is in cache. Disks are in a SAN.
Dmitri and Mikel - We wondered about the ZR logging. It was only there to give reports to Sybase on an odd disconnection issue we have seen.
Thanks again for the replies. We have gone back to the old server's database switch settings i.e. removed logging, dropped down cache, priority level, etc. and things are much better.
Overall performance is still fine but we don't get the huge drop performance when hitting the database hard for reports.
Obviously there are a lot of things that could be the issue i.e. request logging, cache options, etc. and we don't know what has solved it.
For the moment the client is loathed, (or rather there is not a chance in hell), for us to keep stopping their service and playing with setting to find out what is the issue but we will start changing options over the next few weeks and if I find the problem will report back.
Of course if anyone has a definitive answer in the meantime then please let me know. I am tempted to go with the logging!
The -z and -zr all options are completely different. The -z option produces a mildly verbose amount of diagnostic information about disconnections and such like, which probably applies to the "give reports to Sybase" requirement.
The -zr all option, however, results in extreme logorrhea... you absolutely positively do NOT want to turn that on in production unless you have an immediate need for the output.
You can turn -zr on and off at runtime without bouncing the server; see sa_server_option() in the V10.0.1 Help... scroll down to the last example.
Be sure to read all about sa_server_option (see above)...
it can be used to turn a lot of stuff on and off in a non-loathsome manner.
...hey, don't be yelling at me about how lovely limburger is, it's what Google popped up on an image search :)