Clickability is the global leader in on demand Web Content Management, serving over 350 million page views per month for hundreds of customers. As the first platform-as-a-service for the entire Web content lifecycle, Clickability enables customers to build stronger customer relationships and grow revenue faster.
The Business Challenge
Clickability uses MySQL to store the core application data which is used across multiple applications to serve Clickability’s customers’ websites, including the delivery of 500+ million advertisements and 10+ million emails every month. With such high traffic volume, even small problems in the SQL code can result in a huge waste of time and computing resources. Therefore, improving database efficiency and optimizing the most frequently-used queries are among the top priorities for Clickability.
The MySQL Solution
MySQL Query Analyzer
Clickability uses the MySQL Query Analyzer to improve the performance of its database queries. The MySQL Query Analyzer is more elegant and comprehensive than custom shell scripts and offers a level of detail beyond the basics. Through a single panel that consolidates query activity and execution statistics, the MySQL Query Analyzer provides Clickability with significant insight into the performance of its publishing systems, offering a way to measure the impact of deploying new features.
The Clickability Platform currently supports 8,000 users for content creation and management. This results in 100GB of new content being generated every month and 10,000 query executions every second against the MySQL databases. The MySQL Query Analyzer allows Clickability to focus their performance-tuning efforts in areas with the biggest opportunity for improvement, including the most frequently-used queries, the queries returning the largest result sets and the queries utilizing the most processing time. Next, by using the MySQL Query Analyzer sorting and tagging functions, Clickability can spot expensive queries, track the usage frequency and resource consumption, and then modify the application code and SQL statements to optimize these items.
With the help of the MySQL Query Analyzer, Clickability investigated the cost of the most frequent queries and successfully identified a time-consuming query which performed summary and sorting functions but with no constraint on the size of its result set. By enhancing the application code to take over the sorting function and limit the result set, Clickability was able to reduce the database load and double the publishing application’s performance.
One major benefit of the MySQL Query Analyzer, according to Clickability, is that it immediately generates meaningful data and actionable items, leading to significant performance and scalability improvements. By identifying the bottlenecks and removing them early in the development and deployment cycle, Clickability saves time and money in debugging, gets better utilization of their hardware, and scales with fewer resources. All of these improvements have a direct impact on Clickability’s profit margin and bottom line.
MySQL Enterprise Monitor
Clickability also leverages the capabilities of the MySQL Enterprise Monitor, including the Replication Monitor and Enterprise Dashboard, to simplify database administration. The MySQL Enterprise Monitor provides a wide range of valuable data to help Clickability gain insight into the performance of its replicated MySQL systems, including latency and lag times.
With the MySQL Enterprise Monitor, SysAdmins can start monitoring and administrating MySQL databases quickly and be productive instantly. This not only scales Clickability's MySQL database system, but also enables them to scale their operations resources. Without a dedicated DBA on its 30 MySQL servers, Clickability relies on its operations team to cover both day-to-day system operations and database administration. By providing a single interface and easily-accessible information, the MySQL Enterprise Monitor helped Clickability smoothly expand its operations team from three to six SysAdmins.
With the easy-to-use MySQL Enterprise Monitor, even Clickability’s newest team members can identify and evaluate issues quickly. This would not have been possible if Clickability were using a proprietary database, which typically would require multiple full-time DBAs to manage the same number of database servers.