I am working on a complex project having large database having more then 30GB and millions of records in each table which is oprrational. I was facing the issue like server is crashing frequently when high trafics are comming. Remebrer This is not website so that I can set the cache. This is the API based solutions where our API services are using by hundread’s of companies. Server crashing was causing for interruption on our services. So, I had started to looking on the causes for crashing and I have optimized the code and some are optimizing also optimized the server. If you will think that resource is less then resource is not less. Server was crashing on 64 GB RAM so that increased to 128GB Ram and after ward service was also crashing. So, I have started on server configuration and code. What I have done below and servers are going smoothly now.

1. Optimize the MySQL server by settings in mySQL.cnf like memory management,threads management, max connection, and cache connection. but This was not enough. When I run the command like “show processlist”. what I have seen that many of connections are in sleep mode. So I have reduced the time mySQL waittime and intractive time to 30 seconds default was 28800. After this problem has been resolved for crashing the server but get another problem related to execute long SQL. PHP programmers are doing common mistake for writing the code and I am solving those codes in my projects.

I am writing this article because Another developer do not to do such mistake and if found by some one then they can solve easily.

2. Do not use to “root” user for connection strings. root users has all rights related to your core mysql structure and settings. if you will not use root then you will be protect from many attacks and self destruction command by root also.

3. Most of the coders open the connection using “mysqli_connect” but they are not closing the connections using mysqli_close() or mysqli->close(). If you will not close these connection then you will face the problem like unwanted threads will available in mySQL memory in sleep mode.

4. Most of the programmer are using loop within the loop for fetch the query. like below,

qry = mysqli_query(con,”select * from tabl”);
while(res = mysqli_fetch_assoc(qry)){

qry2 = “select * from tbl1”;

}

they can avoid for such condition and try to write the code in single query. if they can not write then they should use php variable to store the code. what will be the problem if you write the code like this. I am getting the eg of small database around 5000 records in tbl. your inner query will be execute 5000 times and if one query is taking 0.01 second then your code will take around 50 seconds, This is small eg otherwise complex query is taking the time b/w 2-10 seconds. I have set mysql wait time is 30 seconds. So my threads will be kill in 30 seconds and my operation will not complete.

5. Use Limit in sql query for large set of results.

6. Try to use less operator like distinct,subquery(query within query), group by. And never be use these operator if you are using query within loop.

7. You can use “CASE” statement in your query to get many results based on conditions rather then to execute many queries one by one if query will complex in large dataset then this can take upto 10-20 seconds but you will avoid

8. if you will need to insert multiple record in one excution like this
for($i=0;$i<$n;$i++){
insert into table(col1,col2)values(‘val1’,val2)

}

you can use above code like this

insert into table(col1,col2)values(‘val1’,val2),(‘val1’,val2),(‘val1’,val2),(‘val1’,val2),(‘val1’,val2),(‘val1’,val2)

So, use your loop to concatinate your string and execute your query in single command after loop.

9. Programmers are using if(isset($_POST[‘var’])) but you can use if($_POST[‘var’]) this also.

10. Use IN statement to fetch multiple records rather then to execute many query in loop

foreach($values as $v){
select * from tbl where col=$v

}
you can use above like this

foreach($values as $v){
$arr[]=$v;

}
select * from tbl where col IN (implode(“,”,$arr))

There are many things to improve but above are main things to improve. Mainly focus on all above point to avoid any crashes. if your server still crashing then to get help from web server expert and networking or increase the resources

Also, I will suggest to use bigdata for large set of database.

Please follow and like us: