fsockopen causing "connection errors"
Hello, First, I tried searching the mysql list archives, but the archive search function on mysql.com does not seem to be working. Second, I'm using the PHP fsockopen() function on port 3306 to test if a database is up and responsive. If the socket opens, I'm assuming the database is up, and I then call fclose() to close the socket. The problem is that if I call this several times in a row quickly, the database senses a "connection error" problem and refuses to connect until I issue a "flush hosts" command. Is there are "more proper" was to use fsockopen()/fclose() to simply ping the database? I do NOT want to use mysql_connect in this case - I just want to ping 3306 without causing MySQL to count them as broken connections and disconnect the client host. Thanks, -Hank __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP/MySQL/Apache interaction problem
I really need some help/direction, so I'll try to explain this as best I can. I have a PHP script that involves three very long running queries (2-3 minutes each). The users of my website kick this off from their webpage, so it runs as "nobody" from inside Apache. In order to prevent a user from running the same script twice, I do a "GET_LOCK" call at the begining of the script, and return a message if it is already running. Everything worked perfectly for the last few years with this method, until last week when my web server was upgraded to a 2.4 kernel, Apache 2 and PHP 4.3.3 (from a 2.2 kernel, Apache 1.3 and PHP 4.1.2). The database server (mysql 3.23.41) is running on a different machine has not changed. Under the new system, the PHP script runs, and fires off the first of the three queries. Exactly 2 minutes into the script running (while the first query is still running), Apache times out and returns a blank page (expected), and I lose the GET_LOCK lock, but the PHP script continues to run, and in fact the other two queries also run and complete under the same mysql connection ID. So I'm not losing the mysql connection, and the PHP script continues to run, so why am I losing the lock? I've run all the queries from the command line, and I can't replicate the problem. Where can I look to find where/why mysql is giving up the lock, when the PHP script continues to run under the same connection ID? (I can understand the problem if the mysql connection dies and the script auto-re-connects, but that isn't happening.). Thanks, -Hank __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Self Join question - large table
Hello, I have a query that will be used alot on my website that involves a 42 million record table being self-joined. The primary table instance will be limited by an index resulting in 1 to about 50,000 rows being selected, then joined to the second instance of the table, which will retrieve one to five or so rows for each record in the source table. This table is totally static and is updated once per day during a maintenance cycle. Here's my question: Does it make sense (from a performance standpoint) to actually make a duplicate of this table so MySQL is joining two identical tables rather than self joining the same table? Thanks, -Hank __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need a quick answer about myisamchk!!!
Very simple question: Can I use myisamchk version 2.6 (from mysql 4.0.15) to fix/repair a table from mysql version 3.23.41 (which includes myisamchk version 1.49)?? The reason is that I have more disk space and processing power on a machine with 4.0.15 installed, but I'd like to rebuild some tables living on an older server/version of mysql (i.e move tables to new machine, repair, move back). There is not enough space on the source machine to do the rebuild there. thanks! -Hank __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT crashing mysql 4.0.14
Environment:
I'm setting up a database server on a Dell Poweredge 2650, dual 1.8GHZ pentium
with 1GB of memory and RAID5 drives. I've installed RedHat 9, and updated the
kernel to 2.4.20-19.9smp. I've installed the RPM binary distribution of MySQL
4.0.14 right from the MySQL website. The only thing I have done is disable
InnoDb in my my.cnf file.
Database Table:
I have a database with 21.4 million records. One field is a VARCHAR(255) field
on which I have created a FULLTEXT index.
Problem:
Most single word full text searches work perfectly and quickly. Occasionally,
when someone enters several words (without any operators), MySQL will crash
with the following in the error log (see below). This is totally consistent,
and MySQL will crash on the same submitted search text each time. Then other,
more complex terms will work. But I can make MySQL crash every time with the
query below in the error log. I've already rebuilt the fulltext index with
myisamchk, and there are no writes to this table at all - it's totally read
only.
I'm not sure what to do next - I don't want to have my database crashing every
hour when someone submits a valid search term that trips up the database.
Any help would be most appreciated.
Thanks,
-Hank
---
030813 12:37:09 mysqld started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.14-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=3
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x84c4408
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbff3e528, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x807474f
0x82a0ad8
0x82ca717
0x82891c3
0x827b037
0x8289956
0x828992c
0x828986b
0x827b2de
0x827946f
0x83328c1
0x80516bd
0x80962be
0x80999fe
0x80a6fed
0x807f46a
0x8082f6b
0x807e5b3
0x80844ee
0x807d79f
0x829e28c
0x82d199a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x84b2668 = SELECT denom,series,serial_number,notes,MATCH (notes)
AGAINST ('\"traded this bill\"') AS score FROM temp_bill_note_search WHERE
MATCH (notes) AGAINST ('\"traded this bill\"' IN BOOLEAN MODE) order by score
desc limit 0, 20
thd->thread_id=214
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
030813 12:48:24 mysqld restarted
---
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT crashing mysql 4.0.14
Thanks for your attention to this problem!
The files are just about 1GB zipped. I'm on a cable modem, so it would take
quite a while to upload these files, but I would be happy to upload them if
helps solve the problem.
Here are the results you requested:
mysql> SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes
AGAINST("traded" IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
|55093 |
+--+
1 row in set (34.02 sec)
mysql> SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes
AGAINST("this" IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
|0 | (a very common stop word most likely in >50% of the notes)
+--+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes
AGAINST("bill" IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 7922619 | (another very common word, but in <50% of the notes)
+--+
1 row in set (9 min 48.39 sec)
By the way, the following count(*) query works WITHOUT crashing the server:
mysql> SELECT COUNT(*) FROM temp_bill_note_search WHERE MATCH notes
AGAINST('"traded this bill"' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 672 |
+--+
1 row in set (8 min 6.58 sec)
But when I then go to query the actual records and score, this SQL crashes the
server:
mysql> SELECT denom,series,serial_number,notes,MATCH (notes) AGAINST ('"traded
this bill"') AS score FROM temp_bill_note_search WHERE MATCH (notes) AGAINST
('"traded this bill"' IN BOOLEAN MODE) ;
CRASH:/usr/bin/mysqld_safe: line 1: 7789 Segmentation fault nohup
/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/lib/mysql/bmw.eskin.com.pid --skip-locking --core-file
>>/var/lib/mysql/logs/error.log 2>&1
But when I leave off the "score" in the SELECT clause, this query works without
crashing the server:
mysql> SELECT denom,series,serial_number FROM temp_bill_note_search WHERE MATCH
(notes) AGAINST ('"traded this bill"' IN BOOLEAN MODE) ;
672 rows returned in set (8 min 13.11 sec)
I hope this helps!!
BTW, I'm not getting any core dump files in the data dir or any other basedir.
If there is any more information you need, or would like to log in to the
server yourself to do tests, let me know via email at [EMAIL PROTECTED]
Thanks!
-Hank
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT crashing mysql 4.0.14
Here is the resolved stack trace when the FULLTEXT query crahses my server: 0x807474f handle_segfault + 423 0x82a0ad8 pthread_sighandler + 184 0x82ca717 memcpy + 39 0x82891c3 tree_insert + 483 0x827b037 walk_and_match + 231 0x8289956 tree_walk_left_root_right + 78 0x828992c tree_walk_left_root_right + 36 0x828986b tree_walk + 43 0x827b2de ft_init_nlq_search + 318 0x827946f ft_init_search + 47 0x83328c1 ft_init_ext__9ha_myisamUiUiPCcUib + 49 0x80516bd init_search__15Item_func_matchb + 489 0x80962be init_ftfuncs__FP3THDb + 94 0x80999fe mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result + 4142 0x80a6fed handle_select__FP3THDP6st_lexP13select_result + 101 0x807f46a mysql_execute_command__Fv + 966 0x8082f6b mysql_parse__FP3THDPcUi + 559 0x807e5b3 dispatch_command__F19enum_server_commandP3THDPcUi + 1471 0x80844ee do_command__FP3THD + 154 0x807d79f handle_one_connection + 635 0x829e28c pthread_start_thread + 220 0x82d199a thread_start + 4 I hope this helps to figure out what the problem is. thanks! -Hank __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: FULLTEXT crashing mysql 4.0.14
Oops.. here's the correct command I used to start mysql: /usr/bin/mysqld_safe --core-file --core-file-size=100 & It was not started with "--core-file=" as mentioned in my previous note. Sorry. -Hank __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT crashing mysql 4.0.14
Sergei wrote: >How big is your table? The MYD file is 2,406,292,556 bytes. The MYI file is 1,381,827,584 bytes. There are 21,402,554 records in the database. This table is static (no writers) and ONLY used for fulltext searching on the "notes" field. Here is the structure: +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | denom | varchar(4) | YES | | NULL| | | series| varchar(5) | YES | | NULL| | | serial_number | varchar(11) | YES | | NULL| | | notes | varchar(255) | YES | MUL | NULL| | +---+--+--+-+-+---+ >Could you please run mysqld with --core-file to get a core dump and >upload it to ftp://support.mysql.com/pub/mysql/secret/ ? I've started mysqld with: /usr/bin/mysqld_safe --core-file= --core-file-size=100 & And I've made it crash (as expected), but for the life of me, I can't find the core file anywhere to send it to you. Nowhere in the documentation does it specify where to find the file or what it's name is. And I've looked all over for it in obvious places and names, but I can't find it. If someone will tell me where to find it, I'll gladly send it along. thanks, -Hank __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple group by taking way too long..
The explain plan isn't anything I wouldn't ordinarily expect - a full table scan: +---+--+---+--+-+--+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+--+---+--+-+--+-+-+ | tbe1 | ALL | NULL | NULL |NULL | NULL | 8190652 | Using temporary | +---+--+---+--+-+--+-+-+ --- Justin Spies <[EMAIL PROTECTED]> wrote: > Henry, > The information you've posted is a good start, can you post the results of > an EXPLAIN command? That would go a long way towards finding a solution. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple group by taking way too long..
I have the following SQL. The source table has 8.1 million rows, and the resulting table will have about 7.9 million rows. I know that's not much of a decrease, but the logic is correct as I've tested it on smaller sets. The problem is that when I run with the full set of 8 million rows, it takes about 2 hours to complete. The source and target tables are all char or tinyint fields (i.e. fixed length records). insert into extract2 select field1,field2,field3,field4, if(right(field1,1)='N',mid(field3,2,1),mid(field3,1,1)) as flag, count(*) as count, val-min(val_cnt) as cnt1, if(max(val)http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max_data_length question...
Hello, While looking at SHOW TABLE STATUS, I noticed that all my dynamic tables seem to have a Max_data_length of 4,294,967,295 bytes. Is this true - that any dynamic tables are limited to 4GB in size? My fixed tables do not show this problem, and report that their Max_data_length is 4+ billion records times their record length (as expected). I'm using mysql version 3.23.41 on Red Hat Linux 7.2. Thanks, -Hank __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - 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: Signal 11 on SHOW PROCESSLIST query
>[EMAIL PROTECTED] wrote: >Henry, can you repeat it with binaries from MySQL site? Unfortunately, no. This database server runs a very high traffic website, so I can't easily (nor do I want to) start changing the mysql version and causing alot of (or any) downtime. Except for this random and minor problem, it is very stable. I also can't repeat the problem, as it just happens unexpectedly (not on every call to "show processlist"). -Hank __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - 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
Signal 11 on SHOW PROCESSLIST query
Hello All, I have mysql 3.23.41 running on a Dell Poweredge 2550 on standard install of Red Hat 7.2 (2.4.9-21smp). This version of mysql came with Red Hat. It's been running my website very smoothly for the last few months, and averages 130 queries per second. Mysql has crashed twice in the last month with the same errors in the error log (see log below). I attempted to resolve the stack dump, but when I do "nm -n libexec/mysqld > /tmp/mysqld.sym" I get a "nm: libexec/mysqld: no symbols" error. The good news is that mysql restarted itself and there seems to be no table corruption (whew!), but I'd still like to figure out why the server crashed on the "SHOW PROCESSLIST" command (which does run once a minute in a cron job). Any pointers or help would be greatly appreciated! Thanks, -Hank Eskin Here is the error log of the last crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=58 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x80b25bf 0x4003aac5 0x4003bb56 0x40038ddc 0x810e76b 0x80b9fab 0x80bb9a5 0x80b7ac3 0x80b7099 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x82b2358 = show processlist thd->thread_id=2144083 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 2144083 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Number of processes running now: 0 020617 17:32:41 mysqld restarted __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - 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
difference between fixed length tables MyISAM vs. ISAM
Hello All, I recently moved my database from mysql 3.22.29 to 3.23.41, and udated all my tables to MyISAM tables. Under ISAM, I could take the filesize of the ISD table and divide by the record length to get the exact number of records returned by "select count(*)" from that table (or vise-versa - it always worked). Now with the MyISAM tables, this does not seem to work - the files are always a little larger than the record count multiplied by the record length. Since these are all fixed length tables, where is the extra space comming from? It's not much - about .1% or between .5 and 1.5 megabytes out of a 20 million record fixed-length table. I don't think it is a deleted record space, since (a) very few records are ever deleted, and new inserts happen every 3 seconds and (b) the overage amount is never divisable by the record length. Is there a forumula that will accurately calculate the size of MyISAM fixed-length files? Is there a new overhead associated with myisam tables? Thanks, -Hank __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com - 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: question about converting isam to myisam tables (shortcut?)
Dave, Thanks for your response. The problem with that, while it is technically one insert statement, mySQL still builds the primary key record-by-record (keycache), rather than sorting the keys and creating the index in one shot, like myisamshk can do. "INSERT..." would take upwards of 24 hours to complete on my table of 18 million records, while myisamchk can do it in about 10 minutes. I know my method works, but I wanted to confirm that it was acceptable, and I wasn't missing anything serious. -Hank > Instead of dropping down to OS cp commands, insert the data from > table TBL_FLAT to TBL_INDX. > > INSERT INTO TBL_INDX SELECT * FROM TBL_FLAT; > > This will be valid in mysql. The insert will be fast since > only one insert statement. Let the list know if this works > for you. Or if your method is valid as well. > > David > > -Original Message- > From: Henry Hank [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 26, 2002 3:37 PM > To: [EMAIL PROTECTED] > Subject: question about converting isam to myisam tables (shotcut!) > > Hello all, > > I'm in the process of building a new mysql db server box to migrate a > database off an existing web server. The webserver is running mysql 3.22.29, > and the new box is running 3.23. The two biggest tables are about 20 million > records and the data and index files are each just over 1GB, and each have a > primary key and three or four non-unique indexes. > > I've copied the data files from the old box to the new, but when I use the > ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the > indexes by keycache. So this is what I'm doing instead, and I'm asking if > there are any problems associated with this approach: > > - the existing ISAM Table is called TBL_ISAM > - Create a new/empty myisam table with no indexes named TBL_FLAT > - Create a new/empty myisam table with all indexes named TBL_INDX > > mysql> insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) > mysql> flush tables; > > the in the OS shell (linux) do the following: > > $ cp TBL_INDX.frm TBL_FLAT.frm > $ cp TNL_INDX.MYI TBL_FLAT.MYI > > then to rebuild all the indexes via sorting, use myisamchk: > > $ myisamchk -r -q TBL_FLAT > > I'm left with a fully populated and indexed TBL_FLAT table, which seems to > work perfectly as the original converted ISAM table. > > An in addition, I did first try "myisamchk -r -k0" to disable the indexes and > do the insert into the TBL_INDX table, but mysql still insists on building > the primary key via the keycache - and it takes way too long. My method allows > me to populate the data file with no indexes, and then fool mysql into > rebuilding all the indexes at once via sorting. > > Does anyone think there are any risks/problems with this approach? > > Thanks in advance! > -Hank > __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ - 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
question about converting isam to myisam tables (shotcut!)
Hello all, I'm in the process of building a new mysql db server box to migrate a database off an existing web server. The webserver is running mysql 3.22.29, and the new box is running 3.23. The two biggest tables are about 20 million records and the data and index files are each just over 1GB, and each have a primary key and three or four non-unique indexes. I've copied the data files from the old box to the new, but when I use the ALTER TABLE...TYPE=MYISAM, it takes forever since it is rebuilding the indexes by keycache. So this is what I'm doing instead, and I'm asking if there are any problems associated with this approach: - the existing ISAM Table is called TBL_ISAM - Create a new/empty myisam table with no indexes named TBL_FLAT - Create a new/empty myisam table with all indexes named TBL_INDX mysql> insert into TBL_FLAT select * from TBL_ISAM; (runs very quickly - not updating any indexes) mysql> flush tables; the in the OS shell (linux) do the following: $ cp TBL_INDX.frm TBL_FLAT.frm $ cp TNL_INDX.MYI TBL_FLAT.MYI then to rebuild all the indexes via sorting, use myisamchk: $ myisamchk -r -q TBL_FLAT I'm left with a fully populated and indexed TBL_FLAT table, which seems to work perfectly as the original converted ISAM table. An in addition, I did first try "myisamchk -r -k0" to disable the indexes and do the insert into the TBL_INDX table, but mysql still insists on building the primary key via the keycache - and it takes way too long. My method allows me to populate the data file with no indexes, and then fool mysql into rebuilding all the indexes at once via sorting. Does anyone think there are any risks/problems with this approach? Thanks in advance! -Hank __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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: major performance disparities with mysql/php - SOLVED
I found the problem. My production server is running mysql 3.22.29, and I needed to use a SET SQL_BIG_TABLES=1 in many places to prevent a "full temp table" condition. The new server is running 3.23, and once I removed the SQL_BIG_TABLES=1 from the script (and allowed mysql to use memory instead), everything worked as expected. -Hank __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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
major performance disparities with mysql/php
Hello Again - I'm posting this again, since it still is an issue that I can not resolve - please help! I've recently installed mySQL on a RH7.2 box without any problems - it runs great. I've been testing some long running queries (full table scans, etc) under different scenarios, and get wildy differing results. Between each test case, I was flushing all tables and re-starting the mysql deamon. When I run one of my longest queries in the mysql command line client, it runs in about 77 seconds. When I run the identical query via PHP running on the box, the same query takes about 930 seconds to complete. For the life of me, I can no figure out why the identical query would run differently from the command line than from PHP. I've repeated this test about 10 times just to be sure - and it is entirely repeatable: command line - about a minute - PHP - about 16 times longer. Any ideas or suggestions? I'm running RH 7.2 (2.4.9-21) on a Dell Poweredge 2550, 1GB memory, RAID, with mysql version 3.23.41 (the standard install unchanged from the RH media). Here is the query...pretty simple: insert into summary_table select frb, denom, series, count(*) as cnt, sum(bills) as bills, sum(bills_hit) as bills_hit, sum(total_hits) as total_hits from detail_table If it a memory/cpu resource problem, how do I set RH to give PHP and Apache the same priority as the mysql deamon? Thanks in advance.. -Hank __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - 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
strange performance problems with mysql
Hello All, I've recently installed mySQL on a RH7.2 box without any problems - it runs great. I've been testing some long running queries (full table scans, etc) under different scenarios, and get wildy differing results. Between each test case, I was flushing all tables and re-starting the mysql deamon. When I run one of my longest queries in the mysql command line client, it runs in about 77 seconds. When I run the identical query via PHP running on the box, the same query takes about 930 seconds to complete. For the life of me, I can no figure out why the identical query would run differently from the command line than from PHP. I've repeated this test about 10 times just to be sure - and it is entirely repeatable: command line - about a minute - PHP - about 16 times longer. Any ideas or suggestions? I'm running RH 7.2 (2.4.9-21) on a Dell Poweredge 2550, 1GB memory, RAID, with mysql version 3.23.41 (the standard install unchanged from the RH media). Here is the query...pretty simple: insert into summary_table select frb, denom, series, count(*) as cnt, sum(bills) as bills, sum(bills_hit) as bills_hit, sum(total_hits) as total_hits from detail_table Thanks in advance.. -Hank __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ - 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: question about myisamchk
>> Get some more info at: http://www.mysql.com/doc/I/n/Insert_speed.html That suggests using LOAD DATA INFILE, but I would really like to avoid dumping a very large ISAM mysql table to a text file, just to load it back into another table (MYISAM). I was looking for answers as to why isamchk works as documented and expected for ISAM tables when using --keys-used=0, and myisamchk does not do the same thing for MYISAM tables! -Hank __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - 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
question about myisamchk
Hello, Under mysql 3.22.29, I was able to use isamchk to disable all index keys (-r -k0), do millions of inserts into the table, then re-enable all the indexes again with "isamchk -r -k4" (4 indexes). I'm testing out a new Redhat 7.2 Linux server with mysql 3.23.41, and am attempting to load a table with 30 million records, then re-enable the indexes. So, I use: > myisamchk -r -k0 new_table - recovering (with keycache) MyISAM-table 'new_table.MYI' Data records: 0 I then use an : insert into new_table from old_table; To load the table, but mysql still attempts to build the index file (MYI), greatly slowing down the table load. How can I entirely disable all indexes for a table, load it, then re-enable? Like I said, this is how it used to work under isamchk under 3.22.29. BTW, I am aware that myisamchk uses a bitmap to re-enable the indexes, but I haven't gotten to that point, since the indexes are being built upon the data load. Thanks, -Hank __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - 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
quick questions about redhat and mysql
Hello, I am trying to decide which OS to use for a dedicated mysql server for my website. I need filesize >2GB support. I really wanted to use Solaris, but there are no drivers for my RAID adapter (long story). I was considering NetBSD, but I don't know it well. I was then back to thinking about Linux. So, I can't keep all the OS/kernel/requirements in my head... Can RedHat 7.1/7.2, out of the box (ie. no patches, kernel re-compiles, etc), support files >2GB? Will I be able to easily re-compile mysql to support large files? Should I stick with the version of mysql on the RH disto, or download/install the latest stable version? Can someone comment on the stability of mysql on redhat 7.1/7.2? Thanks! -Hank __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - 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
NetBSD versus RedHat versus Solaris x86
Hello all, I just ordered a new server to act as a database-only server for my website (wheresgeorge.com). I'm currently running mysql 3.22.29 (I'm stuck there due to my web hosting provider). I plan to upgrade to the latest stable version of mysql, but have the following questions about the choice of Operating System. The hardware is a Dell PowerEdge 2550 Dual 1GHZ Pentium/1GB Memory, 3x18GB SCSI disks and will be set up as RAID 5 via a PERC3/Di card. I also require large file size support (>2GB). My current database has over 16 million records and processes about 80 queries per second (on average). Peak usage could be around 300 queries/second (estimate). It is running on Linux on a dual PIII-800. Performance is very good, but I'm going to hit the 2GB limit soon, and need to expand beyond what my ISP can provide with their managed solutions. So I am going to co-locate this new DB server as a backend to the current webserver. On to the questions: I was considering Net/FreeBSD, but read about threading problems in the archives, so I don't know if they have been resolved. I was then thinking about RedHat7.2 with the 2.4 kernel (large file size support, I think). I then read about Solaris x86.. which is where I'm leaning, but I was told Solaris x86 might be slower than Linux of Free/NetBSD. I'm also thinking of installing Oracle 8i as a side-by-side comparison to mySQL running on the same HW/OS. Anyway, I'm not a UNIX whiz/admin by any stretch... I know enough to *NIX to manipulate files, jobs, processes, etc. I've even compiled a few packages in the past and got PHP/mysql installed and running on a linux box last year. I can write rudimentary shell scripts. I'm looking for general comments regarding the current status of any of these choices of OS on this hardware in relation to running a fast and stable mySQL installation. Are there any pitfalls I need to watch out for on any one OS? Is any one significantly more favorable for mysql that the other? As I've said, I have searched the archives, but can not find any real-current comments on OS selection. Thanks in advance, -Hank Eskin ([EMAIL PROTECTED]) __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com - 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
Contracting opportunities?
Hello All, I'm recently back in the job market, and looking for PHP/MySQL contracting opportunities. Can some helpful people out there please email with good job sites and/or real headhunters that specialize in this type of work? I've already exhausted dice.com, guru.com, monster.com, headhunter.net, and a few other smaller ones. The headhunters I've dealt with seem to be pretty void on demand for these skills. I'm not asking the list for any opportunities themselves, hence I'm not posting my credentials, but I'd rather get pointers in the right direction. Thanks in advance, -Hank ([EMAIL PROTECTED]) __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text - 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
