hi, i have a database with around 700,000 rows which is for a gameserver i have and I added a login system to my website and after a while, with all my users using the website the MSSQL server load goes very high, same with CPU load, it goes almost to 100%.. now imagine 600 users on my site .. after a few minutes with this site up people start having a lot of lag in-game. my question is, how can I optimize my website so it will not get my mssql server load so high? some thing's i've thought of: - delay the user actions in websites like for 30 seconds, so if they do any action that requires to run an MSSQL query they will have to wait 30 seconds to do any other action. - separate my website server from my mssql server. would this help in any way ? ---------------------------------- something that is very confusing is that if I DON'T use the login system with session stuff my mssql load is not high .. and on each action the user does it's required for them to input their account and password .. how come this happens ?? isn't it better the login system with session so i store the user and pass one time and no need to run a query to check for the account details ?? any help will be greatly appreciated !
just check on you sql script try to avoid the * script from select for example. make sure you use the fields that are really important
- Having MySQL on its own server will greatly increase efficiency, as your website grows, you can add more disk space and ram to accommodate the changes. - Use Cache plugins like Memcache and/or APC cache, this will greatly decrease load on your MySQL. The main idea behind cache is that you cache things and then show them to user, having a process that access MySQL once in the backend to load cache, thus taking the stress away from MySQL. - Monitor your processes in MySQL (show processlist, see which processes/queries are taking too long and try to optimize them.
Thank you, I am using some queries with * and then using mssql_fetch_array, instead i should select specific columns and use mssql_fetch_row, is this correct ? You mentioned using cache plugins, does this cache plugins you mentioned also work for MSSQL ? because I don't use MySQL. Also, my website is a 'control panel' for a game account, so any action they do has to be taken immediately otherwise they can login in the game and if the query is ran when they are online it will not take any effect. Will caching queries delay the action by much time or just a few seconds? Thanks for your answers, very helpful.
you also can optimise your queries by adding indexes to your site! You could use EXPLAIN <YOURQUERY> in for example phpmyadmin and see what the querie exactly does! If you need more help, let me know!
You need to separate your SQL SErver from your webserver. The lag time could not only be from inefficient queries, but also bad development. The volume of your site may need multithreading. Are you using IIS? Are you using PHP as an ISAPI or CGI module? But yes, starting at the data level, make sure you use good queries...no select *, be explicit on what you are selecting. Make use of stored procedures (they typically run faster on SQL Server.