| IntroductionSurprised, by the title? well, | | | | performance. Once you start nailing the |
| this is a tour of how we cracked the | | | | issues, you will see that you might even have |
| scalability jinx from handling a meagre 40 | | | | to de-normalize the tables. We did it! Here |
| records per second to 500 records per | | | | is some of the key learnings:* Use "Analyze |
| second. Beware, most of the problems we faced | | | | table" to identify how the mysql query works. |
| were straight forward, so experienced people | | | | This will give you insight about why the |
| might find this superfluous. | | | | query is slow, i.e whether it is using the |
| | | | correct indexes, whether it is using a table |
| Contents* 1.0 Where were we?1.1 Memory hits | | | | level scan etc.* Never delete rows when you |
| the sky | | | | deal with huge data in the order of 50,000 |
| | | | records in a single table. Always try to do a |
| 1.2 Low processing rate | | | | "drop table" as much as possible. If it is |
| | | | not possible, redesign your schema, that is |
| 1.3 Data loss :-( | | | | your only way out!* Avoid unwanted join(s), |
| | | | don't be afraid to de-normalize (i.e |
| 1.4 Mysql pulls us down | | | | duplicate the column values) Avoid join(s) as |
| | | | much as possible, they tend to pull your |
| 1.5 Slow Web Client* 2.0 Road to Nirvana2.1 | | | | query down. One hidden advantage is the fact |
| Controlling memory! | | | | that they impose simplicity in your queries.* |
| | | | If you are dealing with bulk data, always use |
| 2.2 Streamlining processing rate | | | | "load data infile" there are two options |
| | | | here, local and remote. Use local if the |
| 2.3 What data loss uh-uh? | | | | mysql and the application are in the same |
| | | | machine otherwise use remote.* Try to split |
| 2.4 Tuning SQL Queries | | | | your complex queries into two or three |
| | | | simpler queries. The advantages in this |
| 2.5 Tuning database schema | | | | approach are that the mysql resource is not |
| | | | hogged up for the entire process. Tend to use |
| 2.5 Mysql helps us forge ahead! | | | | temporary tables. Instead of using a single |
| | | | query which spans across 5-6 tables.* When |
| 2.6 Faster...faster Web Client* 3.0 Bottom | | | | you deal with huge amount of data, i.e you |
| lineWhere were we?Initially we had a system | | | | want to proces say 50,000 records or more in |
| which could scale only upto 40 records /sec. | | | | a single query try using limit to batch |
| I could even recollect the discussion, | | | | process the records. This will help you scale |
| about "what should be the ideal rate of | | | | the system to new heights* Always use smaller |
| records? ". Finally we decided that 40/sec | | | | transaction(s) instead of large ones i.e |
| was the ideal rate for a single firewall. So | | | | spanning across "n" tables. This locks up the |
| when we have to go out, we atleast needed to | | | | mysql resources, which might cause slowness |
| support 3 firewalls. Hence we decided that | | | | of the system even for simple queries* Use |
| 120/sec would be the ideal rate. Based on | | | | join(s) on columns with indexes or foreign |
| the data from our competitor(s) we came to | | | | keys* Ensure that the the queries from the |
| the conclusion that, they could support | | | | user interface have criteria or limit.* Also |
| around 240/sec. We thought it was ok! as it | | | | ensure that the criteria column is indexed* |
| was our first release. Because all the | | | | Do not have the numeric value in sql criteria |
| competitors talked about the number of | | | | within quotes, because mysql does a type |
| firewalls he supported but not on the | | | | cast* use temporary tables as much as |
| rate.Memory hits the skyOur memory was always | | | | possible, and drop it...* Insert of select |
| hitting the sky even at 512MB! (OutOfMemory | | | | delete is a double table lock... be aware...* |
| exception) We blamed cewolf(s) inmemory | | | | Take care that you do not pain the mysql |
| caching of the generated images.But we could | | | | database with the frequency of your updates |
| not escape for long! No matter whether we | | | | to the database. We had a typical case we |
| connected the client or not we used to hit | | | | used to dump to the database after every 300 |
| the sky in a couple of days max 3-4 days | | | | records. So when we started testing for 500 |
| flat! Interestingly,this was reproducible | | | | sec we started seeing that the mysql was |
| when we sent data at very high rates(then), | | | | literally dragging us down. That is when we |
| of around 50/sec. You guessed it right, an | | | | realized that the typicall at the rate of 500 |
| unlimited buffer which grows until it hits | | | | sec there is an "load data infile" request |
| the roof.Low processing rateWe were | | | | every second to the mysql database. So we had |
| processing records at the rate of 40/sec. We | | | | to change to dump the records after 3 minutes |
| were using bulk update of dataobject(s). But | | | | rather than 300 records.Tuning database |
| it did not give the expected speed! Because | | | | schemaWhen you deal with huge amount of data, |
| of this we started to hoard data in memory | | | | always ensure that you partition your data. |
| resulting in hoarding memory!Data Loss :-(At | | | | That is your road to scalability. A single |
| very high speeds we used to miss many a | | | | table with say 10 lakhs can never scale. When |
| packet(s). We seemed to have little data | | | | you intend to execute queries for reports. |
| loss, but that resulted in a memory hog. On | | | | Always have two levels of tables, raw tables |
| some tweaking to limit the buffer size we | | | | one for the actual data and another set for |
| started having a steady data loss of about | | | | the report tables( the tables which the user |
| 20% at very high rates.Mysql pulls us downWe | | | | interfaces query on!) Always ensure that the |
| were facing a tough time when we imported a | | | | data on your report tables never grows beyond |
| log file of about 140MB. Mysql started to | | | | a limit. Incase you are planning to use |
| hog,the machine started crawling and | | | | Oracle, you can try out the partitioning |
| sometimes it even stopped responding.Above | | | | based on criteria. But unfortunately mysql |
| all, we started getting deadlock(s) and | | | | does not support that. So we will have to do |
| transaction timeout(s). Which eventually | | | | that. Maintain a meta table in which you |
| reduced the responsiveness of the system.Slow | | | | have the header information i.e which table |
| Web ClientHere again we blamed the number of | | | | to look for, for a set of given criteria |
| graphs we showed in a page as the bottleneck, | | | | normally time.* We had to walk through our |
| ignoring the fact that there were many other | | | | database schema and we added to add some |
| factors that were pulling the system down. | | | | indexes, delete some and even duplicated |
| The pages used to take 30 seconds to load for | | | | column(s) to remove costly join(s).* Going |
| a page with 6-8 graphs and tables after 4 | | | | forward we realized that having the raw |
| days at Internet Data Center.Road To | | | | tables as InnoDB was actually a overhead to |
| NirvanaControlling Memory!We tried to put a | | | | the system, so we changed it to MyISAM* We |
| limit on the buffer size of 10,000, but it | | | | also went to the extent of reducing the |
| did not last for long. The major flaw in the | | | | number of rows in static tables involved in |
| design was that we assumed that the buffer of | | | | joins* NULL in database tables seems to cause |
| around 10000 would suffice, i.e we would be | | | | some performance hit, so avoid them* Don't |
| process records before the buffer of 10,1000 | | | | have indexes for columns which has allowed |
| reaches. Inline with the principle "Something | | | | values of 2-3* Cross check the need for each |
| can go wrong it will go wrong!" it went | | | | index in your table, they are costly. If the |
| wrong. We started loosing data. Subsesquently | | | | tables are of InnoDB then double check their |
| we decided to go with a flat file based | | | | need. Because InnoDB tables seem to take |
| caching, wherein the data was dumped into the | | | | around 10-15 times the size of the MyISAM |
| flat file and would be loaded into the | | | | tables.* Use MyISAM whenever there is a |
| database using "load data infile". This was | | | | majority of , either one of (select or |
| many times faster than an bulk insert via | | | | insert) queries. If the insert and select |
| database driver. you might also want to | | | | are going to be more then it is better to |
| checkout some possible optimizations with | | | | have it as an InnoDBMysql helps us forge |
| load data infile. This fixed our problem of | | | | ahead!Tune your mysql server ONLY after you |
| increasing buffer size of the raw records.The | | | | fine tune your queries/schemas and your code. |
| second problem we faced was the increase of | | | | Only then you can see a perceivable |
| cewolf(s) in memory caching mechanism. By | | | | improvement in performance. Here are some of |
| default it used "TransientSessionStorage" | | | | the parameters that comes in handy:* Use the |
| which caches the image objects in memory, | | | | buffer pool size which will enable your |
| there seemed to be some problem in cleaning | | | | queries to execute faster |
| up the objects, even after the rerferences | | | | --innodb_buffer_pool_size=64M for InnoDB |
| were lost! So we wrote a small "FileStorage" | | | | and use --key-bufer-size=32M for MyISAM* Even |
| implementation which store the image objects | | | | simple queries started taking more time than |
| in the local file. And would be served as and | | | | expected. We were actually puzzled! We |
| when the request comes in. Moreover, we also | | | | realized that mysql seems to load the index |
| implmentated a cleanup mechanism to cleanup | | | | of any table it starts inserting on. So what |
| stale images( images older than | | | | typically happened was, any simple query to a |
| 10mins).Another interesting aspect we found | | | | table with 5-10 rows took around 1-2 secs. On |
| here was that the Garbage collector had | | | | further analysis we found that just before |
| lowest priority so the objects created for | | | | the simple query , "load data infile" |
| each records , were hardly cleaned up. Here | | | | happened. This disappeared when we changed |
| is a little math to explain the magnitude of | | | | the raw tables to MyISAM type, because the |
| the problem. Whenever we receive a log record | | | | buffer size for innodb and MyISAM are two |
| we created ~20 objects(hashmap,tokenized | | | | different configurations.for more |
| strings etc) so at the rate of 500/sec for 1 | | | | configurable parameters see here.Tip: start |
| second, the number of objects was | | | | your mysql to start with the following option |
| 10,000(20*500*1). Due to the heavy processing | | | | --log-error this will enable error |
| Garbage collector never had a chance to | | | | loggingFaster...faster Web ClientThe user |
| cleanup the objects. So all we had to do was | | | | interface is the key to any product, |
| a minor tweak, we just assigned "null" to the | | | | especially the perceived speed of the page is |
| object references. Voila! the garbage | | | | more important! Here is a list of solutions |
| collector was never tortured I guess | | | | and learnings that might come in handy:* If |
| ;-)Streamlining processing rateThe processing | | | | your data is not going to change for say 3-5 |
| rate was at a meagre 40/sec that means that | | | | minutes, it is better to cache your client |
| we could hardly withstand even a small | | | | side pages* Tend to use Iframe(s)for inner |
| outburst of log records! The memory control | | | | graphs etc. they give a perceived fastness to |
| gave us some solace,but the actual problem | | | | your pages. Better still use the javascript |
| was with the application of the alert filters | | | | based content loading mechanism. This is |
| over the records. We had around 20 properties | | | | something you might want to do when you have |
| for each record, we used to search for all | | | | say 3+ graphs in the same page.* Internet |
| the properties. We changed the implementation | | | | explorer displays the whole page only when |
| to match for those properties we had criteria | | | | all the contents are received from the |
| for! Moreover, we also had a memory leak in | | | | server. So it is advisable to use iframes or |
| the alert filter processing. We maintained a | | | | javascript for content loading.* Never use |
| queue which grew forever. So we had to | | | | multiple/duplicate entries of the CSS file in |
| maintain a flat file object dumping to avoid | | | | the html page. Internet explorer tends to |
| re-parsing of records to form objects! | | | | load each CSS file as a separate entry and |
| Moreover, we used to do the act of searching | | | | applies on the complete page!Bottomline |
| for a match for each of the property even | | | | |
| when we had no alert criteria configured.What | | | | Your queries and schema make the system |
| data loss uh-uh?Once we fixed the memory | | | | slower! Fix them first and then blame the |
| issues in receiving data i.e dumping into | | | | database!See Also* High Performance Mysql* |
| flat file, we never lost data! In addition to | | | | Query Performance* Explain Query* Optimizing |
| that we had to remove a couple of unwanted | | | | Queries* InnoDB Tuning* Tuning |
| indexes in the raw table to avoid the | | | | MysqlCategories: Firewall Analyzer | |
| overhead while dumping data. We hadd indexes | | | | Performance Tips |
| for columns which could have a maximum of 3 | | | | |
| possible values. Which actually made the | | | | This page was last modified 18:00, 31 August |
| insert slower and was not useful.Tuning SQL | | | | 2005. |
| QueriesYour queries are your keys to | | | | |