kiccu的部落格

Introduction

Surprised, by the title? well, this is a journeying of how we cracked the quantifiability unfortunate from handling a insufficient 40 documents per 2nd to 500 chronicles per 2nd. Beware, supreme of the teething troubles we featured were undeviating forward, so veteran citizens can brainstorm this worthless.

Contents

* 1.0 Where were we?

1.1 Memory hits the sky

1.2 Low process 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 assemblage loss uh-uh?

2.4 Tuning SQL Queries

2.5 Tuning information schema

2.5 Mysql helps us furnace ahead!

2.6 Faster...faster Web Client

* 3.0 Bottom line

Where were we?

Initially we had a set of contacts which could scramble just upto 40 annals /sec. I could even retrieve the discussion, nearly "what should be the just the thing charge of records? ". Finally we contracted that 40/sec was the great charge per unit for a sui generis thrust. So once we have to go out, we atleast required to flying buttress 3 firewalls. Hence we established that 120/sec would be the just what the doctor ordered charge. Based on the facts from our contender(s) we came to the finishing point that, they could espouse about 240/sec. We thought it was ok! as it was our premier giving off. Because all the competitors talked more or less the figure of firewalls he verified but not on the charge.

Memory hits the sky

Our internal representation was always hitting the sky even at 512MB! (OutOfMemory immunity) We deuced cewolf(s) inmemory caching of the generated descriptions.But we could not hurried departure for long! No concern whether we allied the client or not we previously owned to hit the sky in a duo of years max 3-4 days flat! Interestingly,this was reproducible once we sent data at extremely higher taxation(then), of about 50/sec. You guessed it right, an never-ending chemical compound which grows until it hits the protective cover.

Low process rate

We were processing records at the charge per unit of 40/sec. We were using majority tidings of dataobject(s). But it did not contribute the matter-of-course speed! Because of this we started to reserve notes in representation resulting in hoarding memory!

Data Loss :-(

At extraordinarily illustrious speeds we utilized to omit numerous a collection(s). We seemed to have smaller accumulation loss, but that resulted in a mental representation hog. On quite a lot of tweaking to contain the chemical compound sized we started having a unwavering data loss of around 20% at very elevated rates.

Mysql pulls us down

We were facing a intrepid case once we foreign a log record of about 140MB. Mysql started to hog,the mechanism started travel and sometimes it even stopped responding.Above all, we started exploit situation(s) and dealings timeout(s). Which in time shrunken the reactivity of the set of laws.

Slow Web Client

Here again we cursed the number of graphs we showed in a leaf as the bottleneck, ignoring the fact that within were some else factors that were actuation the group hair. The pages in use to pocket 30 seconds to payload for a leaf with 6-8 graphs and tables after 4 years at Internet Data Center.

Road To Nirvana

Controlling Memory!

We proved to put a target on the shock absorber size of 10,000, but it did not concluding for long-lasting. The star blotch in the image was that we taken for granted that the shock absorber of around 10000 would suffice, i.e we would be activity collection previously the buffer of 10,1000 reaches. Inline near the principle "Something can go not right it will go wrong!" it went false. We started losing information. Subsesquently we approved to go with a even wallet supported caching, wherein the accumulation was dumped into the smooth as glass directory and would be full into the info mistreatment "load information infile". This was many modern world quicker than an majority bring in via database operator. you might likewise want to checkout some assertable optimizations with load assemblage infile. This known our puzzle of accretive chemical compound largeness of the raw archives.

The 2d fault we featured was the add of cewolf(s) in recall caching contraption. By non-attendance it used "TransientSessionStorage" which caches the photo objects in memory, within seemed to be any quirk in cleanup up the objects, even after the rerferences were lost! So we wrote a trivial "FileStorage" execution which cache the figurine objects in the area record. And would be served as and once the order comes in. Moreover, we too implmentated a profits machinery to lucre bad metaphors( descriptions older than 10mins).

Another riveting aspect we recovered here was that the Garbage individual had last superiority so the objects created for respectively collection , were scarce cleansed up. Here is a diminutive math to cover the enormity of the problem. Whenever we get a log journal we created ~20 objects(hashmap,tokenized string section etc) so at the charge per unit of 500/sec for 1 second, the number of objects was 10,000(20*500*1). Due to the stocky process Garbage human never had a hit and miss to lucre the objects. So all we had to do was a secondary tweak, we lately assigned "null" to the intent references. Voila! the waste product human was never tortured I speculate ;-)

Streamlining process rate

The process charge was at a scanty 40/sec that routine that we could just resist even a dinky expression of log records! The reminiscence rule gave us a few solace,but the actual hold-up was beside the request of the sleepless filters terminated the annals. We had about 20 properties for all record, we utilized to poke about for all the properties. We denaturised the completing to igniter for those properties we had criteria for! Moreover, we likewise had a representation escape in the perked device process. We kept up a line which grew ad infinitum. So we had to argue a straight data file idea selling to abstain from re-parsing of store to develop objects! Moreover, we previously owned to do the act of questioning for a lucifer for respectively of the chattels even once we had no argus-eyed criteria designed.

What information loss uh-uh?

Once we rigid the memory issues in delivery data i.e merchandising into face down file, we ne'er squandered data! In increment to that we had to erase a two of a kind of disadvantageous indexes in the raw array to eschew the overhead while marketing accumulation. We hadd indexes for columns which could have a maximum of 3 realizable belief. Which in actuality ready-made the bring in slower and was not purposeful.

Tuning SQL Queries

Your queries are your keys to dramatization. Once you arrival nailing the issues, you will see that you may well even have to de-normalize the tables. We did it! Here is a few of the key learnings:

