ASTRAPI Posted September 30, 2011 Share Posted September 30, 2011 Hello Is there any tutorial on how to setup mysql on a second server just for my database? At the moment i use xcache for my forum and sphinx. Do you think that i will get better performance using another software with this kind of setup with the second server only for mysql? Also i use cpanel and i want to ask if i have to use cpanel on the second server just for the sql? Thank you Link to comment Share on other sites More sharing options...
Cloaked Posted September 30, 2011 Share Posted September 30, 2011 You have to make sure the db server is on the same switch or at least the same virtual network as the web server. When you set up mysql make sure that you allow your web server IP to connect to the mysql server. Then all you do is dump the data and import it into the new server. Then setup config php with the IP of the db server and username and password that you allow to connect from remote servers. Link to comment Share on other sites More sharing options...
Gary. Posted October 1, 2011 Share Posted October 1, 2011 Or just get a faster disc and move the mysql to that, Be alot cheaper than getting another server. Link to comment Share on other sites More sharing options...
Rhett Posted October 1, 2011 Share Posted October 1, 2011 How large is your site? It takes a pretty big site to require a second server in most cases. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 1, 2011 Author Share Posted October 1, 2011 I prefer to get a new server so i will have dedicated cpu and ran for it. Do i have to use cpanel also there or is it easy to just install the mysql there? It will be easy to create a database and user and al the rest if i just use mysql installation? Thank you Link to comment Share on other sites More sharing options...
Rhett Posted October 1, 2011 Share Posted October 1, 2011 All you need to install is mysql.... and of course firewall etc... I would suggest you have your hosting provider do this if your not familiar with the configuration/setup though. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 1, 2011 Author Share Posted October 1, 2011 Is a self managed server and i try to get some info so i can try it myself ... Does anyone can post the commands that i need to install correctly the mysql on second server please? Then i need to import my database using ssh or to just transfer by copy the files to the new server? At the end i will configure my.cnf to point to the new server. Thank you Link to comment Share on other sites More sharing options...
CADguru Posted October 2, 2011 Share Posted October 2, 2011 I run 3 servers for my site - Web, DB and backup. Getting the site functional is really easy - however, sphinx configuration was a bit of a nightmare. I *think* I still have the details of how it eventually worked, but we have 4million+ posts and 23k+ members, and it runs beautifully. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 2, 2011 Author Share Posted October 2, 2011 Can you please send me any details that you have? I have 2,500.000 posts and 200.000 users. Per minute i have about 500 - 900 users. Thank you Link to comment Share on other sites More sharing options...
CADguru Posted October 2, 2011 Share Posted October 2, 2011 OK. I have gone through everything, and my notes are at best fragmented. We had major issues just working out what to put where, I will check config, but I believe we have sphinx running on the webserver, but the index load is done by the DB server - make sense? To make sphinx work on the webserver, it looked for mysql libraries on the webserver, and they had to be there or it wouldnt run. So we have a mysql instance on the webserver that does nothing but keep sphinx happy - it still runs against the db server. I will try and compile something of use for you. Link to comment Share on other sites More sharing options...
Rhett Posted October 3, 2011 Share Posted October 3, 2011 Can you please send me any details that you have? I have 2,500.000 posts and 200.000 users. Per minute i have about 500 - 900 users. Thank you You can do that with one server with ease... if you have a proper setup, I have a few sites running at a steady 1000-1800 connections and they run very very well. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 3, 2011 Author Share Posted October 3, 2011 The 500-900 users online are using about 4000 connections as i can see using: netstat -n | grep :80 |wc -l I am using in front of Apache the cpnginx plugin and i use for Apache dso php handler plus prefork default settings and xcache and sphinx... Keep alive off And i have disable persistense connections to mysql. Link to comment Share on other sites More sharing options...
Rhett Posted October 3, 2011 Share Posted October 3, 2011 I use the same stat... and 500-900 users should be not showing 4000 connections imo... but maybe it's due to your config perhaps... Peak on this one site is about 16,000 users in a one hour time frame... with that the connections never surpassed 2400 at any one time. So according to your numbers you would be seeing about 36000 users in one hour? I would be interested in seeing this site. Link to comment Share on other sites More sharing options...
Gary. Posted October 4, 2011 Share Posted October 4, 2011 The 500-900 users online are using about 4000 connections as i can see using: netstat -n | grep :80 |wc -l I am using in front of Apache the cpnginx plugin and i use for Apache dso php handler plus prefork default settings and xcache and sphinx... Keep alive off And i have disable persistense connections to mysql. As to the first one, Thats normal as nginx will chuck alot of connections to apache... If you check by this command ( I think ) then you will see your server IP in there.. If so then it's 100% NGINX and not the real connection.netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n Mysql persistent connections, Keep this on if you are running 1 site, You only disabled for shared hosting communities to stop using alot of resources by keeping connections open... Since your website is busy you may want to use keep_alive.. This will keep the connection open for a set time where if you keep this off it may use alot of resources by constantly opening / closing conenctions on a very busy board like yourself has. Keep alive for apache is always best set at 2. You said you has another server with cPanel, There docs here: http://docs.cpanel.n...etupRemoteMySQL If you read... It will also say: If you wish to use remote MySQL, you need to ensure that MySQL is running on a server that is not running cPanel. Yes this is possible as I done this myself after alot of hard work... You have to keep in mind that even if you tell cpanel to use the remote mysql server, your scripts are probably all hard coded to connect to localhost which they would happily continue to do unless you shut down the local copy of mysql and force them to update their scripts to connect to the remote host. I'll say this again ! ALOT of hard work.. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 4, 2011 Author Share Posted October 4, 2011 Thanks for your reply :smile: @ Rhett I don't know how you calculate them but i have more than 500 per minute daily for 4 hours about peak time. @ Gary. netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n Yes this command is showing my server ip there with the big number of connections that i was talking. But i ws ask for thi also at nginx and they told me that the nginx will pass all real connections to Apache and if i have 4.000 connections it will pass 4.000 connections as it will not create any fake connections or anything else related.Mysql persistent connections I will try to enable it and test again.keep_alive 2 I think no one will do a second click in just 2 seconds. When i have the problem with my forum i can see a lot of :/usr/local/apache/bin/httpd -k start -DSSL Thank you Link to comment Share on other sites More sharing options...
Rhett Posted October 4, 2011 Share Posted October 4, 2011 Gary explained your very high connections above.... it's due to NGINX , those are not user connections... imo your wasting your time trying to set this up on two servers when one properly configured server will handle this with ease. I just don't see the traffic on your site that would require such a move. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 4, 2011 Author Share Posted October 4, 2011 Disabling nginx the site is taking about 20 seconds to load :( Link to comment Share on other sites More sharing options...
Gary. Posted October 6, 2011 Share Posted October 6, 2011 I think no one will do a second click in just 2 seconds. When i have the problem with my forum i can see a lot of : Thank you You not heard of prefetch ? See if that's enabled globally then one single IP can connect as many times as it likes to load the content faster.. For an example, If I visit your site my IP would connect around 15 times a second to load the page as quickly as possible... If it's disabled then only 1 IP would connect, But this would take a fair bit of time to load your IPB site usually. These: /usr/local/apache/bin/httpd -k start -DSSL Thats more than likely you have keep_alive enabled, You will always get those unless your running suPHP.. When you see that it's fine and there only idle threads.. They will die depending on your timeout of apache. NGINX: How many IPS do you see of your server.. How many workers does your NGINX have and how many worker connections. So your on fast_cgi, Have you adjusted the paramaters for that in the pre_virtual_host or is it default, ( Blank ) ?? Have you tuned the fast_cgi controller to close connections and such, And what is the process count for mod_fcgi status, Also whats the apadche status showing..Requests running and how many are idle. Also.. I see you have hundreds of topics from different scripts / perl modules and apache changes and so on.. I'm confused now on what your actual issue is ? Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 7, 2011 Author Share Posted October 7, 2011 About prefetch where i can check it on my cpanel server? I have keep alive off. worker_processes 6; worker_rlimit_nofile 50000; worker_connections 2048; I have 8 core cpu. I am using DSO apache handler and prefork (default settings) Thank you Link to comment Share on other sites More sharing options...
Gary. Posted October 7, 2011 Share Posted October 7, 2011 worker_processes 2; This is ideal for 4 cores and above. worker_connections , Yours is a little low there, Change that to around 8040 worker_rlimit_nofile 20480; Lower yours to this.DSO ! Unless you want your server to be breached I suggest you change that ASAP as you have no security on your apache what so ever.. DSO = BAD BAD BAD. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 7, 2011 Author Share Posted October 7, 2011 Xcache working well only with DSO and is faster for php. I was testing xcache with fast cgi and it was not working well... What do you think? As i also use only one account on the server and i will never use another account i think is safe or not? Link to comment Share on other sites More sharing options...
Rhett Posted October 7, 2011 Share Posted October 7, 2011 Honestly I think you need to hire a systems admin to configure your server for you.... you are putting yourself at risk here... no offense meant. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 8, 2011 Author Share Posted October 8, 2011 Ok thanks. Link to comment Share on other sites More sharing options...
Gary. Posted October 8, 2011 Share Posted October 8, 2011 Xcache working well only with DSO and is faster for php. I was testing xcache with fast cgi and it was not working well... What do you think? As i also use only one account on the server and i will never use another account i think is safe or not? If anything fast_cgi will be much faster, If you say it does not work well then it's configuration problems, DSO can be quick mainly as there is no checks or nothing just apache straight out of the box. It does not matter if your the only account, DSO is not for a live enviroment and should use with caution. If your unsure of things then do not play with it futher your box settings and configurations seems very bad and you should contact someone with the modules you like and get it properly configured, All sys admins would advise you if something would not work right, Like your previous post, you have memcache, Memcached, DSO, xcache and nginx all working at the same time so I can assume there will be issues with that and alot of memory leaks. There are a few factors you need to look at, Speed, Stabality and security, It's very hard to combine them, suPHP will be best for security but then that module can slow things down and can cause high load on busy server, Yet nginx, eaccelerator and such cannot function with it. DSO is straight forward, There is no security on that and its simple to use, Install and it's working, Not a great deal can be done with it and if you start adding modules to that then your basicly going towards a basic cgi. Like most people, They want speed, stablity too but using DSO is not the way forward. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 8, 2011 Author Share Posted October 8, 2011 Can i ask if memcached work well with fast cgi? Thank you Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.