Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Server Performance after RAM upgrade

Posted on 2008-06-17
8
Medium Priority
?
442 Views
Last Modified: 2013-11-10
Pervasive SQL 9.1
Windows 2000 SP 4 on Intel XEON 2ghz machine

I was having performance issues on this server on one particular query.  I copied the entire database to a newer machine and had no problems.  The query would run for up to 60 seconds on the older machine, in my web application it would of course time out.  On the newer machine the query ran anywhere form instantly to 2 seconds.

The old machine only had 500 MB RAM so my first choice was to upgrade the RAM.  So I added 2 GB to the old server.

Here is the problem.  Now all of the queries run in about the same amount of time.  7-12 seconds.  That is of course a big improvement on the one query I was having trouble with and that speed is almost acceptable.  The bad thing is that the queries that ran instantly on the old RAM configuration now take as long as the slow one (7-12 seconds)

I monitored the database while accessing the website and the lag time is in the database or at least it is staying connected to the table while the wait time is happening.

My Processor load never goes over 57% during the queries.  Some of these (now) slow queries are single row indexed selections

I have attached the configuration for performance tuning and memory usage.  I have not adjusted them.



config1.gif
config2.gif
0
Comment
Question by:bcolladay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21804739
A few ideas:
1) Increase the L1 database cache from 100MB to 400MB.  With 2GB of RAM, you have plenty of room for L1 cache, which is where the real work gets done, anyway.
2) Try patching the engine.  V9.1 can be a bit on the unstable side, and num erous improvements were added in both v9.50 and in v9.52.  Get both from Pervasive's web site.  You must apply v9.50 patch FIRST, and then do the 9.52 update afterwards.  Be sure to do both clients AND the server.
3) Monitor the database using Pervasive's Monitor and watch the database connections from the Active Users screen.  See how many disk versus cache accesses are being generated.
4) Try running your queries manually from the Pervasive Control Center (PCC) and see if you see the same results there.  It could be a connection-delay issue and not related to query performance at all.  
5) If they are still slow there, try enabling the Query Plan Viewer and review the query to make sure that indices are available for the query to optimize on.
0
 
LVL 4

Author Comment

by:bcolladay
ID: 21805037
I tested my query from control center and it runs instantly.

I watched the active user monitor and it shows overwhelmingly Cache access for all users.  A ratio of about 2000 cache accesses for every 1 disk access.

We were getting ready to do the 9.5 patch when we noticed the slowness after ram.

I'll change the L1 cache next chance we get to restart.

Also the windows swap file was set at 4gb minimum and maximum.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21805071
If the query runs instantly, then the query or the database is not the problem.  Perhaps you have a problem in the web server with name resolution to the database.  Make sure that the DNS servers are set correctly on the server, and that it you do a DNS lookup, you get the right address instantly.

You could also try getting a network capture from the web server doing a single function call, and see where the time goes...
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 4

Author Comment

by:bcolladay
ID: 21805318
Okay, it is sporadic.

The same query I ran that was instant on the local Control Center a few munites later was taking 10 seconds or more.  And the sporadic nature is the same on the local control center and the web site.  The local network users are also seeing sporadic slowness.

Since this happened right after a RAM upgrade does it sound like it could be bad RAM?  

Can I set my L1 cache to 1 GB? I currently have it set at 100 MB and my available RAM in Task Manager shows 1.8 GB avail
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 1000 total points
ID: 21805559
You can, but it is not recommended.  The PSQL process, like all other 32-bit processes, are limited at 2GB of virtual memory space.  If you opt for 1GB of L1 cache, then you'd want to disable L2 cache (Max Microkernel Memory Usage = 0).  Even so, with SQL users, each new connection creates a new SQL thread, which increases memory use even further, so be sure to monitor your "Virtual Bytes" value in PerfMon (not TaskMan, which is a different value).

When you see slowness, what does the disk:cache ratio look like?  If you see lots of disk activity, then it means that your database is much larger than cache, and pages are getting swapped out.  
0
 
LVL 4

Author Comment

by:bcolladay
ID: 21806250
Well now I can't get it to slow down....
0
 
LVL 4

Author Comment

by:bcolladay
ID: 21812772
I will come back after I get a chance to upgrade to 9.5x and reset the memory settings on the server.  This machine is in another city and can't be restarted during the day.  I have to schedule the down time with the client.

Thanks for your help.
0
 
LVL 4

Author Closing Comment

by:bcolladay
ID: 31468026
I upgraded to 9.5x on the server engine and all of the clients.  Oddly enough after the first morning of the RAM upgrade I haven't had the slow down again.  I am accepting this answer because of the memory performance tuning suggestions.

THanks
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question