MySQL Forums
Forum List  »  Optimizer & Parser

Re: Max Number of Indexes?
Posted by: Conner Hewitt
Date: January 29, 2011 10:38PM

Thanks so much for the response, it got me looking into a number of other issues with our current set up.

Here is the output from the SHOW statements; I have obfuscated some column names of the table:


--------------------------------------------------------------

SHOW CREATE TABLE table

CREATE TABLE `table` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `datetime_c` datetime NOT NULL,
 `c1_id` int(10) NOT NULL,
 `c2_id` int(10) NOT NULL,
 `c3_id` int(10) NOT NULL,
 `c4_id` int(10) NOT NULL,
 `ip` tinytext NOT NULL,
 `char_c` char(2) NOT NULL,
 `c5_id` tinytext NOT NULL,
 `url` text NOT NULL,
 `bool_c` int(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `datetime_c` (`datetime`),
 KEY `c3_id` (`c3_id`),
 KEY `ip` (`ip`(15))
) ENGINE=InnoDB AUTO_INCREMENT=6851191 DEFAULT CHARSET=utf8

SHOW TABLE STATUS LIKE 'table'

Name 	Engine 	Version Row_format 	Rows 		Avg_row_length 	Data_length 	Max_data_length Index_length 	Data_free 	Auto_increment
table 	InnoDB 	10 	Compact 	6619306 	134 		890241024 	0 		529907712 	1984954368 	6848631 

[CONTINUED]

Create_time 		Update_time 	Check_time 	Collation 		Checksum 	Create_options 	Comment
2011-01-22 00:45:02 	NULL 		NULL 		utf8_general_ci 	NULL

SHOW VARIABLES LIKE '%buffer%'

Variable_name 			Value
bulk_insert_buffer_size 	8388608
innodb_buffer_pool_size 	8388608
innodb_log_buffer_size 		1048576
join_buffer_size 		4194304
key_buffer_size 		134217728
myisam_sort_buffer_size 	67108864
net_buffer_length 		16384
preload_buffer_size 		32768
read_buffer_size 		8388608
read_rnd_buffer_size 		1048576
sort_buffer_size 		2097152
sql_buffer_result 		OFF

MySQL Version: 5.1.54-log

--------------------------------------------------------------


You are 100% right about the query being cached. I thought I had the query cache disabled however after testing it, the query on the test server took around 45 seconds, then on next submit only 7 seconds. This, however, still makes me think it'd be a vast improvement if implemented on the production server, as running the same query with no cache and the same table as posted above, it takes a good 11 minutes to complete! (compared to 1 minute 14 seconds on the production server, still without indexes and no cache).

The production server is just a single server with 12GB of RAM, and the main reason that was chosen is because I wanted the entire database to fit into memory. It's maintained by a managed dedicated hosting provider who I believe optimized the settings when we first started with them, however this was when our entire database consisted of MyISAM tables rather than InnoDB. From the looks of it, InnoDB is not optimized as of right now to take advantage of the 12GB of RAM.

Some questions if you don't mind :) :

1. Thinking of the size of the table, would changing the 'ip' column from a TINYTEXT to a VARCHAR(15) show any significant reduction in the size of the table? This column NEEDS to be indexed, and from what I've read (please correct me if I'm wrong), having an index on a prefix of 15 characters on a TINYTEXT causes MySQL to pull the entire row rather than just using the index directly as VARCHAR(15) would. Would the performance difference be noticeable?

2. Same as question #1, but for the 'url' column. I've read that Apache has a max URL length of 8,192 bytes, but usually gives errors around 4,000. Would it be better to change the 'url' column from a TEXT to a VARCHAR(8192)? This column is not used in any WHERE/GROUP BY/ORDER BY clauses, so as far as I know this DOES NOT need to be indexed.

3. Are the TINYTEXT and TEXT columns able to be fully placed into RAM?

4. After looking at the SHOW TABLE STATUS result, the 'Data_free' column is scaring me. Does this mean that this table only has roughly 1.85GB of space left? If so, is there a way to modify this and a couple of other tables so it can store more? This table is going to get large (currently at ~7 million rows, looking at 100 million rows or more).

5. What would be your recommended settings for the MySQL and InnoDB variables? The server is a Core i7 930, has 12GB of RAM and 2 74GB 15k SAS drives in RAID 1, and is running both Apache and MySQL.

6. We have a lot of temporary tables being created, as well as a lot of JOIN queries between this table and another one about 1/10th the size in both row count and actual size (pulling data in the same ratio). Should the tmp table and/or the join_buffer_size variables be modified? I can post the tmp table vars if needed.

7. Would the performance increase that comes with having the entire database in RAM be so much so that more complicated, composite indexes would become insignificant?

8. Just making sure, if the table is stored entirely within RAM, writes will still be performed on disk right?

9. Last question, I swear! :) If the entire database is able to be stored in RAM and performance just increases drastically, would there be any issue with getting a server that supports say 64GB of RAM so the database will always fit as it grows?

Thanks again, your help is greatly appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
6399
January 26, 2011 02:07PM
2123
January 27, 2011 11:13AM
Re: Max Number of Indexes?
1783
January 29, 2011 10:38PM
2529
February 02, 2011 09:46AM
1572
February 08, 2011 10:41AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.