Hello friends, I have a problem on hand. My hosting company has suspended one of my hosting account coz they say it slows down the server due to its slow queries. I am running site-sift script on these and am handling MYSql problem for the frst time - so please advice in a novice language. I am pasting some of the code that the hosting company provided to show that the query has a problem. P.S - i have 4 directories hosted with all having Site-Sift script and the company says all 4 have a problem. Info given by the hosting company : Sir, Your have too many slow queries in script and same case with other databases: # User@Host: squareone2[squareone2] @ localhost [] # Query_time: 15 Lock_time: 100 Rows_sent: 1 Rows_examined: 782 SELECT * FROM pages where mid = '0' and active = 'Yes' and catID = '25' order by id asc LIMIT 0, 10; # Time: 060807 1:35:58 # User@Host: squareone1[squareone1] @ localhost [] # Query_time: 11 Lock_time: 10 Rows_sent: 0 Rows_examined: 1680 use reindirdb; SELECT * FROM pages where mid != '0' and active = 'Yes' and catID = '205' ORDER BY mid DESC; # Time: 060807 1:36:00 # User@Host: admin[admin] @ localhost [] # Query_time: 16 Lock_time: 10 Rows_sent: 0 Rows_examined: 0 use psa; SET timestamp=1154932560; update sessions set click_time=NOW() where sess_id='24187b1268da170812bf0980264548ee'; # Time: 060807 1:36:16 # User@Host: squareone1[squareone1] @ localhost [] # Query_time: 7 Lock_time: 10 Rows_sent: 1 Rows_examined: 0 use reindirdb; SELECT * FROM categories WHERE id = 25; # Time: 060807 1:36:22 # User@Host: squareone2[squareone2] @ localhost [] # Query_time: 44 Lock_time: 50 Rows_sent: 3 Rows_examined: 782 use links20db; SELECT * FROM pages where mid = '0' and active = 'Yes' and catID = '27' order by id asc LIMIT 0, 10; # Time: 060807 1:36:27 # User@Host: squareone1[squareone1] @ localhost [] # Query_time: 9 Lock_time: 10 Rows_sent: 3 Rows_examined: 1680 use reindirdb; SELECT * FROM pages where mid = '0' and active = 'Yes' and catID = '25' order by id asc LIMIT 0, 20; User@Host: squareone2[squareone2] @ localhost [] # Query_time: 9 Lock_time: 10 Rows_sent: 1 Rows_examined: 0 use links20db; select * from settings where id = '1'; # Time: 060807 1:59:41 # User@Host: squareone1[squareone1] @ localhost [] # Query_time: 14 Lock_time: 10 Rows_sent: 0 Rows_examined: 1680 use reindirdb; SELECT * FROM pages where mid = '0' and active = 'Yes' and catID = '349' order by id asc; Thanks. SEO Ocean
Excessive server usage; the other way around. It's time to upgrade your hosting account. I have no experience with site-sift script though I do faced some problems 5 months back when I was told to switch towards VPS from shared hosting due to excessive server usage at my php/mysql based website.
SEO Ocean, you could try asking over at the SiteSift script support forum if this is a known issue. http://forum.site-sift.com/site-sift-listings.html
Site sift on two of my directories, and there is no slowdowns on mysql's. Could be the host is using an old version of mysql (some hosts hate to pay to upgrade).
They are all single table queries, I dont see how the can be a resource hog. If you have moved the database from another site, make use that there is a primary key set up on all the tables.
Normally when you're faced with slow queries you can look to add indexes. In this case the queries are so simple and the tables appear to be so small that I can't see indexes making much of a difference. I suspect that the queries and the script is just fine and your host has over provisioned the machine or you're just over stretching what your current hosting account can handle.
How big is your DB? Is it on the same server as the webserver? Did you optimize it (through phpmyadmin). did you flush session tables and free up other resources? how many queries/second and queries/page do you generate?
The DB isnt too big - the .sql file is just about 1mb ! Yes the DB is on the same server as the Webserver. Phpmyadmin - optimized thru it. Session tables flushed. Thanks. Seo Ocean
In that case I can only recommend to switch the host. I once had the same problem. The host kept blaming it on me until I had enough and moved my sites somewhere else. Voila, everything ran fast again. So even though they may respond saying their servers are fine, don't believe what they say. Who are you hosting with?
I have bought over another hosting plan - i was hosted with serversea hosting. Now i moved to hostmysite.com - hope to have better results.
actually, I had the same problem.The server load would go high and then my hosting company suspended my account. Did some research and found that if you index certain fields that it will make the queries run smoother. So if you have several fields, index the primary fields and other fields that have the lots of the same data. Like a id (i.e. company id). This worked for me and now the website and queries run faster. Obviously once your DB gets bigger, you can optimize them via MySql. Hope this helps.