40/sec to 500/sec

Introduction

Surprised, by the title? well, this is a tour of how we cracked the scalability jinx from handling a meagre 40 records per second to 500 records per second. Beware, most of the problems we faced were straight forward, so experienced people might find this superfluous.
Contents

* 1.0 Where were we?

1.1 Memory hits the sky
1.2 Low processing rate
1.3 Data loss :-(
1.4 Mysql pulls us down
1.5 Slow Web Client

* 2.0 Road to Nirvana

2.1 Controlling memory!
2.2 Streamlining processing rate
2.3 What data loss uh-uh?
2.4 Tuning SQL Queries
2.5 Tuning database schema
2.5 Mysql helps us forge ahead!
2.6 Faster...faster Web Client

* 3.0 Bottom line

Where were we?

Initially we had a system which could scale only upto 40 records /sec. I could even recollect the discussion, about "what should be the ideal rate of records? ". Finally we decided that 40/sec was the ideal rate for a single firewall. So when we have to go out, we atleast needed to support 3 firewalls. Hence we decided that 120/sec would be the ideal rate. Based on the data from our competitor(s) we came to the conclusion that, they could support around 240/sec. We thought it was ok! as it was our first release. Because all the competitors talked about the number of firewalls he supported but not on the rate.

Memory hits the sky

Our memory was always hitting the sky even at 512MB! (OutOfMemory exception) We blamed cewolf(s) inmemory caching of the generated images.But we could not escape for long! No matter whether we connected the client or not we used to hit the sky in a couple of days max 3-4 days flat! Interestingly,this was reproducible when we sent data at very high rates(then), of around 50/sec. You guessed it right, an unlimited buffer which grows until it hits the roof.

Low processing rate

We were processing records at the rate of 40/sec. We were using bulk update of dataobject(s). But it did not give the expected speed! Because of this we started to hoard data in memory resulting in hoarding memory!

Data Loss :-(

At very high speeds we used to miss many a packet(s). We seemed to have little data loss, but that resulted in a memory hog. On some tweaking to limit the buffer size we started having a steady data loss of about 20% at very high rates.

Mysql pulls us down

We were facing a tough time when we imported a log file of about 140MB. Mysql started to hog,the machine started crawling and sometimes it even stopped responding.Above all, we started getting deadlock(s) and transaction timeout(s). Which eventually reduced the responsiveness of the system.

Slow Web Client

Here again we blamed the number of graphs we showed in a page as the bottleneck, ignoring the fact that there were many other factors that were pulling the system down. The pages used to take 30 seconds to load for a page with 6-8 graphs and tables after 4 days at Internet Data Center.

Road To Nirvana

Controlling Memory!

We tried to put a limit on the buffer size of 10,000, but it did not last for long. The major flaw in the design was that we assumed that the buffer of around 10000 would suffice, i.e we would be process records before the buffer of 10,1000 reaches. Inline with the principle "Something can go wrong it will go wrong!" it went wrong. We started loosing data. Subsesquently we decided to go with a flat file based caching, wherein the data was dumped into the flat file and would be loaded into the database using "load data infile". This was many times faster than an bulk insert via database driver. you might also want to checkout some possible optimizations with load data infile. This fixed our problem of increasing buffer size of the raw records.

The second problem we faced was the increase of cewolf(s) in memory caching mechanism. By default it used "TransientSessionStorage" which caches the image objects in memory, there seemed to be some problem in cleaning up the objects, even after the rerferences were lost! So we wrote a small "FileStorage" implementation which store the image objects in the local file. And would be served as and when the request comes in. Moreover, we also implmentated a cleanup mechanism to cleanup stale images( images older than 10mins).

Another interesting aspect we found here was that the Garbage collector had lowest priority so the objects created for each records , were hardly cleaned up. Here is a little math to explain the magnitude of the problem. Whenever we receive a log record we created ~20 objects(hashmap,tokenized strings etc) so at the rate of 500/sec for 1 second, the number of objects was 10,000(20*500*1). Due to the heavy processing Garbage collector never had a chance to cleanup the objects. So all we had to do was a minor tweak, we just assigned "null" to the object references. Voila! the garbage collector was never tortured I guess ;-)

Streamlining processing rate

The processing rate was at a meagre 40/sec that means that we could hardly withstand even a small outburst of log records! The memory control gave us some solace,but the actual problem was with the application of the alert filters over the records. We had around 20 properties for each record, we used to search for all the properties. We changed the implementation to match for those properties we had criteria for! Moreover, we also had a memory leak in the alert filter processing. We maintained a queue which grew forever. So we had to maintain a flat file object dumping to avoid re-parsing of records to form objects! Moreover, we used to do the act of searching for a match for each of the property even when we had no alert criteria configured.

What data loss uh-uh?

Once we fixed the memory issues in receiving data i.e dumping into flat file, we never lost data! In addition to that we had to remove a couple of unwanted indexes in the raw table to avoid the overhead while dumping data. We hadd indexes for columns which could have a maximum of 3 possible values. Which actually made the insert slower and was not useful.

Tuning SQL Queries

Your queries are your keys to performance. Once you start nailing the issues, you will see that you might even have to de-normalize the tables. We did it! Here is some of the key learnings:

* Use "Analyze table" to identify how the mysql query works. This will give you insight about why the query is slow, i.e whether it is using the correct indexes, whether it is using a table level scan etc.

* Never delete rows when you deal with huge data in the order of 50,000 records in a single table. Always try to do a "drop table" as much as possible. If it is not possible, redesign your schema, that is your only way out!

* Avoid unwanted join(s), don't be afraid to de-normalize (i.e duplicate the column values) Avoid join(s) as much as possible, they tend to pull your query down. One hidden advantage is the fact that they impose simplicity in your queries.

* If you are dealing with bulk data, always use "load data infile" there are two options here, local and remote. Use local if the mysql and the application are in the same machine otherwise use remote.

* Try to split your complex queries into two or three simpler queries. The advantages in this approach are that the mysql resource is not hogged up for the entire process. Tend to use temporary tables. Instead of using a single query which spans across 5-6 tables.

* When you deal with huge amount of data, i.e you want to proces say 50,000 records or more in a single query try using limit to batch process the records. This will help you scale the system to new heights

* Always use smaller transaction(s) instead of large ones i.e spanning across "n" tables. This locks up the mysql resources, which might cause slowness of the system even for simple queries

* Use join(s) on columns with indexes or foreign keys

* Ensure that the the queries from the user interface have criteria or limit.

* Also ensure that the criteria column is indexed

* Do not have the numeric value in sql criteria within quotes, because mysql does a type cast

* use temporary tables as much as possible, and drop it...

* Insert of select/delete is a double table lock... be aware...

* Take care that you do not pain the mysql database with the frequency of your updates to the database. We had a typical case we used to dump to the database after every 300 records. So when we started testing for 500/sec we started seeing that the mysql was literally dragging us down. That is when we realized that the typicall at the rate of 500/sec there is an "load data infile" request every second to the mysql database. So we had to change to dump the records after 3 minutes rather than 300 records.

Tuning database schema

When you deal with huge amount of data, always ensure that you partition your data. That is your road to scalability. A single table with say 10 lakhs can never scale. When you intend to execute queries for reports. Always have two levels of tables, raw tables one for the actual data and another set for the report tables( the tables which the user interfaces query on!) Always ensure that the data on your report tables never grows beyond a limit. Incase you are planning to use Oracle, you can try out the partitioning based on criteria. But unfortunately mysql does not support that. So we will have to do that. Maintain a meta table in which you have the header information i.e which table to look for, for a set of given criteria normally time.

* We had to walk through our database schema and we added to add some indexes, delete some and even duplicated column(s) to remove costly join(s).

* Going forward we realized that having the raw tables as InnoDB was actually a overhead to the system, so we changed it to MyISAM

* We also went to the extent of reducing the number of rows in static tables involved in joins

* NULL in database tables seems to cause some performance hit, so avoid them

* Don't have indexes for columns which has allowed values of 2-3

* Cross check the need for each index in your table, they are costly. If the tables are of InnoDB then double check their need. Because InnoDB tables seem to take around 10-15 times the size of the MyISAM tables.

* Use MyISAM whenever there is a majority of , either one of (select or insert) queries. If the insert and select are going to be more then it is better to have it as an InnoDB

Mysql helps us forge ahead!

Tune your mysql server ONLY after you fine tune your queries/schemas and your code. Only then you can see a perceivable improvement in performance. Here are some of the parameters that comes in handy:

* Use the buffer pool size which will enable your queries to execute faster --innodb_buffer_pool_size=64M for InnoDB and use --key-bufer-size=32M for MyISAM

* Even simple queries started taking more time than expected. We were actually puzzled! We realized that mysql seems to load the index of any table it starts inserting on. So what typically happened was, any simple query to a table with 5-10 rows took around 1-2 secs. On further analysis we found that just before the simple query , "load data infile" happened. This disappeared when we changed the raw tables to MyISAM type, because the buffer size for innodb and MyISAM are two different configurations.

for more configurable parameters see here.

Tip: start your mysql to start with the following option --log-error this will enable error logging

Faster...faster Web Client

The user interface is the key to any product, especially the perceived speed of the page is more important! Here is a list of solutions and learnings that might come in handy:

* If your data is not going to change for say 3-5 minutes, it is better to cache your client side pages

* Tend to use Iframe(s)for inner graphs etc. they give a perceived fastness to your pages. Better still use the javascript based content loading mechanism. This is something you might want to do when you have say 3+ graphs in the same page.

* Internet explorer displays the whole page only when all the contents are received from the server. So it is advisable to use iframes or javascript for content loading.

* Never use multiple/duplicate entries of the CSS file in the html page. Internet explorer tends to load each CSS file as a separate entry and applies on the complete page!

Bottomline Your queries and schema make the system slower! Fix them first and then blame the database!

See Also

* High Performance Mysql

* Query Performance

* Explain Query

* Optimizing Queries

* InnoDB Tuning

* Tuning Mysql

Categories: Firewall Analyzer | Performance Tips This page was last modified 18:00, 31 August 2005.

-Ramesh-

In The News:

This RSS feed URL is deprecated, please update. New URLs can be found in the footers at https://news.google.com/news

Forbes

How A Mysterious Tech Billionaire Created Two Fortunes—And A Global Software Sweatshop
Forbes
rom an office suite on the 26th floor of the iconic Frost Bank Tower in Austin, Texas, a little-known recruiting firm called Crossover is searching the globe for software engineers. Crossover is looking for anyone who can commit to a 40- or 50-hour ...


Harvard Business Review

How Software Is Helping Big Companies Dominate
Harvard Business Review
Even outside of the tech sector, the employment of more software developers is associated with a greater increase in industry concentration, and this relationship appears to be causal. Similarly, researchers at the OECD have found that markups — a ...


Software AG Launches webMethods B2B Cloud, a New Partner Integration-as-a-Service
Associated Press (press release) (blog)
RESTON, Va.--(BUSINESS WIRE)--Nov 19, 2018--Software AG (Frankfurt TecDAX: SOW) today announced the immediate availability of webMethods B2B Cloud, a new Partner Integration-as-a-Service (PIaaS). webMethods B2B Cloud provides enterprises ...


RT

NATO mapping software now available to Chinese military, report finds
RT
A Belgium-based defense contractor Luciad has sold the software to China, according to the South China Morning Post (SCMP) citing Chinese government sources. The software, which includes a Luciad Lightspeed application, is used to map the terrain and ...

and more »

ZDNet

Fitbit news: Charge 3 software update, Black Friday deals save you $30 to $70 on a tracker
ZDNet
Fitbit recently released a software update that adds quick replies for Android users, the ability to accept and reject calls from the Charge 3, and a new celebration animation when you meet your daily step goal. In addition, new weight, hydration, and ...

and more »

Economic Times

Theresa May vows to give Indian software developers fair post-Brexit chance
Economic Times
It will no longer be the case that EU nationals, regardless of the skills or experience they have to offer, can jump the queue ahead of engineers from Sydney or software developers from Delhi," she said in her speech. "Instead of a system based on ...
UK will be fair to Indian software developers: MayThe Hindu BusinessLine

all 2,142 news articles »

Unifi Software Expands Leadership Team with Appointment of Bill Serino as Chief Revenue Officer
Associated Press (press release)
SAN MATEO, Calif., Nov. 19, 2018 (GLOBE NEWSWIRE) -- Unifi Software, a leader in providing a seamlessly integrated suite of self-service data tools, today announced that it has expanded its leadership team with the addition of Bill Serino as Chief ...


Advanced computer technology and software turn species identification interactive
Science Daily
Being the product of digitally available software, interactive identification keys are not only easy, quick and inexpensive to publish, but they are also simple to edit and build on in a collaborative manner. Experts from all around the world could ...


India Video Surveillance Analytics Software Market (2018-2024): Forecast By Macro Applications, Micro Applications ...
Associated Press (press release) (blog)
The “India Video Surveillance Analytics Software Market (2018-2024): Market Forecast By Macro Applications, By Micro Applications, By Deployment, By Verticals , By Regions and Competitive Landscape” report has been added to ...


Defiance Crescent News

Who lives with you? Facebook seeks to patent software to figure out profiles of households
Defiance Crescent News
Facebook Inc. is applying to patent software that it could use to create profiles of users' households by making educated guesses about how many people live in the household, what their relationships to each other are, what interests they share and ...
Facebook Patent Shows Software That Can Analyse Who Lives With YouNDTV
Facebook seeks to patent software to analyse your household for targeted adsHindustan Times

all 57 news articles »
Google News

Free Program Removes Spyware not Detected by Premium Security Scan

What is Spyware?Spyware monitors your surfing habits and sends the... Read More

How to Get The Best Accounting Software For Your Small Business

Buying accounting software is a major investment. It's an important... Read More

Importance of Coding Standards

Programming Help for BeginnersWe write programs to instruct computers. When... Read More

Microsoft Moves to Small Business Accounting/Retail Market ? Stakes and Thoughts

In this small article we will be looking at the... Read More

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software... Read More

Fleet Maintenance Software Reviews

Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More

Falling in Love With More Than One Screensaver: The Fun Part

Having from five to ten and more favorite screensavers is... Read More

EDI: Electronic Document Interchange for Microsoft Great Plains ? Overview for Software Developer/Pr

Microsoft Great Plains - Microsoft Business Solutions accounting and ERP... Read More

C++ Tutorial 2, Input and Variables

This is the tutorial where we really get into programming.... Read More

Microsoft CRM Integration with Lotus Notes Domino: Messaging Connector ? Future Directions

IBM Lotus Notes Domino and Microsoft CRM (Client Relation Management)... Read More

How to Upgrade Dexterity Customization ? Tips for IT Manager

If you have Microsoft Great Plains and support it... Read More

Spyware, Adware, etc. -- Terms and Common Sense

When reading an article where some term is used often,... Read More

Introduction To ISDN, Part II

In the previous ISDN article, we looked at how and... Read More

Microsoft Great Plains BR: Bank Reconciliation

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Software Piracy - Global Increase

Pirated software is on the increase and now accounts for... Read More

Selecting Microsoft Great Plains Partner/VAR/Reseller: ERP Implementation & Customization ? Overview

In the case when you represent mid-size or mid-size-to-large business,... Read More

Microsoft Great Plains Implementation for Large Corporation ? Overview for VP IT

Microsoft Business Solutions Great Plains was historically designed to serve... Read More

What To Do When Windows Wont Boot

When Windows fails to boot it is normally caused by... Read More

Document Templates Give You The Perfect Framework For Your Documents

When it comes to running an office, the SOHO entrepreneur... Read More

Dig Out That Worm

Internet worms. Is your PC infected?If your computer has become... Read More

Reduce Pop-ups and Annoying Ads

There is many things more frustrating than surfing a website... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

ERP System of the Future: Database, Business Logic and Interface

We will base our prognosis on our Microsoft Business Solutions... Read More

The Dirt on Screensavers

Remember back in the days where screensavers were the coolest... Read More