Re: practical MySQL database size limits
our database is about 20 Gb and growing daily. so far, I still see nearly constant time query performance on tables with ~10M rows. I don't think mysql is limited by file size per se. I guess performance depends a lot on what your tables look like, and your hardware, obviously. From my own experience MySQL (MyISAM) scales very well up to 20M rows, but 100M rows turned out to be too much... -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't set myisam_repair_threads
From http://www.mysql.com/doc/en/News-4.0.13.html: Added multi-threaded MyISAM repair optimisation and myisam_repair_threads variable to enable it. The variable is also documented in http://www.mysql.com/doc/en/SHOW_VARIABLES.html: If this value is greater than one, MyISAM table indexes during Repair by sorting process will be created in parallel - each index in its own thread. Unfortunately this variable is neither listed by SHOW VARIABLES, nor can I set it: mysql set myisam_repair_threads=2; ERROR 1193: Unknown system variable 'myisam_repair_threads' I'm using 4.1.0-alpha-standard. Am I doing something wrong, or was this variable dropped? If so, perhaps the documentation should be updated... -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam_max_extra_sort_file_size
From the documentation: If the temporary file used for fast index creation would be bigger than using the key cache by the amount specified here, then prefer the key cache method. Did I understand correctly that if I always want fast index creation whenever myisam_max_sort_file_size is set large enough, this variable must be set to 0? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam_max_sort_file_size not set
In my.cnf: [mysqld] key_buffer_size=64M myisam_sort_buffer_size=512MB myisam_max_sort_file_size=100 myisam_max_extra_sort_file_size=0 bulk_insert_buffer_size=64MB ... All variables are set as expected, except myisam_max_sort_file_size, which is set to 4GB. Only if I choose a value smaller than 4GB, or manually execute set global myisam_max_sort_file_size=100; does the variable get updated. Is there any reasoning behind this behavior, or is this simply a bug? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't force Repair by sort
Is there any way to force a Repair by sort rather than a (way to slow) Repair with keycache when doing an ALTER TABLE t ENABLE KEYS? The table in question is 6GB large and contains 100M rows. I have set myisam_sort_buffer_size=512MB myisam_max_sort_file_size=1MB myisam_max_extra_sort_file_size=0 bulk_insert_buffer_size=64MB but this doesn't help. I am running somewhat low on diskspace with only a few gigabytes free, could this have any influence here? Any other ideas? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA hangs
But myisam_sort_buffer is, AFAIK, only used during Repair by sort (in PROCESSLIST), not Repair with keycache. What did yours say during the 21 hours? It may change from sort to keycache after awhile -- something to do with the size of the indexes or something. Not exactly sure about the criteria. Yes, it is indeed using Repair with keycache. This change of strategy would explain why indexing the complete data set takes 40x longer than indexing a 20% subset. I'll try further increasing myisam_sort_buffer_size to 512MB. Also found two further options that may be relevant, myisam_max_sort_file_size and myisam_repair_threads, which I will increase to 10GB and 2, respectively. No idea about InnoDB, but I would guess that it'd be slower? Does DISBALE/ENABLE KEYS even do anything with InnoDB, or just MyISAM? Hmm. I just checked, and it seems it doesn't do anything. (For InnoDB tables, the query plan still shows type=ref rather than type=ALL after DISABLE KEYS.) -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g Try surrounding the value with quotes, i.e.: explain SELECT * FROM campaign_t WHERE datestamp '20041105' -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA hangs
4) ALTER TABLE ENABLE KEYS - Puts a full load on the CPU, but neither index nor table size seem to change, even after waiting for an hour. Based on smaller data sets, I'd expect the index to reach something close to 4 GB. Update: After several hours, the index file started growing, although very slowly. After another 21 hours or so the indexes were complete. The final index file size is 4.43 GB. I currently have the following options set: key_buffer_size=64M myisam_sort_buffer_size=64MB bulk_insert_buffer_size=64MB Any suggestions what else could be tried in order to get MySQL to scale? Any important options I overlooked? Might InnoDB be faster? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA hangs
Any idea what has gone wrong here? Yet another limitation I have run into? Tried this: 1) CREATE TABLE 2) ALTER TABLE DISABLE KEYS 3) LOAD DATA - Complete after 30 min, table size 5.78 GB, index size 1.21 GB. 4) ALTER TABLE ENABLE KEYS - Puts a full load on the CPU, but neither index nor table size seem to change, even after waiting for an hour. Based on smaller data sets, I'd expect the index to reach something close to 4 GB. I'd appreciate any suggestions. Do I need to allocate more memory? Which parameters? Surely I'm not the first to store more than a gigabyte of data into a table with a few indexes :-) -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA hangs
I have a MyISAM table, into which I am trying to bulk-load several gigabytes of data. The first attempt got stuck after the table.MYD file had reached precisely 4 GB. There wasn't any error message, but I observed that neither the data nor the index file was increasing in size, even though the CPU load was at 100%. Anyways, this was solved by setting the following table properties (and killing the database server + deleting the table): MAX_ROWS = 1 AVG_ROW_LENGTH = 300 Now I have again run into the same issue, but at 5.78/1.21 GB (table/index size). For a while the table size remained constant while the index size still increased, but now nothing seems to be happing any more, while the CPU is 100% busy. I would have expected the index to reach a final size of something around 4 GB. Any idea what has gone wrong here? Yet another limitation I have run into? -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
