Home > How Query Sampling Improves Database Performance

How Query Sampling Improves Database Performance

Given the overwhelming importance of data to organizations, anything they can do to speed up troubleshooting problems in the databases they use is of great value. If a company can speed up or avoid troubleshooting, it frees up time they can invest in doing and building more with their data. Careful attention to database query construction and execution also pays similar dividends, as improved database performance helps organizations get more done faster.

The Benefits of Database Performance Monitoring

Database performance monitoring helps organizations use their database management systems more effectively and productively. This is especially true for well-known open-source databases like Amazon Aurora, MongoDB, MySQL, PostgreSQL, and Redis. In fact, none of these databases are known for providing notable power, ease of use, or automation from built-in tools and consoles. Thus, it’s entirely worthwhile to put a capable software as a service (SaaS)-hosted tool to work around the clock monitoring the performance of these platforms along with the queries and applications calling upon their data. Continuously running a capable, general-purpose database performance monitoring tool to measure database health and performance offers definite benefits. First and foremost, organizations obtain ongoing visibility into the availability and responsiveness of their databases. Second, database performance monitoring provides historical metrics and constant current readings. By comparing historical metrics against current values, database performance monitoring contextualizes these values. Thus, it uses history to display trends and helps administrators understand how current readings stack up against prior averages and typical baselines. Furthermore, a comprehensive database performance monitoring tool sees an organization’s databases wherever they live: on-premises, in the cloud, or in hybrid implementations across multiple clouds and on-premises elements. Time is of the essence when it comes to identifying and addressing the root causes of database issues. A well-built database performance monitoring tool helps speed resolution in production environments. Likewise, such a tool can guide the construction of database applications and queries. DBAs and developers can use database performance monitoring tools to compare specific queries and applications before and after changes are made. This provides detailed insights into resulting performance and resource consumption impacts. Using database performance monitoring tools in this way enables better-optimized and more efficient database designs; faster, more efficient queries; and better-behaved database applications.

Case Study: Challenges in Scaling Transactional Emails

SendGrid has a cloud-based customer communication platform built around transactional and marketing emails. Lots of emails, in fact: SendGrid delivers nearly 20 billion customer engagement emails every month to internet and mobile-based customers of organizations like Airbnb, Foursquare, HubSpot, LinkedIn, Pandora, Spotify, and Uber as well as more traditional enterprises like Costco, Intuit, Taco Bell, and Walmart. The company’s systems handle mind-boggling amounts of email and web traffic, delivering and measuring the delivery of messages at massive scale. The company’s high transaction rates come from email transmissions, but they also involve large numbers of message opens, spam reports, click tracking, and interactive record access activity. In fact, according to a SolarWinds® case study, the scale of SendGrid’s database activities “broke known limits of MySQL performance and scalability.” The large MySQL data set sizes involve not only email messages but internal data (which includes link tracking and access/usage monitoring). SendGrid runs scores of MySQL database instances, some with tens of billions of rows consuming multiple terabytes of storage per instance. In general, SendGrid puts high resource demands on the hardware on which its MySQL databases run. Their typical configurations are anything but plain vanilla and incorporate high-performance SSDs and cutting-edge multi-CPU, multi-core servers (two or four 32-plus core CPUs per server card, with upwards of 256GB of RAM per server). These capabilities are vital to delivering high I/O rates and supporting giant in-memory data collections consuming major CPU and storage resources. All this activity drives SendGrid’s servers and infrastructure to the edges of their capabilities across the board. At SendGrid, massive and rapid growth posed further challenges for developer and operations teams trying to answer the following key database questions:
  • Where are the heaviest consumers of resources?
  • What process spends the most time in a database instance?
  • Which applications and code access which data?
  • Do any servers have performance issues? If so, what’s causing them?
  • What causes changes in specific data? Which applications and servers issue related queries?

Achieving Query Insights

SendGrid depended on query log reports to mine the data it needed to answer those questions. Producing and analyzing the necessary reports imposed major performance costs on servers. In fact, these reports took so much effort to produce and interpret that they were often outsourced to a remote DBA team and were only infrequently available. Consequently, important but minor tasks could get delayed for days and required scheduled database downtime and late-night or overnight work to complete. As a case in point, SendGrid found itself tracking some vexing and mysterious “shouldn’t happen” changes to data. Similar prior issues were difficult to diagnose and required improvised methods to detect and analyze. This spurred SendGrid to look at SolarWinds Database Performance Monitor for help. Its sophisticated query sampling was able to quickly and easily address SendGrid’s problem. The company’s staff simply configured the SolarWinds Database Performance Monitor agent’s sampling features to prioritize queries changing the focus data. The deployment got immediate results. Because SolarWinds Database Performance Monitor is cloud-based and uses metrics from the database engine, the monitoring imposed only negligible overhead in MySQL database instances under review. Net result: what had been a huge headache became a simple routine report. In another case, SendGrid noticed for some database instances, replication delay grew way past normal bounds on an important cluster. Further investigation showed this delay was not related to increased mail volume. An in-context database analysis using SolarWinds Database Performance Monitor showed another database running in parallel on the cluster had an impact on replication. This was then easily remedied by rescheduling the competing tasks so they didn’t overlap with replication intervals. Overall, daily reports from SolarWinds Database Performance Monitor provided useful insights into performance. This meant the DBAs no longer had to generate and dig into tedious reports detailing query performance metrics. Instead, the SolarWinds Database Performance Monitor reports—which involve no performance overhead—proved “easier to read, [got] delivered to a much broader audience, [saved] us money every quarter, and [allowed] our people to spend… time on more fun and challenging work,” according to SendGrid Manager of Product Operations Chris McDermott. Better still, immediate visibility into database behavior and performance helped SendGrid save money on hardware by pinpointing where resources were needed rather than throwing hardware all around to solve specific issues. Sound intriguing? If so, visit the SolarWinds Database Performance Monitor homepage or watch the Get Complete Visibility for Faster Application Troubleshooting webinar video to see what it can do for you.
Avatar photo
Ed Tittel
Ed Tittel is a 30-plus year veteran of the IT industry who writes regularly about cloud computing, networking, security, and Windows topics. Perhaps best known…
Read more