I run a site that gets 2,500 unique visitors generating 30,000 page views per day. My server runs on a Intel(R) Pentium(R) D CPU 3.40GHz, 2 cores and has 2 GB of RAM. It currently cannot handle the amount of traffic I'm getting. I need help increasing the performance of MySQL. (Please note this is not an Apache issue, the static HTML parts of my site run fine. It's the PHP/MySQL script that is slowing it down). I enabled MySQL caching, which worked fine for a while, but it uses too much RAM and slows the whole server down (without caching, the site is unusable). I tried "optimizing" the SQL queries in my script (along with optimizing SQL table), but that didn't do much. The slow query log doesn't show any specific query slowing it down. Here's the my.cnf variables: skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576 set-variable=max_connections=9999 Code (markup): They're all the default values (apart from query_cache and max_connections), which I thought would be enough for most sites, but apparently not. Any ideas on what I should do?
Can you explain the type of site this is? Wordpress, Forum, other CMS, Custom, etc.., and what sort of database usage per user, per page view, (Reads/Writes) your site would commonly see. Also, are you using any sort of front-end caching mechanism like APC?
It's a custom script. It runs 2 small SELECT queries per page view. Yes, it uses APC. A typical query is SELECT DISTINCT `download` FROM `download` WHERE `download` != '' AND `deleted` = 0; # Time: 100911 15:58:25 # User@Host: root[root] @ localhost [] # Query_time: 4.409534 Lock_time: 0.000094 Rows_sent: 6259 Rows_examined: 17309 Code (markup): That's from the slow log when the site was very slow (as you can tell from the query time). I'll give it a try. Anyone else have any ideas?
`download` and `deleted` columns are indexed, correct? What's the total number of rows in the table? Can you also post the full table structure with any indexes included?
Nope, they're not. I heard that indexing can sometimes slow down queries. `id` int(9) NOT NULL AUTO_INCREMENT, `download` varchar(999) NOT NULL, `source` varchar(999) NOT NULL, `link` varchar(999) NOT NULL, `date` varchar(22) NOT NULL, `thumb` varchar(999) NOT NULL, `desc` varchar(999) NOT NULL, `deleted` int(11) NOT NULL DEFAULT '0', `views` int(9) NOT NULL, PRIMARY KEY (`id`) Code (markup): 11086 Rows for `download`, 121344 Rows for `download_cache`. `download_cache` is where I store the IP addresses of users who've downloaded a file. I'm also guessing some of my fields could be "text", but I doubt that makes much of a difference.
Definitely index those columns. See what that does for your query time. You can also use the EXPLAIN function to get a better idea of what the query is doing. http://dev.mysql.com/doc/refman/5.1/en/explain.html Another really good query to run to help figure out the correct column types for the specific table is: SELECT * FROM `download` PROCEDURE ANALYSE() Code (markup):
I'm not sure if adding an index is a good idea. The table is constantly being updated by a bot that runs 24/7. Furthermore, even if this does speed up the query time, will it even have any impact on the performance i.e. use less resources?
The number of rows is small, so I don't think an index will negatively impact performance. If the table is updated a lot, you may want to switch to InnoDB tables instead of MyISAM. Personally, I prefer InnoDB for just about every purpose. I would add the index and see what the time goes to. You can also add a composite index on `download`,`deleted`. This will work when you query only using the `download` column, or when you use both.
jestep, A lot of websites are giving me different information on how to setup an index. Could you tell me exactly how I might do it? Thanks.
You can use a command line to do it. I recommend using a GUI like navicat. It allows you to easily add, modify or drop indexes. http://www.navicat.com/ You don't need any of the modifiers, so if you want to use the command line, just use: CREATE INDEX index_test_1 ON `download` (`download`); If you want to try the composite index, drop the first one, and use: CREATE INDEX index_test_2 ON `download` (`download`,`deleted`);
Indexes will use more disk space but it could potentially increase performance by a tremendous amount under the right circumstances. 4.4 seconds for a query with that amount of data is ridiculous (in a bad way). Are you serving your static content on the same server as your sql server?
Yes I am Antelope. Anyway, adding the index didn't really do much. From what I've read, it's mostly for speeding up queries that have a specific result e.g. searching. My site is still crashing under the heavy hold. I guess no one can help me then?
It speeds up queries that depend on a conditional statement. In your example you have: "WHERE `download` != '' AND `deleted` = 0". So indexing download and deleted would be a good idea to start no matter what. Just like you would want to index a date column if you happen to have an archiving system in place (something you would likely have on a blog for example). Your database schema is also pretty bad and you've only showed us 1 table so god knows how bad the table to table links are (if any). You seem to have a strong affection towards using varchar(999) on most fields when it makes no sense to use such a field type and size. `id` int(9) NOT NULL AUTO_INCREMENT, [b]this is ok[/b] `download` varchar(999) NOT NULL, [b]what type of info is this?[/b] `source` varchar(999) NOT NULL, [b]and what is this?[/b] `link` varchar(999) NOT NULL, [b]a bit but much, but whatever[/b] `date` varchar(22) NOT NULL, [b]why are you using varchar and not timestamp or datetime?[/b] `thumb` varchar(999) NOT NULL, [b]link to a file path or filename? it doesn't need to be so long[/b] `desc` varchar(999) NOT NULL, [b]fine I guess, if you expect most descriptions to be ~1k chars[/b] `deleted` int(11) NOT NULL DEFAULT '0', [b]Is this 0 or 1? Why is it a 11 digit int when it could just be a tinyint?[/b] `views` int(9) NOT NULL, [b]this is fine assuming it tracks the # of hits on the row[/b] PRIMARY KEY (`id`) Code (markup): If you want help you're going to have to post way more information than you currently have. How is it being displayed, what is the frequency of these queries, any relationships?, what context is the data being used in?, etc..
I'd highly recommend innodb tables. The biggest difference is the support of row-level locking. This will help a lot for your situation. And I concur with other posters that indexing those columns is a good idea. On my forum I have 50k visits per day with about a million daily page views. It's a forum and my 5gb database is on a single server. You may need better specs too. That's an old processor and more ram would be good idea. There are also optimization scripts out there to examine parameters. Your my.cnf is really small. And do you have phpmyadmin? Are you running mysql 5.x yet with 64 bit binaries?