December 8, 2010 at 9:18 pm
Comments posted to this topic are about the item What's Happening Here?
December 9, 2010 at 3:12 am
Hi Steve
Are you missing the data for the buffer cache hit ratio graph?
Steve
December 9, 2010 at 3:21 am
stephen.sarre (12/9/2010)
Hi SteveAre you missing the data for the buffer cache hit ratio graph?
Steve
The Buffer cache hits are pegged at 100% (all that is required is found from cache).
I agree that the next things to look at are disk I/O and network contentions. I myself have seen hardware (disk) falling behind a couple of times, and predict I/O being the prime reason for performance bottlenecks in the future.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 9, 2010 at 3:26 am
Hi,
I am wondering how you send you emails. Do you send them via an exchange-server inside your network? Or do you have some external provider sending your mails?
In the last company I worked we had over the time three different approaches to send our mails so that is why I am asking. Each approach had its benefits and back draws depending where you want the workload to be.
And is this just one newsletter with one list of recipients? Or are these different newsletters (which can’t be easily separated via a switch) so the Server has to read over the same table quite a few times. With 700.000 newsletters it should not be that much workload, depending what the normal workload and performance of the server. I guess that maybe there is tuning-potential in the application sending the mails or the workflow because you know what you are doing on the SQL Server side. And in the old company as well as in the new one most of the time I help the developers pinpoint down potential misdoings in the applications or bad design because the server is on the other end, bad behavior of an application can be seen pretty clear as soon as it hits the database.
BR
Christoph
December 9, 2010 at 7:48 am
We send mail through a .NET application, which sends them individually. There is a limited customization for each one, and so the database needs to be read to send them. We actually read a batch, process on the client, send, update the database back with sending records.
700,000 emails in a few hours is no joke. We examined lots of services and processes over the years. Managing that workloads requires lots of tracking and processing to comply with SPAM laws. There potentially could be a more efficient way, and it's probably worth examining. Currently there are about 3 tables used to store the emails, the recipients, and something else (escapes me this minute). We keep about 4 days worth of tracking there, though there are logs written to local disk on the clients as well.
December 9, 2010 at 7:49 am
Steve Jones - SSC Editor (12/9/2010)
What does this tell you?
From a pure hardware point of view, NOT taking into consideration optimising the software like mu_rauer said, investigate RAIDing some SSDs.
Compare the ROI of optimising the emailing software vs. the ROI of a (few?) SSD array(s). You might want to look at OCZ's HSDL
About OCZ's HSDL:
better suited to workstations and the HPC market. Backing these projections are OCZ’s initial performance numbers. The company is claiming read and write speeds of up to 740 MB/s, and up to 120,000 IOPS. Those figures are insane. Clearly, they’re also in excess of what a single SAS or SATA interface could accommodate. And according to OCZ, this is only the first generation of the technology—it’s supposed to get faster from here.
You might also want to have a gander at this article entitled "How Do SSDs Redefine Storage Performance?". This is a comparison of two Samsung 470 SSDs vs. eight Fujitsu 15K RPM HDDs.
It is important to note that there are faster and somewhat more efficient hard drives available, which would have shifted the results a bit in favor of the hard drive arrays. Yet, the differences we see are significant enough to be sure that whatever you tweak on the hard drive side still cannot beat a fast and efficient SSD array. Also, we had to use the 15K RPM Fujitsu drives because no hard drive maker wanted to provide drives once we told them what we were planning. This alone should speak volumes.
December 9, 2010 at 7:50 am
Steve Jones - SSC Editor (12/9/2010)
700,000 emails in a few hours is no joke. We examined lots of services and processes over the years. Managing that workloads requires lots of tracking and processing to comply with SPAM laws.
Our company also had to be careful not to be marked as spammers, although we only have to send out +-4600 emails.
December 9, 2010 at 9:29 am
AndrewJacksonZA (12/9/2010)
Steve Jones - SSC Editor (12/9/2010)
What does this tell you?From a pure hardware point of view, NOT taking into consideration optimising the software like mu_rauer said, investigate RAIDing some SSDs.
Compare the ROI of optimising the emailing software vs. the ROI of a (few?) SSD array(s). You might want to look at OCZ's HSDL
About OCZ's HSDL:
better suited to workstations and the HPC market. Backing these projections are OCZ’s initial performance numbers. The company is claiming read and write speeds of up to 740 MB/s, and up to 120,000 IOPS. Those figures are insane. Clearly, they’re also in excess of what a single SAS or SATA interface could accommodate. And according to OCZ, this is only the first generation of the technology—it’s supposed to get faster from here.
You might also want to have a gander at this article entitled "How Do SSDs Redefine Storage Performance?". This is a comparison of two Samsung 470 SSDs vs. eight Fujitsu 15K RPM HDDs.
It is important to note that there are faster and somewhat more efficient hard drives available, which would have shifted the results a bit in favor of the hard drive arrays. Yet, the differences we see are significant enough to be sure that whatever you tweak on the hard drive side still cannot beat a fast and efficient SSD array. Also, we had to use the 15K RPM Fujitsu drives because no hard drive maker wanted to provide drives once we told them what we were planning. This alone should speak volumes.
I'm not sure how much of a budget I have to change hardware, especially as this works. There is a few minute slowdown as things ramp up, but overall the site performs well during sending. However I would definitely consider SSDs for something like this if performance were an issue.
December 9, 2010 at 10:21 am
Steve,
While exploring SQL Monitor's public demo this morning I DM'd @sqlmonitor the following...
Noticed on sqlmonitor public site, Autogrowth for Files set to defaults on 252314-sql1 sqlservercentral instance. PBM would flag this.
Autogrowth increment set to 1 MB for the sqlservercentral data file and 10% for the log file.
Policy Based Management would catch the use of a percentage for growth. The 1 MB growth increment is too small and a custom policy could be setup to catch that.
@sqlmonitor said this was done on purpose for some of your future blog posts and that I had caught it prior to the posts beginning.
I just love the idea that I can monitor machines with just a browser.
Regards,
December 9, 2010 at 10:43 am
I'm looking to write something up on that. Once I get some screenshots and we schedule the maintenance, we'll change that. Part of what we are showing is that we make mistakes like everyone else. The website is a portion, and not the main portion, of the business.
December 9, 2010 at 5:35 pm
From the view of database architect, sending emails is a business functionality and need to be hosted in a separate server as separate application! The application needs to read sqlserver!
R
December 9, 2010 at 11:39 pm
Steve Jones - SSC Editor (12/9/2010)
I'm not sure how much of a budget I have to change hardware, especially as this works. There is a few minute slowdown as things ramp up, but overall the site performs well during sending. However I would definitely consider SSDs for something like this if performance were an issue.
The thing is, is this even an problem? It's something that was noticed, but is it really a problem for you guys? Would the time and money that would be used to address this potential problem be better spent on something else?
December 10, 2010 at 1:51 am
Prakash-205770 (12/9/2010)
From the view of database architect, sending emails is a business functionality and need to be hosted in a separate server as separate application! The application needs to read sqlserver!
This seems like an interesting idea to me. If things get worse or if the performance-impact of sending newsletters (Steve said there is a little bit of slowdown at the beginning of the sending-process) is not accepted you can set up a replication of the newsletter-data to another server with SSDs. This one does not have to be clustered or as secure as any other server because it will only be there for the data-gathering so it will be easier to get the budget for it. So you would get the workload away from the productive system, disk as well as bandwidth. The response to these emails like bounce code and so on could be written directly back into the live system so it is not necessary to take too much care if this server runs all the time as long as it is up for the newsletters (a little bit before to synchronize the subscription) and the subscription does not time out.
If the application runs on a standalone-server without any other applications you may even set up an SQL Server there as a subscriber to reduce the high bandwidth-usage if this seems to get worse and may have a big impact on the site in the future.
Maybe this would be a relatively cheap solution to possible problems without too much reengineering of the applications and only with a short downtime if any at all (Locks while creating the snapshot for the replication if you start with a snapshot).
Another benefit could be that if the bottleneck is the SQL Server and not network or mailserver that with this architecture the sending could be splittet up more than one server. But maybe you don't want to put that much workload on the mailserver as well as the network.
December 10, 2010 at 8:55 am
Prakash-205770 (12/9/2010)
From the view of database architect, sending emails is a business functionality and need to be hosted in a separate server as separate application! The application needs to read sqlserver!
It does. We have 3 other computers with a client application that reads SQL Server, then sends the emails.
December 10, 2010 at 8:56 am
AndrewJacksonZA (12/9/2010)
Steve Jones - SSC Editor (12/9/2010)
I'm not sure how much of a budget I have to change hardware, especially as this works. There is a few minute slowdown as things ramp up, but overall the site performs well during sending. However I would definitely consider SSDs for something like this if performance were an issue.The thing is, is this even an problem? It's something that was noticed, but is it really a problem for you guys? Would the time and money that would be used to address this potential problem be better spent on something else?
It's not really a problem now. We had a hardware refresh in 2010, so things are actually running better. We moved from physical servers to virtual ones, and it's working great.
We have to balance resources, and there are other things to fix.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply