Home Mysql One MySQL Performance Tip You Must See

One MySQL Performance Tip You Must See

618
0

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

One MySQL Performance Tip You Must See 1

I at once started seeking out alternatives and determined on Go Daddy’s 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 myself, 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, and 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 to 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 has 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 not know how long it ran.

The subsequent aspect I mentioned become that MySQL would additionally take an 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 website, wherein there are 20,000 visitors in three hours and maybe 10 updates throughout that time, caching might glaringly be beneficial. I noticed looking at the web that apache has a little 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 fact storage rather than Microsoft Access gives numerous blessings: excessive overall performance, cross-Platforming, more than one customer gets 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.

One MySQL Performance Tip You Must See 2

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 affairs.

There is the usual way to emigrate Microsoft Access statistics to MySQL server. MS Access can export its tables to the diffusion of codecs, including ODBC. Using this option, you may 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 DECIMAL(19, four) for the equal motive. While MS Access supports Unicode by default, MySQL does not so tightly bind individual sets to discipline types. Instead, it allows each textual content area to be bound to various man or woman units, such as Unicode. Again, you’ll specify it manually if 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. In the facts processing discipline, the international’s leader, Intelligent Converters, evolved Access-to-MySQL converter to offer customers the correct, fast, and smooth answer to that conversion undertaking.

One MySQL Performance Tip You Must See 3

Although it’s miles spotless to apply, Access-to-MySQL provides enough abilities to migrate 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 to manage the migration or do not directly get entry to the vacation spot MySQL server, Access-to-MySQL can export information into MySQL unload record.