Cox Communications Powers Massive Data Warehouse with MySQL

Cox Communications is the fourth largest cable-television provider in the United States, serving approximately 6.3 million customers. A Fortune 500 company, Cox has built communications networks and delivered quality cable television programs since 1962. It is now a full-service provider of telecommunications products, including an array of multi-service broadband communications solutions. To maintain optimum performance and customer-service levels, Cox has developed a huge data warehousing application. At the heart of this business-critical system is a 2-billion row MySQL database.

Cox uses the MySQL database to manage a variety of cable modem information, maintain cable firmware, provide an interface to customer care for real-time support and expedite internal and customer trending and analysis. Additionally, the database aggregates the company's scheduled polls and mines the data for pertinent trouble-identification metrics on its RF (coaxial) plant.

Given the size and complexity of the application, the MySQL database is under constant pressure to perform. On a daily basis, Cox uses MySQL to pull data from over 1.2 million cable modems. Already the database includes more than 3,600 tables and 2 billion rows of data. Overall, the MySQL database handles approximately 4 million inserts every two hours.

"No question, this qualifies as a 'heavy load' environment," noted Mark Cotner, manager of Network Application Development at Cox. "The application runs across 27 collection servers and the database will soon be about 600GB in size. We use MySQL's replication feature to keep four copies of the database distributed across several large systems.

"Based on this level of activity, you might expect that the database is busy all the time, but in fact it is almost always idle," said Cotner. "This is a result of the speed of the MySQL database. The collection of data that occurs every two hours takes only a few minutes to complete, and the network-usage mining that happens at night takes less than three hours."

While MySQL has proven that it is qualified to easily handle Cox's large-scale, high-volume warehouse application, it has also enabled Cox to achieve an extremely low total cost of ownership.

"In today's market, we are constantly striving to keep costs down," stated Cotner. "Hardware and software costs contribute heavily to our IT budget. Leveraging the MySQL database allows us to save money without sacrificing performance."

Cox uses the "LAMP" software set, with Red Hat Linux, Apache, MySQL, PHP and Perl. The poller application is written in Perl and they also use Perl to collect data from the pollers and store it in the master MySQL database. The application has a Web-based front-end written in PHP, which enables reporting and real-time polling features.

The Cox technical environment includes IBM xSeries servers, all of which are running the Linux operating system. Two Linux boxes with 2.5 terabytes of disk space in a high availability configuration serve all of their high speed Internet customer care account lookups and real-time modem polling (approximately 100, 000 hits a day). They also aggregate Cox's scheduled polls and mine that data for pertinent trouble identification metrics on its coaxial plant.

As an industry leader, Cox relies on the ultra-fast, highly reliable MySQL database. "Judging by its performance to this point, we are confident the MySQL database can handle even greater demands," offered Cotner. "The MySQL application is central to our ability to make more informed business decisions and provide better customer care."