Lag/Choke Between MySQL Server And Frontend
Dec 10, 2007
I run a rather busy forum. Recently we've finally moved onto a dual server setup. Our setup consists of this:
Files/Http:
C2D 6400
2GB RAM
2x 80GB HDD's in RAID 1
Lighttpd/Xcache/php 5.2.5
DB Server:
Xeon 5360 (2.4Ghz 4MB Cache)
2GB RAM (Upgrade to 4GB coming this week... if we can get these issues sorted anyway).
2x 36GB Raptor's in RAID.
MySQL 5.0.x
=====
Now, when theres less than 1,000 users online my board the server runs fine. The front end server has a load of <2.0 and the DB server goes no higher than 4-5. As soon as there's slightly more load placed on the server it's like they seem to choke. It appears that there's no MySQL data transferred between the servers for a couple of seconds before it goes back down. It's almost like a choking effect. The servers are connected directly to each other and have 100Mbps bandwidth between the two over internal IP's. At most there's probably 60 pages being served a second. On average perhaps 25/second.
Could it be a problem with my MySQL settings? Here's my.cnf
Code:
[mysqld]
safe-show-database
old_passwords
back_log = 75
max_connections = 650
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 16
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
innodb_data_file_path = ibdata1:20M:autoextend
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
I am running out of ideas and this is very frustrating. At the moment the site is more speedy on a single server setup than spread over the two...
View 8 Replies
ADVERTISEMENT
Jul 4, 2007
My dedicated server has been choking/lagging the past month or so. I sent my colo some tracerts and they said the problem is on my end.
The server is a dedicated game server machine, im running 9 servers on it. No more then 3 servers are full at any one time and the maximum amount of bandwidth that has ever been used at any one time was 5 MB. My bandwidth limit is 100 Mbits, so it can't be a bandwidth limitation problem.
I was thinking maybe my firewall is blocking some packets (i have stateful packet inspection enabled). Is there any kind of tests i can do to figure out what exactly is happening? (aside from tracerts, which dont really pinpoint the problem).
View 6 Replies
View Related
Mar 27, 2009
I have an apache server on windows which I wanted to speed up using caching systems.
I tried installing squid, and I got it working with a basic example config. It seemed to work well, however under more heavy load I experienced some weird behaviour where network output is 0 for several seconds at the time, and all clients just hang in the wind and wait for a respons.
Remote to the server is working fine, so it's definitely a squid issue.. with only apache running the server is constantly sending out data, no halts there.
Any hints on what errors I should be looking for?
View 1 Replies
View Related
Dec 18, 2007
I keep getting this error :
MySQL Error : MySQL server has gone away
How do I fix it?
my.cnf contents
Quote:
[mysqld]
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 800
key_buffer = 48M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 3000
thread_cache_size = 128
wait_timeout = 90
connect_timeout = 30
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 256M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_user_connections = 500
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
View 5 Replies
View Related
Jun 27, 2009
I have recently upgraded mysql server from 5.0.75 to 5.1.31 on my Ubuntu server 9.04 32 bit. After that when I am running phpmyadmin it is printing a warning :
Your PHP MySQL library version 5.0.75 differs from your MySQL server version 5.1.31.
View 2 Replies
View Related
May 8, 2009
Since my /var partition is full, so I moved /var/lib/mysql to /backup/mysql/.
Seems all the files are copied and I changed my.cnf reboot mysql, but all the web sites using db is not working anymore..
View 4 Replies
View Related
Jan 21, 2007
I installed the MySQL binary packages in /usr/local/mysql/ after removing the MySQL RPM package. MySQL is functioning when I executed /usr/local/mysql/bin/safe_mysqld. I reinstalled MySQL before I installed PHP. When I used a PHP script to access a MySQL database, it outputs an error:
Code:
Warning: mysqli::mysqli() [function.mysqli-mysqli]: (HY000/2002): can't connect to local mysql server through socket /var/lib/mysql/mysql.sock in index.php on line 2
However, I installed MySQL in /usr/local/mysql, not in /var/lib/mysql. How do I fix MySQL?
View 1 Replies
View Related
Jul 7, 2009
Which server for mysql only?
This is sort of a continuation to this thread here
Based upon the screenshots I provided below. What server would best suit my needs. I need this server to be able to handle the mysql usage and server load.
This server will be only for mysql. The rest of the site (html, videos) will be hosted on a different server.
If you think these servers are not enough for this please let me know any suggestions you have pertaining to server specifications.
Server 1:
Dual Core
Core 2 Duo E4500 2.2Ghz 2MB Cache
500 GB SATA II Hard Drive
2 GB DDR2 SDRAM
Server 2:
Dual Core
Core 2 Duo E8400 3.0Ghz 6MB Cache
500 GB SATA II Hard Drive
4 GB DDR2 SDRAM
Server 3:
Quad Core
Core 2 Quad Q9300 2.5Ghz 6MB Cache
500 GB SATA II Hard Drive
4 GB DDR2 SDRAM
View 3 Replies
View Related
May 9, 2009
i have install a durpal on my centos server(php5,mysql5,suphp),
for some feature,
i get a lof of error like
---------
Warning: MySQL server has gone away query
---------
i try to google to find some solution,
but the issue is still here,
do you know any problem make this?
View 9 Replies
View Related
Jun 23, 2008
I have an older script that works well on the config below. Can anybody know where I find such boxes?
PHP version 4.4.7
MySQL version 4.1.22-standard-log
View 1 Replies
View Related
Sep 29, 2007
My site is getting big and my server can barely hold all the mysql processes.
Once I had a similar problem with bandwidth because my media files were eating quite a big chunk of it. So, I just bought a media server and changed some HTML to hotlink all the media from the media server. I would like to do something like that with my MySQL databases, to get some load off my main server.
Should I get my current host to cluster another server for mysql? Or can I use a completely different server (from a different webhost) for mysql?
View 7 Replies
View Related
Sep 19, 2007
I have A and B server, i linked a cross cable in both server second ethernet port.
I have A as 10.0.0.1 (eth02) and B as 10.0.0.2 (eth02).
Both can ping to each other.
Now how could i setup 10.0.0.2 mysql to accept connections from 10.0.0.2?
The idea is to setup in A server to be able to backup mysql or write to B server.
View 10 Replies
View Related
Jun 7, 2007
I have an issue with one of my scripts,it wont run on mysql 5.x
if its possible to run mysql 4.x and 5.x on the same server?
View 11 Replies
View Related
Feb 15, 2007
as you know most of servers use utf8 for mysql charset
1- i use following command to backup as utf8
PHP Code:
mysqldump -u root -p123 --default-character-set=utf8 forum > /db.sql
But it is latin1 as you see following
PHP Code:
CREATE TABLE `access` ( `userid` int(10) unsigned NOT NULL default '0', `forumid` smallint(5) unsigned NOT NULL default '0', `accessmask` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`userid`,`forumid`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
why?
View 4 Replies
View Related
Jan 25, 2007
How would you offer MySQL 4.1.x and 5 on the same server for your customers?
Would you just change the port of MySQL 5, for instance 3307 so that both versions can coexist?
View 0 Replies
View Related
May 31, 2007
we got this error on mysql db after that we have change the my.cnf value
Description : MySQL server has gone away. Please contact with your host provider, the connection time should be increased a little.
It's the reason of the session issue (I see that sometimes it's caused on the session table update). It seems like there are some problems with search.
here's my.cnf how can we fix this prpblem?
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
#skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1800 ## era a 1024
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci
[mysql.server]
user=mysql
#basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
View 4 Replies
View Related
Mar 28, 2007
I didn't find a way to do it. How to have MySQL 4 and 5 on the same server?
View 5 Replies
View Related
Jan 27, 2006
How do I get MSSQL Server and MySQL working on the same server?
I have MySQL running but in PHP.INI when I uncomment the extension=php_mssql.dll line MySQL stops working.
Why is this?
View 2 Replies
View Related
Mar 25, 2009
telling me that mysql is hitting loads of 25, which he says is very high and i don't quite know what it means. He is strongly recommending that i shld get another dedicated server for mysql alone.
So can i get some advice on how I can assess the validity of his recommendations? I see that my CPU is load is hitting 125-150% daily. So i suppose there is some truth?
He is recommending that i get a raid-5 which is best. How can i know a suitable configuration of a new server to get? Of course, if i got all the money in the world i shld buy the best. But with constraints on budget, i want to get one (if i really need a new server) that is good for current loads with some projected margins for growth.
View 14 Replies
View Related
Nov 1, 2009
I've a huge site that runs on PHP and MYSQL, I'm going to get a dedicated server and make it for MYSQL so I need to know what server should I get for MYSQL ?
does it need to have lots of ram or should I get high CPU ?
View 7 Replies
View Related
Jun 5, 2009
as default,we may install all the feature on the server,
but i find some shared hosting server provider install web httpd on the front,
and mysql on another one,
could you share your experience about how do you do now?
View 9 Replies
View Related
Jul 25, 2009
It possible to run mysql on differnet server
iam haveing cpanel server
so it possible to run mysql for only one account on different server
i know about remote sql,but i think it wont work for only one account
please advise me for this
View 11 Replies
View Related
Jul 1, 2009
I did an apache rebuild and about two hours later, my mysql stopped working.
So i tried various rebuilds to see if perhaps it was suoshin (this caused php to stop working on my server before) but to no avail, this hasnt fixed it. I cant even access the databases via phpmyadmin.
I've restarted the mysql server, apache, i've rebooted a couple times. I don't know what to do.
View 7 Replies
View Related
Apr 25, 2009
lets say my web server IP is A, and my dedicated MySQL server is B.
Apache and PHP have been installed on A. MySQL server has been installed and configured on B.
Im just wondering, do I still need to install MySQL client on the web server A?
View 9 Replies
View Related
May 8, 2008
to pickup a dedicated MySQL 5 server from Dell running Linux. I have the option of either getting:
(1x) Quad Core Xeon E5430 2x6MB Cache 2.66Ghz
OR
(2x) Quad Core Xeon E5405 2x6MB Cache 2.00Ghz
They are about the same price. What option is best? The 4 cores but 666mhz faster each core, or double the cores (8), but less mhz per core. Can MySQL 5 even use 8 cores effectivley?
View 10 Replies
View Related
Apr 9, 2008
What can you do to stop so many users on your server that use mysql as apart of their website from getting this message: MySQL server has gone away
Like there has to be something installed or configured on the server that causes this as well, as I know people are taking too long to load something and it through that message, but it has to be something you can do server wide to get reed of it?
View 13 Replies
View Related
Sep 22, 2008
I have setup php and mysql on a windows IIS 6 server. php has installed fine and works but I have 2 problems
1. When i browse to http://localhost i get a 403 error but when browsing to http://localhost/index.php it works fine. It makes me think that the server does not know what extension to default to when doing a directory listing
2. I have phpmyadmin installed and the cfg file is set to do a 'config' authentication. That works but everytime i go to submit a form to create a new database or anything i get prompted with an HTTP user/pass. I'm not sure if this is a phpmyadmin issue or a php configuration issue
View 1 Replies
View Related
Jan 17, 2008
I got some questions about dedicated mysql server.
I read some things about compression used, should I give this a try?
Image
Any other tweaks? The current os is centos (The server is donated) I plan to switch to debian etch or freebsd 6.2
I got my.cnf tweaked right for the 2gb of ram I think but seems alittle slower then when it was on localhost, I was thinking dedicated mysql server would be quicker, its slow enough that my light weight mysql pages are running much slower
As for the 100mbit line, what else do you guys run? I'm thinking of going to 1000mbit so I'd get about 200mbps atleast, any better way?
View 3 Replies
View Related
Jan 29, 2008
1)i want to delete a folder in /var/lib/mysql,
when i use "ls" to check ,it shows with (space)foldername,
i use rm -rf ./foldername, it failed,
and i use rm -rf ./(space)foldername, oh no,all the files in the /var/lib/mysql are gone,
2)i do not have the backup files of the folder at all.
3)how can i rebuild the system files of the mysql server with a smooth way?
View 5 Replies
View Related
May 17, 2008
Running CentOS 4.6. Currently, the my.cnf contains:
Code:
[mysql.server]
user=mysql
basedir=/var/lib
If I moved all files and directories under /var/lib/mysql/ to /harddisk3/mysql/ what do I enter for the basedir path?
View 5 Replies
View Related