A Perfect Guide for MYSQL and Security – Creating the Best Linux Hosting Environment
By Kanquona Bhattacharjee
There are certain things you must be very careful of when you build your website. Performance of the website is a major factor in its success. This guide will give you a technical overview of your server performance. In this guide I am only concentrating on Linux environment as
1. It is cheaper.
2. It supports the fastest script engine PHP.
3. It has the default MySQL database.
4. Its web server is apache, which can be configured with less effort and
5. The Linux environment is most popular.
Before I begin, I must explain what makes a website performs rock solid. As you are aware in most of the websites database is a key factor which mainly stores and fetches data. A portal with slow database execution takes long time to execute and that can take away most of the visitors. If your portal accepts payment then security is the next thing, you shall be concerned of. So, my article will mainly revolve with these two features and a bit of other key areas which you would like to give focus on.
Hardware: This is the factor you must take seriously depending upon the kind of business you are doing and the number of visitors you are drawing. The hardware peripherals which affect the performance are mainly RAM and your CPU speed. RAM is a space which stores data temporarily for CPU access. But if your CPU cannot process faster, then transferring more data into the RAM area will not solve the problem, since the data will remain unprocessed. A better system is a balanced system and increasing just one parameter will never go to improve speed.
MySQL: Database is the most important parameter in terms of server performance. If I classify what makes a slow database or rather what you can do to improve data seek speed, then we get
1. Database design
2. Query standard and
3. Configuration as the primarily responsible reasons.
Database design: MySQL allows you to define data type definition in wide varieties. And you must use them optimally. Declaring the data type of a field as int(11) will unnecessarily eat up space, if the field stores value between 0 and 10. Remember, a good bridge is one which is has better architecture. Mere using good materials never build a good bridge. Here are a few guidelines.
A. For numeric value, guess what maximum value it might store and then select between tiny int, small int, medium int and int. Fields which you know will never store negative numbers should be unsigned.
B. For alphanumeric characters it is wise to select variable length than fixed length as variable length can adjust memory space depending upon size of data stored.
C. Normalize the table as much as possible. This decreases the chance of data redundancy. Also as tables get broken up, memory space usage decreases.
D. Proper indexing is the key for better search result. Index your table wisely and pragmatically. If you think, your table will be searched more on employee name than on employee code, create index based on employee name. Mysql arranges data based on available indexes, so search based on index results faster performance.
Query Standard: The fast data retrieval depends on the query you write. Here are a few tips on that.
A. Join query is always better than multiple nested queries. So try to use join queries if more than one table are involved in search.
B. Avoid Select * statement.
C. Try to test queries by writing them in alternate ways.
Server configuration is the last thing we can do to better the performance. Here are the important settings which affect the performance.
A. Key_buffer_size: This is the most important settings. The larger you set its value, the more MYISAM indexes store in memory. As most queries use indexes as search factor, memory plays better role than disk. The ideal settings is 25% of server memory but less than 50%, for improved performance.
B. The table cache: When mysql access a table, it places it in the cache. So increasing cache size plays an important role. So if you are accessing three tables, mysql opens three tables in the cache. Its default value is 64, but you must set it accordingly depending upon performance. An optimized one, not too high, not too low.
C. Sort_buffer: If you are sorting huge table then you must set its value high.
D. Read_rnd_buffer_size: The read_rnd_buffer_size is used after sort when rows are read in sorted order. If you are using many queries which use order by clause, increasing this value will help improve performance.
E. Query cache: first you must on query cache by setting query-cache-type=1 and query-cache-size can ideally be around 15-20 MB.
F. Tmp_table_size: This variable set the maximum size for a temporary table in memory. You must try avoiding temporary table by optimizing query. If the table becomes two large, MySQL creates a MyISAM table instead. Upping this value helps in speed, if temporary table size gets increased.
I would not discuss much on security issues rather would try to discuss it separately. But as an introduction SQL injection, Cross Side Scripting and HTTP trace are the most important security threat one website can get. SQL injection is a threat which exposes table name and data when a purposely built query is passed to the database through any web form. Basically when user input is incorrectly filtered for string literal escape characters. If we consider the below SQL statement
“Select * from employees where emp_name’=” + EmployeeName + “‘”
Now if the user input EmployeeName has any ‘ inside it, mysql cannot escape the string literal and exposes error.
Kanquona Bhattacharjee is a freelance writer and blogger. She has completed her post graduation in English from the University of Calcutta. She has some deep insight into social problems and often present these infront of the most strongest medium. Her personal blog is http://journeytolearning.blogspot.com/ She can be contacted at firstname.lastname@example.org