* Use "Analyze table" to determine how the mysql inquiring plant. This will give you acuity in the order of why the query is slow, i.e whether it is victimisation the true indexes, whether it is mistreatment a array plane scan etc.

* Never withdraw rows once you settlement with measureless aggregation in the writ of 50,000 chronicles in a lone table. Always try to do a "drop table" as overmuch as come-at-able. If it is not possible, rewrite your schema, that is your lone way out!

* Avoid uninvited link(s), don't be nervous to de-normalize (i.e repeat the column values) Avoid enrol(s) as more as possible, they be given to twist your questioning trailing. One unknown lead is the fact that they oblige roughness in your queries.

* If you are dealing with bulk data, ever use "load accumulation infile" in attendance are two options here, district and removed. Use local if the mysql and the application are in the said apparatus otherwise use distant.

* Try to tear your complex queries into two or three simpler queries. The advantages in this attack are that the mysql assets is not bulging up for the full act. Tend to use temporal tables. Instead of exploitation a one-member inquiry which spans intersectant 5-6 tables.

* When you agreement near gargantuan magnitude of data, i.e you poverty to modus operandi say 50,000 records or much in a unattached query try using shorten to load manoeuvre the annals. This will give support to you mount the association to new heights

* Always use smaller retailing(s) alternatively of spacious ones i.e spanning crosstown "n" tables. This hair up the mysql resources, which might produce unskillfulness of the complex even for uncontrived queries

* Use secure(s) on columns near indexes or foreign keys

* Ensure that the the queries from the somebody surface have criteria or inhibit.

* Also ensure that the criteria file is indexed

* Do not have the definite quantity efficacy in sql criteria inside quotes, because mysql does a group cast

* use evanescent tables as some as possible, and globule it...

* Insert of choice/delete is a twofold array holdfast... be sensitive...

* Take exactness that you do not symptom the mysql database beside the frequency of your updates to the info. We had a true case we used to throw out to the information after all 300 accounts. So once we started experimentation for 500/sec we started seeing that the mysql was exactly effortful us downhill. That is once we complete that the typicall at the charge per unit of 500/sec location is an "load data infile" will all ordinal to the mysql database. So we had to transfer to throw out the library after 3 proceedings a bit than 300 chronicles.

Tuning information schema

When you promise near grand amount of data, always guarantee that you partition your accumulation. That is your road to quantifiability. A one-person table near say 10 lakhs can never ordered series. When you will to punish queries for reports. Always have two levels of tables, raw tables one for the actual data and other set for the story tables( the tables which the user interfaces enquiry on!) Always insure that the data on your gossip tables never grows further than a impede. Incase you are planning to use Oracle, you can try out the breakdown based on criteria. But undesirably mysql does not bracket that. So we will have to do that. Maintain a meta array in which you have the header message i.e which array to manifestation for, for a set of fixed criteria as a rule juncture.

* We had to waddle done our info mental representation and we additional to add quite a few indexes, take away some and even duplicated file(s) to extract expensive articulation(s).

* Going fore we complete that having the raw tables as InnoDB was in actuality a overhead to the system, so we transformed it to MyISAM

* We too went to the magnitude of chemical reaction the number of rows in adynamic tables active in joins

* NULL in info tables seems to origin whatsoever presentation hit, so baulk at them

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

* Cross cheque the inevitability for respectively index in your table, they are dearly-won. If the tables are of InnoDB next lookalike draft their demand. Because InnoDB tables be to payoff in a circle 10-15 modern times the proportions of the MyISAM tables.

* Use MyISAM whenever in that is a majority of , any one of (select or instil) queries. If the embed and superior are going to be more then it is recovered to have it as an InnoDB

Mysql helps us forge ahead!

Tune your mysql server ONLY after you magnificent line your queries/schemas and your code. Only past you can see a perceivable enrichment in carrying out. Here are some of the parameters that comes in handy:

* Use the buffer mere bulkiness which will enable your queries to kill quicker -innodb_buffer_pool_size=64M for InnoDB and use -key-bufer-size=32M for MyISAM

* Even guileless queries started fetching much juncture than expected. We were in fact puzzled! We completed that mysql seems to oceans the graduated table of any array it starts inserting on. So what routinely happened was, any unrefined interrogation to a tabular array with 5-10 rows took in circles 1-2 secs. On additional investigating we saved that basically formerly the basic inquiry , "load information infile" happened. This disappeared once we exchanged the raw tables to MyISAM type, because the cushion mass for innodb and MyISAM are two disparate configurations.

for more than configurable parameters see present.

Tip: activation your mysql to inception next to the shadowing remedy -log-error this will enable imperfection logging

Faster...faster Web Client

The user interface is the key to any product, mega the sensed rate of knots of the leaf is more important! Here is a chronicle of solutions and learnings that strength locomote in handy:

* If your assemblage is not going to redeploy for say 3-5 minutes, it is superior to cache your shopper side pages

* Tend to use Iframe(s)for interior graphs etc. they present a sensed fastness to your pages. Better immobile use the javascript supported complacent loading device. This is something you could poorness to do once you have say 3 graphs in the selfsame page.

* Internet buccaneer displays the entire page solitary once all the listing are received from the restaurant attendant. So it is recommended to use iframes or javascript for delighted loading.

* Never use fivefold/duplicate entries of the CSS database in the html leaf. Internet holidaymaker tends to mountain each CSS folder as a remove entry and applies on the made page!

Bottomline
Your queries and schema brand the group slower! Fix them oldest and afterwards deuced the database!

See Also

* High Performance Mysql

* Query Performance

* Explain Query

* Optimizing Queries

* InnoDB Tuning

* Tuning Mysql

Categories: Firewall Analyzer | Performance Tips
This leaf was ending made to order 18:00, 31 August 2005.