Home Mysql One MySQL Performance Tip You Must See

One MySQL Performance Tip You Must See

709
0

I run about twenty websites, previously spread over shared hosting accounts. Last month, my hosts, each independently, shut me down for overutilizing the 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 GoDaddy’s dedicated hosting. The price was proper, and for just over $100 a month, I got a nice server with 2 GB of memory, 500GB of monthly 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 website online which does UFC predictions and UFC outcomes. On fight night, the site can get pretty busy, and my previous excessive traffic turned into 18,000 traffic in the 3 or so hours that the fights take place. 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 have most of my answers.

It’s critical to recognize that GoDaddy is now not at fault. I asked for a container with certain specifications, and that’s precisely what they gave me, complete 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 was 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 be glaringly beneficial. I noticed, looking at the web, that Apache has a little caching and, even better, a few-minute caching; however, it is not considered production-ready within the 2.0. Fifty-two builds that I even have, so I discarded that concept as a substitute for quick. I did, however, word a few matters when I looked at MySQL optimizations.

The first query is, do you need to emigrate to MySQL? Using MySQL server as fact storage rather than Microsoft Access gives numerous benefits: high performance, cross-platforming, more than one customer gets right of entry to, strong security features, and much more. So, if you are building a complex multi-person system, you 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 is desired to be moved, there isn’t always much benefit to using MySQL in this type of situation.

There is the usual way to export Microsoft Access statistics to a 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 through the Connector/ODBC ODBC driver supplied by MySQL AB. To migrate the MS Access database to a MySQL server, right-click on the database and choose the “Export” option. 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 not have a complicated structure, the answer is “yes.” Otherwise, you need to be aware that Access will now longer export index data with the data. You will need to build indexes for 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 no longer has a CURRENCY facts kind; as an alternative, it creates a column with DECIMAL(19, four) for the same purpose. 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.

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 grmoreIf you need more to manage the migration or do not have direct access to the vacation spot MySQL server, Access-to-MySQL can export information into Mya SQL unload record.