SQL Network Traffic

[MemoNick]

Hi guys,

 

I'm working on a news website. I am at the development/testing stage. I noticed that the network traffic stood at 3TB for the past 6 days. My homepage consists information from a number of tables to display the latest news from different sections and also a poll. However, in my limited expertise, I can't imagine how I created such network traffic.

 

My questions are these: is it normal to have this amount of traffic? And secondly, how can I optimize my tables (I'm currently taking a look at indexes).

 

Thanks in advance,

Nicholas

[kicken]

Are you talking about just traffic between your Database server and webserver, and not traffic from external sources like people browsing the site or a crawler of yours downloading the news?

 

Either way really, for something that is "just development/testing stage" I would be inclined to believe that no, you should not really see that much traffic. Without knowing more about the situation though it's hard to say for sure.

[MemoNick]

The data is as follows (all transfers from from mySQL):

 

This is what I am getting in phpMyAdmin: http://oi43.tinypic.com/2v0k8rc.jpg - one process, around 50 connections. The one process is me, in phpMyAdmin.

The biggest problem is with the traffic associated with these connections - 3.3 Terabytes and counting in 6 days: http://oi41.tinypic.com/2lj6n9t.jpg

 

In the beginning of development, I forgot to use mysqli_close($con), so I figured that could be the problem. I was looking for ways to kill those 50 connections, but I can't find any.

 

Of course, with the site not yet open, the problem would escalate far more with many more connections, so I was looking for ways to fix this before it gets out of hand.

 

Thanks

[kicken]

You don't need to be calling mysqli_close yourself, PHP will take care of that cleanup when your script ends. You should only be opening your connection once during your script so make sure that you are doing that. If it's just you browsing the site, then you really should only be seeing a couple of connections at most.

 

The next questions though would be whether you are using shared hosting or dedicated hosting. If it's shared hosting, those graphs you see may not be just you, and might include other uses on that system. I'm not familiar enough with PHPMyAdmin or Hostgator to know what exactly those graphs measure.

[jazzman1]

You are just a web developer. The traffic to db could come from many different points of the server. Don't worry to much about that This is a DBA's or sysadmin's job. About the indexes, we cannot help you again unless you want to show us database schemas and provide us some details about applications.