Improving MySQL Server Database Performance
Computers & Technology → Technology
- Author Aaron Webber
- Published November 18, 2010
- Word count 815
Even though this article will genuinely improve the memory employed by MySQL the performance gain might be dramatic depending on your queries and database usage. MySQL can be a rock solid, lighting quickly database server which has been designed for two factors pace and efficiency. It could be the Ferrari of databases: Light weight, quickly and Built for the higher pace tracks! I nonetheless hear an awful lot of stories from owners whose databases are running two slow. In my experience, the three principal places to seem for issues are:
-
Faulty Database Design
-
Lousy Queries
-
Server factors
Faulty Database Design
Correct database design might be the single most essential factor for the ensuring efficiency and maintainability with the database. Right here is what you have to answer when designing a desk: Can I decrease the size of knowledge that every row will have? Right here is what you are able to do:
-
Use unsigned numeric values when the application is not going to save negative numbers. Like the quantity ordered of an item in an ecommerce application is never going to become -$125.
-
Use Variable length values as an alternative to fixed length worth i.e. utilised varchar in place of char.
-
Tend not to use unnecessarily big field sizes. For most ecommerce application unsigned smallint is extra than sufficient to retailer inventory count. A field described as unsigned smallint can keep a max benefit of 65535.
-
Dont ignore normalization; its helps prevent unnecessary repetition of info. The part B of that is, dont overuse normalization. If the desk is not going to grow in size substantially, there's no point in normalization. For instance, if the user desk has just 20 rows (i.e. 20 employees in a organization), all attempts of normalization are wasted.
-
Use Keys. Dont decide keys by The customer id has to become indexed from the order desk. If the order desk is becoming searched 90% of your times by order date, it makes much more sense to index order date.
Remember, how a desk will probably be employed must determine how it truly is designed. Spending time right here will save years of frustration.
Lousy Queries
It sounds too excellent to become true but you wont believe the number of developers out there who totally suck at writing queries. You will discover two kinds of undesirable queries:
a) Unnecessary Queries: These are the queries that shouldnt have been made inside initial place. The only method to avoid it can be asking, Do I actually need to have this info?
b) Inefficient Queries: These are the queries that usually do not use the underlying desk structure or MySQL functions inside correct way.
Right here is actually a starting point to begin looking at issue areas:
-
Unnecessary utilization of Select * statements when the entire processing is becoming carried out on a single column. The far more info is fetched from the server the extra work MySQL has to do and much more bandwidth it takes.
-
Employing sub-query as an alternative to a join. On a correctly designed database, joins are incredibly rapidly. Making use of sub-queries just shows a lack of knowledge.
-
Improper use of Keys. It can be specially valid for range checks. Remember to use the Explain statement to check the utilization of keys and then use the use crucial statement in your where clauses to force critical utilization.
Server Factors
Every thing carried out correctly, there nonetheless may well be some server factors that may possibly be causing the technique to become slow. These are:
-
Hardware associated
-
Server configuration associated
Right here is what you are able to do about the hardware:
-
The extra RAM is on the method the much better it can be. MySQL often fetches info from the RAM and a lot more the RAM is on the technique, the much better it can be.
-
Purchase the fastest achievable RAM! A slower RAM is just irony.
-
As soon as you will be settled with the RAM size and velocity, seem for processing velocity. MySQL can use multiple processors.
As soon as you might be satisfied with the hardware, you will find a set of variables in my.cnf that you should seem at:
a) key_buffer_size: This describes the memory accessible to save the index keys. The default is 8 MB but you may set it to 25% with the RAM.
b) query_cache_size: This worth is by default ..! if you might have a great deal of repeating queries like in reporting applications and so on, make certain you set this benefit large.
c) table_open_cache: This determines the number of desk descriptors that MySQL will maintain inside cache. The default worth is 64. But, if you've 100 users accessing a desk concurrently then this worth require to atleast be 100. You also need to take into considerations joins and so on. Thus, this worth will need to also be kept large.
Read more Software Guide and Review
Article source: https://articlebiz.comRate article
Article comments
There are no posted comments.
Related articles
- The Ultimate Guide to 3D Animation: From Basics to Advanced Techniques
- Mitsubishi Electric proves heat pump compatibility with microbore pipework
- Why Small to Large Companies Continue to Use Dated/Dinosaur Technology
- 10 Ways Business Central’s Quality Inspector App Streamlines Quality Assurance
- 10 Ways Business Central’s Quality Inspector App Streamlines Quality Assurance
- The Rise of Sustainable Technology: Shaping a Greener Future
- Why Bullseye Engagement Offers the Best OKR Software for Businesses
- Web Development Companies in Canada
- How EasyPDF™ Forms Save Time & Money at Home and in the Workplace
- The One and Only 15-Second Digital Lien Waiver to Complete and Submit in Record Time Using the Free Adobe Reader
- The Impact of Employer Branding on Leadership Recruitment
- Augmented Reality (AR) in Business: Why Your Company Needs It
- Top 10 Reasons to Use Business Central’s License Plating App
- The Hidden Advantages of European Offshore Development Companies
- App Development: Transforming Ideas into Reality
- Automate you Chauffeur Service with A to Z Dispatch
- The Impact of Machine Learning and AI on Business: What the Future Holds In the modern busine
- Generate Flashcards Fast with AI: The Ultimate Solution for Developers
- Blockchain Interview Guide: Essential Questions and Answers for Success
- Eight Free Business Central Apps That You’ll Wish You Had
- How Artificial Intelligence (AI) and Machine Learning (ML) Are Transforming Computer-Based Trading Platforms
- The Role of Gas Engineers in Modern Energy Systems: Linking to Sustainability and Innovation
- The Significance of Stars in the Universe and Their Impact on Human Culture Throughout Evolution
- Exploiting Artificial Intelligence for Urban Mobility Transformation: A Case Study of Guatemala City
- Top 10 Ways Business Central Users Streamline Shipping
- The Impact of AI on Job Security and Availability in Africa: A Future at a Crossroads
- CNC Machining Vs 3D Printing: Which Technology Is Right For Your Project?
- The Future of Search: Embracing AI-Powered Search Solutions
- Low-Fidelity Vs High-Fidelity Prototypes: When To Use Each In Product Design
- MARKET SEGMENTATION