ERROR 2014 - command out of sync, why?
Database error: cannot use database pollo MySQL Error: 2014 (Commands out of sync; You can't run this command now) Session halted. Why? - 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
Wont use Index when data is not evenly distributed
Cant figure out why MySQL wont use index on a big table. Ok, the data is not evenly distributed which might be the problem. Look here: I have a table with 1 million records, with the following fields: IdUser, int X, int Y, int Z, int C, char(10) no, varchars, text, or blobs. IdUser is a user identity. As things are right now, only 3 users are registered. iduser=2, 34, 39 User 39 owns 99.999 % of the data in the table. When using EXPLAIN, Mysql tells me that when querying the table with IdUser=39, MySQL will not use index. Querying the table with all other idusers than 39, causes MySQL to use index. Has anyone of you seen this behavious before? /Claus - 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: Wont use Index when data is not evenly distributed
Just added more indexes and found a good one. thanx for the advise ! =) /C - Original Message - From: Claus Reestrup [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 29, 2002 2:28 PM Subject: Wont use Index when data is not evenly distributed Cant figure out why MySQL wont use index on a big table. Ok, the data is not evenly distributed which might be the problem. Look here: I have a table with 1 million records, with the following fields: IdUser, int X, int Y, int Z, int C, char(10) no, varchars, text, or blobs. IdUser is a user identity. As things are right now, only 3 users are registered. iduser=2, 34, 39 User 39 owns 99.999 % of the data in the table. When using EXPLAIN, Mysql tells me that when querying the table with IdUser=39, MySQL will not use index. Querying the table with all other idusers than 39, causes MySQL to use index. Has anyone of you seen this behavious before? /Claus - 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 - 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
DBI: error 12 from table handler , Can't allocate memory, temp tables, WHY?
Hi. I have a perl script that uses many temporary tables (HEAP mostly) and when running the script many times in parallel some of the script instances fail. The query uses temporary tables and recently I discovered that some of the queries uses filesort (disk access) due to GROUP BY making the response time for every concurrent script longer than expected. Here is the error I get: DBD::mysql::db do failed: Got error 12 from table handler at /home/travis/tr.pl ... Looking at the log files for MySQL I cant figure out what queries cause the failure but generally speaking I assume that the problem is the temporary tables. Looking up the error code received by DBI/DBD I get: 'perror 12' Error code 12: Cannot allocate memory Can't allocate memory? Why is that? Below is my server info and my default my-huge.cnf settings. I have tried to set the SET SQL_BIG_RESULT=1 resulting in all temp tables getting written to disk, but the error still exists. I have tried to modify the parameters in my.cnf, primarily adjusting key_buffer and sort_buffer, but the default my-huge.cnf configuration seems to work the best. Could someone please tell me what is wrong? SERVER INFO: - * MySQL v 3.23.52 on FreeBSD 4.7, dual 1200 MHz Intel CPU, 1Gb RAM, 10Gb Free swap space * and lots of free space on all partitions, especially in /var * no limits on CPU, Memory, open files etc. in FreeBSD kernel * No errors shown in /var/messages * No errors shown in /var/db/mysql/host.err * The queries involve the use of temporary tables and a lot of READ queries on regular tables. * No locks have been issued and no locks are needed. * No other processes on the server uses MySQL while the program runs. MySQL info: - Compiled with static, no innodb, no BDB for full performance /etc/my.cnf - copied 100% from my-huge.cnf --- [client] #password = your_password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-innodb skip-bdb set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 #set-variable = join_buffer_size=512000 set-variable= myisam_sort_buffer_size=64M #log-bin server-id = 1 - 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
Concurrent queries 'wait for each other'
Hi! I have a problem with MySQL that hangs when concurrent queries run. The symptom is this: A) A Perl program that performs several queries using temporary tables takes 8 seconds to run. CPU utilisation around 30% (using the 'top' command) B) Running two instances of the same perl program in parallel causes each program to wait for each other, doubling the response time for each program, that is each program is now 16 seconds to finish. CPU utilisation around 40% (using the 'top' command) Lets call them process 1 and process 2, each started at the same time. They both run for 16 seconds and they both finish at the same time. C) Doubling the amount of processes again, running 4 in parallel, shows the same symptom: They are all started at the same time, and they all return results at the same time. Each process is now 32 seconds to finish CPU utilisation around 60% (using the 'top' command) etc. etc. This shows that all processes seem to wait for each other to finish before returning a result instead of returning the results immediately! Has anyone solved this problem before? SERVER INFO: - * MySQL v 3.23.52 on FreeBSD 4.7, dual 1200 MHz Intel CPU, 1Gb RAM, 10Gb Free swap space * and lots of free space on all partitions, especially in /var * no limits on CPU, Memory, open files etc. in FreeBSD kernel * No errors shown in /var/messages * No errors shown in /var/db/mysql/host.err * The queries involve the use of temporary tables and a lot of READ queries on regular tables. * No locks have been issued and no locks are needed. * No other processes on the server uses MySQL while the program runs. /etc/my.cnf: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-innodb set-variable= max_tmp_tables=1024 set-variable= tmp_table_size=128M set-variable= key_buffer=384M set-variable= max_heap_table_size=128M set-variable= max_allowed_packet=16M set-variable= join_buffer_size=4M set-variable= long_query_time=3 set-variable= table_cache=4096 set-variable= sort_buffer=32M set-variable= record_buffer=16M set-variable= thread_cache=16 set-variable= max_connections=300 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M server-id = 1 # Point the following paths to different dedicated disks tmpdir = /home0/mysqltmp/ - 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
Fw: Concurrent queries 'wait for each other'
- Original Message - From: gerald_clark [EMAIL PROTECTED] Wrote: Perhaps each process simply requires 8 seconds of disk access. Since they share the same disk it will take 8sec. times the number of processes running to complete. You have a strange definition of hang. Claus Reestrup wrote: Hi! I have a problem with MySQL that hangs when concurrent queries run. The symptom is this: A) A Perl program that performs several queries using temporary tables takes 8 seconds to run. CPU utilisation around 30% (using the 'top' command) B) Running two instances of the same perl program in parallel causes each program to wait for each other, doubling the response time for each program, that is each program is now 16 seconds to finish. CPU utilisation around 40% (using the 'top' command) Lets call them process 1 and process 2, each started at the same time. They both run for 16 seconds and they both finish at the same time. C) Doubling the amount of processes again, running 4 in parallel, shows the same symptom: They are all started at the same time, and they all return results at the same time. Each process is now 32 seconds to finish CPU utilisation around 60% (using the 'top' command) etc. etc. This shows that all processes seem to wait for each other to finish before returning a result instead of returning the results immediately! Has anyone solved this problem before? SERVER INFO: - * MySQL v 3.23.52 on FreeBSD 4.7, dual 1200 MHz Intel CPU, 1Gb RAM, 10Gb Free swap space * and lots of free space on all partitions, especially in /var * no limits on CPU, Memory, open files etc. in FreeBSD kernel * No errors shown in /var/messages * No errors shown in /var/db/mysql/host.err * The queries involve the use of temporary tables and a lot of READ queries on regular tables. * No locks have been issued and no locks are needed. * No other processes on the server uses MySQL while the program runs. /etc/my.cnf: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-innodb set-variable= max_tmp_tables=1024 set-variable= tmp_table_size=128M set-variable= key_buffer=384M set-variable= max_heap_table_size=128M set-variable= max_allowed_packet=16M set-variable= join_buffer_size=4M set-variable= long_query_time=3 set-variable= table_cache=4096 set-variable= sort_buffer=32M set-variable= record_buffer=16M set-variable= thread_cache=16 set-variable= max_connections=300 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M server-id = 1 # Point the following paths to different dedicated disks tmpdir = /home0/mysqltmp/ - 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 - 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
Table locking questions
Hi everyone! I'm fairly familiar with the table locking methods in MySQL but I have a problem I can't solve. I have two main tables which are read simultaneously by many clients. The clients only do reads, but they do it very extensively and for 30 secs each. The two main tables needs updates every 30-45 minutes. The process that updates the tables lasts about 50 secs (several queries, updates and inserts). All my tables are properly indexed using EXPLAIN and all the tables are MyISAM types. I'm using MySQL v3.23.47 All the processes are background processes. The problem: Doing a write lock on the two main tables ensures that no clients can access the tables while they are updated, but what about the clients who are in the middle of a chain of queries from the main tables? Solutions? A) The process that updates the main tables can maintain a version number which increases every time the tables are updated. The process WRITE locks the main tables and thus freezez the many clients reading from them. A client reads the version number from the tables before it starts to do its many queries. When the client is finished processing the tables, it reads the version numbers again and compares them. If they are not the same, the process must have been freezed and must be restarted again. The solution is fairly simple to implement, but the problem is the many client programs taking up resources while they are frozen. Any suggestions? - 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
