Table size limitations...
I have a table that holds a lot of information. I tried to INSERT something into it, and received... DBD::mysql::st execute failed: The table 'fancy_big_table' is full at ./tom_to_mutt.pl line 156. The table is 4G is size. The table's Max_data_length = 4294967295 (4G) I'm running the MySQL server on a FreeBSD system, and this is not a file system limitation. I am using MyISAM tables. So, I'm not sure what to do. I could easily double the size of that table in the next few months. After reading the CREATE TABLE page in the manual, I thought that increasing the AVG_ROW_LENGTH would help since it says that... MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be I ran ALTER TABLE fancy_big_table AVG_ROW_LENGTH = 9482; and after a long time, it finished without error, but inspecting the Avg_row_length and the Max_data_length afterwards showed no change. Maybe this is a red herring, and totally down the wrong path. I'm not sure. So, if one of you could help me figure out how to cram more crap into my table, I'd be awefully appreciative. Thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table size limitations...
On Wednesday 15 August 2001 11:30, Dan Nelson wrote: In the last episode (Aug 15), Nathanial Hendler said: I have a table that holds a lot of information. I tried to INSERT something into it, and received... DBD::mysql::st execute failed: The table 'fancy_big_table' is full at ./tom_to_mutt.pl line 156. The table is 4G is size. The table's Max_data_length = 4294967295 (4G) I'm running the MySQL server on a FreeBSD system, and this is not a file system limitation. I am using MyISAM tables. I wonder if there is still a 4gb limit on dynamic MYISAM tables? Is there a 4-byte offset used as the pointer in indexes for this table type? I checked some of my databases and all the dynamic tables have 4294967295 as their max length, where the fixed tables vary; one has 115964116991. The docs do mention MAX_ROWS and AVG_ROW_LENGTH together; maybe you need to specify both of them for mysql to bump your table limits above 4gb. If that doesn't work, and you can spare the wasted space, try converting your table to fixed-length rows (convert all varchars to chars). I had a similar thought regarding the dynamic tables, since I saw the same numbers. I can spare the wasted space, but the column making it dynamic is of the type text, so I can't just make it fixed-length (right?). Any ideas? I'm trying ALTER TABLE resumes MAX_ROWS = 1; right now (sugggested by ryan) I hope that works. thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl Script: MySQL Slow Query Log Parser
I wrote a perl script that will parse slow_queries logs, and output some useful information. It's kind of hard to explain, but I'll try. I wanted to see what queries were taking a lot of time, and how often they were happening. I wrote a perl script that parses the log files, ignores queries that take less than n seconds, and normalizes the queries and reports the info for each queries sorted by query occurance. 'normalize' meaning... this... SELECT * FROM ween WHERE pandy_fackler = 1; SELECT * FROM ween WHERE pandy_fackler = 15; becomes... SELECT * FROM ween WHERE pandy_fackler = XXX; this... SELECT names FROM things WHERE name LIKE '%wazoo%'; SELECT names FROM things WHERE name LIKE '%tada%'; becomes... SELECT names FROM things WHERE name LIKE 'XXX'; This has proven to be a very interesting and useful tool. You should DL it and try it on your long_queries log file. Whoever runs mysql.com should put a copy on the website. It might just be the greatest thing ever (my program, not the website). For more info, and to get a copy, you can get it at: http://www.retards.org/mysql/index.php I'd like to hear people's thought on it. I'm the only person to test it so far, so it'd be nice to know that it works for other people. Thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query help...
Hello, I have a query that I can't figure out how to do. I would be really happy if some SQL experts could give me a little advice on how to do this I have two tables. I believe that the tables can be described as having a one to many relationship. Below are the columns in each table, relevant to the query... Table: assessments --- assessment_id int() primary key sent_status tinyint() Table: assessment_hits --- assessment_id int() hit_date timestamp(14) The assessment_id column in assessment_hits doesn't have to be unique, so the table has many rows with the same assessment_id and different hit_date values. What I want to find out is, how many assessments.assessments_ids with a sent_status =1 there are for each month. Here's what I did that didn't work... SELECT DATE_FORMAT(h.hit_date, '%Y %M') as month, count(*) as total FROM assessment_hits h, assessments a WHERE a.assessment_id = h.assessment_id AND a.sent_status = 1 GROUP BY LEFT(h.hit_date, 6) This gives me how many assessment_ids with a sent status = 1 there are in the assessment_hits for each month. How do I just use one hit_date from assessment_hits for each assessments.assessment_id with a sent_status=1? I don't care which hit_date it uses, preferably the most recent one, but it doesn't matter. I'm thinking that I have to use a special join, but I don't know. Thanks, Nathanial Hendler Tucson AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How many queries per second do you get?
Hello, I was just looking at my MySQL databse using mytop and saw that I was getting over 60 queries per second. Can people give me an idea of how many queries per second they get? Thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to tell when I need more memory?
How does one know if their database server needs more memory? I have several tables around 600,000+ rows that are being joined. I have 512MB of memory. Is there a rule of thumb, or a good way to calculate how much memory my database server needs. Can I run my bigest query through some sort of benchmark to see how much memory it consumes? Thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using identical column names in seperate tables
From the MySQL Manual (13.6 Other Optimization Tips) http://www.mysql.com/doc/T/i/Tips.html Columns with identical information in different tables should be declared identical and have identical names. Before Version 3.23 you got slow joins otherwise. Try to keep the names simple (use name instead of customer_name in the customer table). To make your names portable to other SQL servers you should keep them shorter than 18 characters. I'm a little confused about this statement. I'm reading two possible tips, and I want to make sure I understand... How does keeping names 'simple' optimize my database? How does naming columns with identical information in different table the same optimize my database? Thanks, Nathan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
