Archive

Articles taggués ‘optimization’

Ten MySQL performance tuning settings after installation

04/05/2016 Comments off

mysql performance tuningIn this blog we’re going to discuss the top ten MySQL performance tuning settings that you can implement after an installation.

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest changing a few MySQL performance tuning settings after installation – even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings fro MySQL performance

Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

Lire la suite…

How to Optimize MySQL Tables and Defragment to Recover Space

04/05/2016 Comments off

MySQL OPTIMIZE TABLE command

If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.

This will result in lot of unused space, and also might affect performance.

So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.

This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.

1. Identify Tables for Optimization

The first step is to identify whether you have fragmentation on your MySQL database.

Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.

mysql> use thegeekstuff;

mysql> select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
 from information_schema.tables 
 where round(data_free/1024/1024) > 500 
 order by data_free_mb;

+------------+----------------+--------------+
| table_name | data_length_mb | data_free_mb |
+------------+----------------+--------------+
| BENEFITS   |           7743 |         4775 |
| DEPARTMENT |          14295 |        13315 |
| EMPLOYEE   |          21633 |        19834 |
+------------+----------------+--------------+

In the above output:

  • This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
  • data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
  • data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
  • All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.

From the filesystem level, you can see the size of the individual table files as shown below.

The file size will be the same as what you see under “data_length_mb” column in the above output.

# ls -lh /var/lib/mysql/thegeekstuff/
..
-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD
-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD
-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD
..

In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.

Lire la suite…

Why Image Optimization is an Absolute Necessity for SEO?

28/04/2016 Comments off

image optimizationWhen content for a webpage is created and published, image optimization is probably the last thing an author knows about. Well, it is an acceptable mistake if an author’s example is taken into consideration in this context. But, it should be in the “Absolutely-To-Do” list of an SEO Vancouver specialist before posting the graphical parts of web based content.

According to the SERP feature history of Mozcast Feature Graph, Google images search ranking is 3rd in the list with 32.4% of organic search results following Adwords – Side of 42.6% search results (2nd in the list) and Adwords – Top of 58.3% search results (1st in the list) respectively.

In this post, I’ll try and explain certain small SEO steps which can improve the visibility of your webpage with the help of image optimization:

  1. Naming of the Image Files: Even if it sounds absurd, a proper image file name plays a generous role in image optimization. According to google, a file name of “Testing Audi A8” does well than “IMG_909”.
  1. Character size of File-name: 3-5 words file name is optimal for an image file.
  1. Format of Image Files: When it boils down to the format of an image, JPEG steals the show. Instead of putting a PNG file in your webpage, try putting JPEG for better results.
  1. ALT Text: ALTernate Text is a great visibility influencer. Generally, every image should have an ALT text with it which should be descriptive enough for the crawlers.
  1. Do Not Stuff your image’s ALT Text with keywords. It is considered as a bad practice nowadays. Keyword stuffing is being target by google and everyone should avoid this dangerous habit.
  1. User Experience should be the primary concern of every SEO activities we do. Google has become user centric and it is always better to perform the activities taking the requirements of random potential user in the mind. This part should be taken care of carefully. The quality of images should be good yet it should not take more than 2-3 seconds to get loaded in a decent internet connection.
  1. Placement of Images: yes, the placement of images in a web page also decides the faith of the page. It is talked about and a proven fact that if the text surrounding the image is matching or, of relevance, it has a greater chance to get pumped up in the search ranking.

After performing above given steps, the changes of your webpage getting visible in google search results get higher than ever. Please the context of this article in mind. It is also very possible that image optimization does not yield you expected results but for this to happen, you might blame the content associating it as well. Image optimization should be your only way to get ahead but it surely gives you an admirable edge over the others.

Factors such as Image metadata, Dimensions of images and, such things are also said to be crucial factors for SEO Vancouver purposes but there is no definite proof of it. However, it is always advisable to keep an account on all of the relevant factors for better results.

Source: sotra.ca