One MySQL Performance Tip You Must See

I run about twenty websites, previously spread over shared hosting accounts. Last month my hosts, each independently shut me down for over utilizing CPU. Fair enough, I did not assume that I turned into abusing the accounts, however, I do not make the policies.

I at once started seeking out alternatives and determined on Go Daddy dedicated hosting. The price was proper and for just over $100 a month I got a nice server with 2 GB of reminiscence, 500GB of month-to-month bandwidth, a fast CPU all to my self, to abuse as I see the match.

Fast ahead to UFC seventy-seven. You see I run a web website online which does UFC predictions and UFC outcomes. On fight night time, the site can get pretty busy, my previous excessive turned into 18,000 traffic in the 3 or so hours that the fights take vicinity. My shared website hosting account never had a hassle with this load, as a minimum not as far as I may want to tell. However, with UFC seventy-seven on my new devoted web hosting account my server hit a brick wall. The CPU changed into at 100% and there were 2 hundred apache processes all vying for the available CPU and memory. Immediately I started searching out reasons and extra importantly solutions. Three days later I think I actually have most of my answers.

It’s critical to recognize that Go Daddy become now not at fault, I asked for a container with sure specifications and that’s precisely what they gave me, entire with all of the software they stated. MySQL, PHP 4.3.9 and Apache 2.0.Fifty-two. So I commenced searching (whilst the server calmed down) at what become going on when a consumer requested a page. The first aspect that I observed was that the modern-day apache system might take about 5% CPU, I did now not be aware of how long it ran. The subsequent aspect I mentioned become that MySQL would additionally take enormous CPU if simplest for a quick time frame. It’s important to recognize that in UFC 77 MySQL was taking approx 50% CPU for the complete busy duration. Time to search for a few optimizations.

For my web site, wherein there are 20,000 visitors in three hours and maybe 10 updates throughout that time, caching might glaringly be beneficial. I noticed looking the web that apache has a few caching and even better a few min caching, however, it is not considered production equipped within the 2.0.Fifty-two builds that I even have, so I discarded that concept as a substitute quick. I did, however, word a few matters when I looked at MySQL optimizations.

The first query is do you really need to emigrate to MySQL? Using MySQL server as a facts storage rather than Microsoft Access gives numerous blessings: excessive overall performance, cross-Platforming, more than one customers get right of entry to, strong security machine and plenty more. So, if you are building a complex multi-person system, you really want to migrate information from Access to MySQL.

But if MS Access facts are used by a single person and the records document is honestly copied to a brand new gadget when it desires to be moved, there isn’t always much benefit to using MySQL in this type of state of affairs.

There is the usual way emigrate Microsoft Access statistics to MySQL server. MS Access can export its tables to a diffusion of codecs, including ODBC. Using this option you may be able to export an Access table to MySQL thru Connector/ODBC ODBC driving force supplied through MySQL AB. To migrate MS Access desk to MySQL server right-click on at the desk and choose “Export” alternative. After numerous steps, your data can be exported to MySQL.

Is this method sufficient for you? If your MS Access database is small and does now not have a complicated shape, the answer is “yes”. Otherwise, you need to be aware that Access will now not export index data with the data. You will need to build indexes to your tables manually after exporting them. Also, there is some distinction in data kinds between Microsoft Access and MySQL. One example would be the CURRENCY information kind: MySQL does no longer have a CURRENCY facts kind, as an alternative, it creates a column with the definition DECIMAL(19, four) for the equal motive. While MS Access supports Unicode by means of default, MySQL does now not so tightly bind individual sets to discipline types. Instead, it allows each textual content area to be bound to any wide variety of man or woman units, such as Unicode. Again, you’ll specify it manually in case you need to guide Unicode in the ensuing MySQL database.

Another method to the migration hassle is to apply third-celebration programs that would handle it for you. The international’s leader in facts processing discipline, Intelligent Converters evolved Access-to-MySQL converter with the intention to offer customers with the correct, fast and smooth answer to that conversion undertaking.

Although it’s miles very clean to apply, Access-to-MySQL provides enough abilities to migrate very huge and complicated MS Access databases. At the same time the migrated consequences are accurate because this system does all necessary records transformation, supports all MS Access types and attributes, converts all indexes (including relationships among tables), handles multibyte person units and much greater. If you need more manage the migration or simply do not have direct get entry to the vacation spot MySQL server Access-to-MySQL can export information into MySQL unload record.

Author: Rohit Shetty

Share This Post On