Re: How to LOCK all the database??
Hello colleagues. Hi, How can I global lock a database? Global means that no user/process should be able to access it; I want to keep the database in read only for a certain period, NOT ONLY for the current session. Have a look to Grant and User Privileges. You can create a read only user, put him only select priv, and then use this user for read only ... Or modify an existing user to only set select privilege to him, do you work, and after give him back write access ... http://dev.mysql.com/doc/mysql/en/GRANT.html Hope this help. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation de mysql
Hi, j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance Please refer to the INSTALL file located into the mysql-3.23.58-pc-linux-i686 folder, all is described there. ./configure --prefix=/usr/local/mysql is not needed for binary distrib. PS : this is an english list, so talk english please or do not expect any answer ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can MySql run over windows os?
Hi, Have a look here : http://www.mysql.com/doc/en/Windows_installation.html Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimise SELECT ... LIMIT
Hi list, I'm trying to optimise a SELECT ... LIMIT query, perhaps i miss something important and so, i'm asking your help :) I'm using MySQL 4.0.15 under Linux. Here's a test query : mysql explain SELECT * FROM F4000 WHERE ReplyTo=8711465 ORDER BY Numero LIMIT 234599,20; +---+--+++-+---+ +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+++-+---+ +-+ | F4000 | ref | ReplyTo_Numero | ReplyTo_Numero | 4 | const | 188063 | Using where | +---+--+++-+---+ +-+ And here's index description : mysql show index from F4000; | F4000 | 0 | PRIMARY| 1 | Numero | A | 2535091 | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 1 | ReplyTo | A |NULL | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 2 | Numero | A |NULL | NULL | NULL | | BTREE ReplyTo and Numero are both integer. This query took around 1 sec, could i made something to have better performance ? PS : I know that Richard Davey have post a question on limit, 2 two days ago, but i didn't find anything that can help me. I also look at http://www.mysql.com/doc/en/LIMIT_optimisation.html without any success. Thanks. David Any Idea ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimise SELECT ... LIMIT
Hi list, I'm trying to optimise a SELECT ... LIMIT query, perhaps i miss something important and so, i'm asking your help :) I'm using MySQL 4.0.15 under Linux. Here's a test query : mysql explain SELECT * FROM F4000 WHERE ReplyTo=8711465 ORDER BY Numero LIMIT 234599,20; +---+--+++-+---+ +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+++-+---+ +-+ | F4000 | ref | ReplyTo_Numero | ReplyTo_Numero | 4 | const | 188063 | Using where | +---+--+++-+---+ +-+ And here's index description : mysql show index from F4000; | F4000 | 0 | PRIMARY| 1 | Numero | A | 2535091 | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 1 | ReplyTo | A |NULL | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 2 | Numero | A |NULL | NULL | NULL | | BTREE ReplyTo and Numero are both integer. This query took around 1 sec, could i made something to have better performance ? PS : I know that Richard Davey have post a question on limit, 2 two days ago, but i didn't find anything that can help me. I also look at http://www.mysql.com/doc/en/LIMIT_optimisation.html without any success. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French characters ok with mysql in Windows, nok with mysql in Linux
From: Willy Dockx [EMAIL PROTECTED] Sent: Monday, January 05, 2004 7:23 PM Subject: RE: French characters ok with mysql in Windows, nok with mysql in Linux Hello, etc/sysconfig/i18n contains: LANG=en_US.UTF-8 SUPPORTED=nl_BE.UTF-8:nl_BE:nl:en_US.UTF-8:en_US:en SYSFONT=latarcyrheb-sun16 Is that ok? I can't remenber what was my conf, but google is you friend ... http://groups.google.fr/groups?hl=frie=UTF-8oe=UTF-8q=french+redhat+i18nsa=Ntab=wgmeta= http://groups.google.fr/groups?hl=frlr=ie=UTF-8oe=UTF-8q=accent+redhat+i18n Did you try to connect directly to MySQL through a standard MySQL client and make an insert with é à ù ... and see if the select is ok after the insert ? What concerns the 'driver connection url' : should I leave 'useUnicode=truecharacterEncoding=UTF-8' in it? Don't know, i'm not using java ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French characters ok with mysql in Windows, nok with mysql in Linux
From: Willy Dockx [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 05, 2004 3:00 PM Subject: French characters ok with mysql in Windows, nok with mysql in Linux Hello, I already posted this problem end of 2003, but probably, the champagne has troubled the answers. The solution of this problem is really important for me. Can anybody help? I have made a website using jboss, Hibernate, mysql 4.0.16 and mysql-connector-java-3.0.9. In development this is installed on Windows2000 and everything works fine, also when the user inputs characters like é, à, è, . In production this is installed on Linux (RedHat 8). There also everything works fine, except for the strange characters é, à, è. I suppose the reason is that RedHat uses UTF-8 as encoding. I've tried to put 'useUnicode=truecharacterEncoding=UTF-8' in the driver connection url, but this doesn't help. Can anyone help me on this problem? Hi, I'm storing french characters with MySQL under Linux RedHat. Hox did you export your data to Linux ? Did you look to the file /etc/sysconfig/i18n ? The default file is quite buggy under RedHat 8. Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delayed Insert Question
Hi list, I've got a small MyISAM table which is used for some statistics. I'm only doing insert into this table. I need that clients doing INSERT queries can exit as soon as possible. So, i'm using INSERT DELAYED with some good succes. But I've got a question. If i decrease delayed_insert_limit to ten secondes for example, is that mean that delayed_queue will be flushed every ten secondes ? Is there an other variable that specify the flush time ? For the moment, MySQL can handle an average of 92.12 query/sec. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
So, i'm using INSERT DELAYED with some good succes. But I've got a question. If i decrease delayed_insert_limit to ten secondes for example, is that mean that delayed_queue will be flushed every ten secondes ? Is there an other variable that specify the flush time ? No - delayed_insert_limit refers to how many rows a DELAYED thread will insert at once before checking if any other SELECTs are waiting for the table. The process (and all related variables you can tweak) are documented here: http://www.mysql.com/doc/en/INSERT_DELAYED.html Thanks Chris, I've read mysql doc sereval times, but i can't find any varaible that specify when the delayed queue was flushed. If I understand, I can increase delayed_insert_limit for better performance, but I should also increase the delayed_queue as well ? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delayed Insert Question
Tuesday, December 09, 2003 2:51 PM Chris Elsworth wrote: If you increase delayed_insert_limit then you're effectively giving the DELAYED thread more preferencee to the table; it will write more rows (once it can, ie there's a phase of time where there's no locks on the table) in a batch, which potentially makes other selects wait longer. Inserting delayed_queue_size means the clients can pile more and more rows into the DELAYED thread while it gets chance to write. This may give your clients a bit of a boost, but only if the DELAYED thread fills up; at a default of 1000, you must be doing a lot of inserts to reach that. Thank you Chris, I think I understand now. Remember if you have a lot of rows waiting and mysql crashes, they're lost. Well, I know that, but loosing 2000 inserts when i made more than 3 000 000 a day isn't a big problem. This table is for statistics only, data isn't very important and MySQL doesn't crash as often happyily :) Now i just need to choose, I can boost the insert ratio but i'll take some risks, or i can leave all as default ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0.15 Warning: thr_alarm queue is full BUG
Hi list, I think i've found a little bug in MySQL 4.0.15 (mysql-standard-4.0.15-pc-linux-i686.tar.gz) Description : Here's my error log : Warning: thr_alarm queue is full Warning: thr_alarm queue is full [...] How To Repeat : Here's my conf file : [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-innodb skip-name-resolve skip-host-cache skip-locking set-variable= max_connections=256 set-variable= table_cache=512 set-variable= key_buffer = 64M set-variable= max_allowed_packet = 1M set-variable= sort_buffer_size = 512K set-variable= net_buffer_length = 8K set-variable= myisam_sort_buffer_size = 8M Then use SET to modify some variables : set global max_connections=512; set global table_cache = 1024; And wait :) It seems that thr_alarm queue was allocated when server starts and not reallocated when modify some variables. How To Solve : Do not use SET, modify my.cnf and restart the server instead. Hope this help. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1044 : Access Denied problem Create Table
Hi list, I've just a crazy problem with Mysql 4.0.15 (mysql-standard-4.0.15-pc-linux-i686.tar.gz). I have 2 user, root and Dstats. With root, i can do all i want (create database, create table ...). With Dstats i can't create table in a database. My Grant : grant all privileges on Dstats_Stats.* to [EMAIL PROTECTED]; Here's the db table for my user Dstats : mysql select * from db where Host=localhost and Db=Dstats_Stats; +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ | localhost | Dstats_Stats | Dstats | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y| +---+--++-+-+--- --+-+-+---++-+-- --++---+--+ 1 row in set (0.00 sec) Then mysql flush privileges; And now trying to create a db : shell ./mysql -u Dstats -p Dstats_Stats Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2839807 to server version: 4.0.15-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create table test (testi integer not null,PRIMARY KEY (testi)) type=MyISAM; ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'Dstats_Stats' Can someone please help me ? I don't know what i'm doing wrong, all my MySQL servers work like a charm except this one ... PS : This is the my.cnf : [client] port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-innodb skip-name-resolve skip-host-cache skip-locking max_connections=512 table_cache=1024 query_cache_type = 0 key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M Thanks in advance. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert BUG into MyISAM table (Linux + MySQL 3.23.56)
Can you create a repeatable test case ? Regards, Sergei On the server where i've got problem, I tried making a dump and import dump into a table with same name but into a different database. There it works ... So, i come back to my production database and table. I've always got the same problem. I tried a CHECK TABLE, but saying me that's all is OK :( I'll try tomorow a myisamchk on this table, I can't take down my table for the moment (too many people connected). Here's the status; Threads: 4 Questions: 46820537 Slow queries: 1324 Opens: 712 Flush tables: 1 Open tables: 512 Queries per second avg: 27.049 Thx David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert BUG into MyISAM table (Linux + MySQL 3.23.56)
Hi list,
I've got a little bug with MySQL.
I can insert a row into my table but this row will not appear in the table
:(
Server is under linux redhat, MySQL is 3.23.56 installed from binary
tar.gz
from MySQL team.
Table Description :
mysql desc Log_Forums;
+--+--+--+-+-+--
--+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
--+
| Pseudo | char(16) | | PRI | |
|
| Date | timestamp(14)| YES | PRI | NULL|
|
| Numero | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| Type_message | enum('0','1')| | | 0 |
|
+--+--+--+-+-+--
--+
4 rows in set (0.00 sec)
And some code :
mysql select * from Log_Forums where Date=2003080611;
Empty set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(doss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(coss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
As you can see, i can't find coss08 in my table :(
Table check is ok :
mysql check table Log_Forums;
+--+---+--+--+
| Table| Op| Msg_type | Msg_text |
+--+---+--+--+
| jeuxvideo.Log_Forums | check | status | OK |
+--+---+--+--+
1 row in set (0.15 sec)
Table have got lots of records with pseudo=coucou.
It seems now that i can't insert any row which pseudo coucou
Can someone help me ?
Thanks
David
So i've make a dump of this table and import it on a new server.
There, i haven't got any problem.
Si i tried to understand how to make MySQL work like it must works.
In fact my table have got 1200 different pseudos and always 60K records.
When i'm doing an insert, i also do a delete ...
But pseudo coucou is repeated 53250 times.
I tried something like :
insert into Log_Forums (pseudo,date,Type_Message) values
(buh08,2003080611,0);
Don't work.
But if i do :
update Log_Forums set pseudo =boucou where pseudo=coucou;
then
insert into Log_Forums (pseudo,date,Type_Message) values
(buh08,2003080611,0);
WORK ...
That seems that a large number of one specific pseudo make MySQL consider
that this value will be the lowest possible value.
Can someone have some clue to help me ?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert BUG into MyISAM table (Linux + MySQL 3.23.56)
Hi,
I don't know if it has anything to do with your problem, I just wanted to
comment on the use of a timestamp column in a primary key...
* David Bordas
+--+--+--+-+-+
+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+
+
| Pseudo | char(16) | | PRI | | |
| Date | timestamp(14)| YES | PRI | NULL| |
| Numero | smallint(5) unsigned | | PRI | NULL
|auto_increment |
| Type_message | enum('0','1')| | | 0 | |
+--+--+--+-+-+
-- --+
The timestamp column will automatically be updated when any other field in
the row is updated. This kind of magic is normally not wanted for
primary
keys... :)
URL: http://www.mysql.com/doc/en/DATETIME.html
I know that :)
But timestamp is quite great because it only uses 4 Bytes /row where
DateTime will use 8 Bytes / row.
And i'm only doing select / insert / delete so, timestamp is good for me.
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert bug into MyISAM table (Linux + MySQL 3.23.56)
Hi, mysql select * from Log_Forums where Date=2003080611; mysql insert into Log_Forums (pseudo,date,Type_Message) values (doss08,2003080611,0); insert into Log_Forums (pseudo,date,Type_Message) values (doss08,2003080611,0); Same thing here. But i solve my problem by update table like this : UPDATE Table set pseudo=- where pseudo=coucou. Now the lowest usable pseudo is - and not coucou. - is the lowest word i can find, so ... Thx David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23 vs. 4.0
Hi! Currently, I'm running 3.23.51 on Red Hat 7.1, and I'm contemplating upgrading to MySQL 4.0, but I'm not sure what to expect. I don't know if MySQL 4.0 is fully backwards-compatible with 3.23.x versions, or if something is going to break if I upgrade. What are the main advantages of upgrading to 4.0? Speed? Features? I can go through the whole changelog if need be, but I'd prefer to hear what actual users are reporting as far as benefits and disadvantages. - Jonathan Did you look at manual ? http://www.mysql.com/doc/en/Upgrading-from-3.23.html David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert bug into MyISAM table (Linux + MySQL 3.23.56)
Hi list,
I've got a little bug with MySQL.
I can insert a row into my table but this row will not appear in the table
:(
Server is under linux redhat, MySQL is 3.23.56 installed from binary tar.gz
from MySQL team.
Table Description :
mysql desc Log_Forums;
+--+--+--+-+-+--
--+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
--+
| Pseudo | char(16) | | PRI | |
|
| Date | timestamp(14)| YES | PRI | NULL|
|
| Numero | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| Type_message | enum('0','1')| | | 0 |
|
+--+--+--+-+-+--
--+
4 rows in set (0.00 sec)
And some code :
mysql select * from Log_Forums where Date=2003080611;
Empty set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(doss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(coss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
As you can see, i can't find coss08 in my table :(
Table check is ok :
mysql check table Log_Forums;
+--+---+--+--+
| Table| Op| Msg_type | Msg_text |
+--+---+--+--+
| jeuxvideo.Log_Forums | check | status | OK |
+--+---+--+--+
1 row in set (0.15 sec)
Table have got lots of records with pseudo=coucou.
It seems now that i can't insert any row which pseudo coucou
Can someone help me ?
Thanks
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld CPU usage is almost 100% !!!
Hi, I'm using GNU/Linux Slackware 8.0 and not using any my.cnf (so the configs are all at default).. Is it possible that mysql is doing some background tasks while the system is in idle state ? Which MySQL version did you use ? Some old mysql binary were compiled with wrong glibc and have this kind of problem ... Around 3.23.50, 3.23.51 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storage issue
Here's an example of how much data could be stored in a year: 1 value/minute are stored = 1440 values/day. 365 days / year. We have 100 different tables with 25 columns each. This makes 100*25*365*1440 = 1 314 000 000 values per year. A typical value could be 25,5624. How much space (in Mb) could this take up after a year do you think? You'll find something here : http://www.mysql.com/doc/en/Storage_requirements.html Typically a float is stored on 4 Bytes ( a Byte is 8 bits ). - 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
Access denied database privilege question
Hi list, I've a question about the database privilege and the Mysql.db table. If i do something like this : GRANT SELECT ON MYDB.* TO [EMAIL PROTECTED]; ( i'm using MyISAM tables ... ) And if MYDB contains something like 100 tables, could this make me problems ? In fact, i have errors like this : Failed to connect to database: 1044 Error: Access denied for user: '[EMAIL PROTECTED]' to database 'MYDB' 2006 : MySQL server has gone away All is working well, except sometimes i have such errors each day during a small period of time and all is going back well later ... I see that restarting the serveur could help me but i don't know why ... All connections are through TCP / IP on a private 100Mb LAN. Extract of my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve skip-host-cache [...] Here : http://www.mysql.com/doc/en/Access_denied.html I've find something : Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using It's true, i'm using mysql-3.23.54a-pc-linux-i686.tar.gz and the system is Red Hat 7.2 with red hat buggy glibc. This could be my problem ? Please, i need to solve my problem ... Perhaps i'm not alone having this problem ... Did someone hear ( and understand ) me ? Thanks David - 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
3.23.54 Aborted_clients Aborted_connects
Hi list and thanks to read me. Since december, I've got aborted_clients and aborted_connects to my mysql server ... Clients that connected to this server have errors like : Failed to connect to database: Error: Access denied for user: '[EMAIL PROTECTED]' to database 'mydb' MySQL server has gone away Server is : 2 * Intel PIII 1GB ram RAID 5 scsi disk Linux Red Hat Linux sql1.jeuxvideo.com 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown Here's the my.cnf : port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve skip-host-cache set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=2M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=512 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 set-variable= thread_concurrency=4 The network is a 100Mb private LAN. All ethernet cards on the network are fixed to 100Mb full duplex. Error log of mysql didn't show anything : 030116 08:54:47 mysqld started /usr/local/mysql/bin/mysqld: ready for connections and that's all ... Logging queries didn't help me to find me problem ... Here's some stats : mysql status; -- ./mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686) Connection id: 337007 Current database: Current user: root@localhost Current pager: stdout Using outfile: '' Server version: 3.23.54 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 1 day 8 min 49 sec Threads: 2 Questions: 833840 Slow queries: 28 Opens: 97 Flush tables: 1 Open tables: 72 Queries per second avg: 9.592 Extract of show table status : Aborted_clients 1032 Aborted_connects 1380 Bytes_received89271244 Bytes_sent 2877636797 I try to upgrade kernel to 2.4.18 but all was the same so i came back to default kernel. I change mysql server from 3.23.53a to 3.23.54a but didn't solve anything ... I check all table with CHECK TABLE mytable EXTENDED and all are ok ... Please, i'm going mad, i didn't find the problem ... Rgds David - 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: 3.23.54 Aborted_clients Aborted_connects
Hi, Was found problems with some glibc system, please use temporally the work around of the start the server with the option: --thread_stack=196KB Ok i'll have a look to that. For the glibc, i used binary tar.gz, so even if my glibc is buggy this'll not affect me ... The access denied is privilege issue. Probably but what this works 99% of the time and sometimes have access denied ? Tables Mysql.* are clean ... Thanks David - 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: Re: Order by does not use an index when it should.
Anybody else who has any idea why the index are not used as they should? I've got same things with 3.23.xx and select query through a TCP/IP connection. I don't know why, but you can solve this issue using the USE INDEX syntax for select queries ... http://www.mysql.com/doc/en/SELECT.html David - 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 [Access denied for user], please help !!!
Hi all, I've some problem with my Mysql servers since the beginning of last week :( I've upgrade mysql from 3.23.53a to 3.23.54a and i still have same errors :( I've got plenty of error like this : Failed to connect to database: Error: Access denied for user: ´[EMAIL PROTECTED]´ to database ´DB1´ MySQL server has gone away I connect from web servers which are on the same 100Mb LAN that mysql servers. I try during a ping during at least 2 hours and i didn't find anything, 0 packet loss ... OS : linux I tried check table extended on the table and status is ok, I try a new flush privileges and this didn't change anything ... Thanks. David Original message : Subject : [Error] Mysql server has gone away 3.23.53a Hi all, I've some problem with my Mysql servers since the beginning of this week. I've got plenty of error like this : Failed to connect to database: Error: Access denied for user: ´[EMAIL PROTECTED]´ to database ´DB1´ MySQL server has gone away Error Nø1, please contact webmaster ... In fact, clients are C cgi compiled with libmysql ( in this package MySQL-devel-3.23.49-1.i386.rpm ) The Cgi is ok and have worked well since several mounths. Error Nø1 : connection mysql ok, can't execute my query It seems that mysql disconnect me before i can execute my query. But query is just after the connection in the code ... :( Server : Linux Redhat 2 * PIII 1 Ghz 1Go ram Scsi raid 5 mysql-3.23.53a-pc-linux-gnu-i686.tar.gz ( binary ) As you can see, i connect to my server via a 100Mb LAN. The LAN is ok, all ethernet card are 100Mb Full Duplex without any collision or packet dropped. Exemple : RX packets:259950587 errors:0 dropped:0 overruns:0 frame:0 TX packets:1809122347 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:3130833186 (2985.7 Mb) TX bytes:1637489037 (1561.6 Mb) Here's the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=512 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 set-variable= thread_concurrency=4 I can't see anything in the error log. Mysqld is still alive and still responding 99% of the time. Status : Threads: 3 Questions: 3065350 Slow queries: 46 Opens: 91 Flush tables: 1 Open tables: 66 Queries per second avg: 9.052 Show Status : Aborted_clients 4401 Aborted_connects374 Bytes_received332129641 Bytes_sent 1691254415 [...] Created_tmp_disk_tables 0 Created_tmp_tables211 Created_tmp_files 1290 Handler_delete | 11135 | Handler_read_first | 226| Handler_read_key | 2005755| Handler_read_next| 1714626349 | Handler_read_prev| 0 | Handler_read_rnd | 2613047| Handler_read_rnd_next| 62634691 | Handler_update | 357300 | Handler_write| 304440 | Key_blocks_used | 62869 | Key_read_requests| 97082717 | Key_reads| 60114 | Key_write_requests | 487582 | Key_writes | 392106 | Max_used_connections | 23 | Not_flushed_key_blocks | 0 | Not_flushed_delayed_rows | 0 | Open_tables | 66 | Open_files | 102| Open_streams | 0 | Opened_tables| 91 Questions| 3065612| Select_full_join | 0 | Select_full_range_join | 0 | Select_range | 600| Select_range_check | 0 | Select_scan | 51701 | Slave_running| OFF| Slave_open_temp_tables | 0 | Slow_launch_threads | 0 | Slow_queries | 46 | Sort_merge_passes| 645| Sort_range | 99887 | Sort_rows| 2613047| Sort_scan| 20030 | Table_locks_immediate| 1841001| Table_locks_waited | 1977 | Threads_cached | 0 | Threads_created | 1226346| Threads_connected| 2 | Threads_running | 1 | Uptime | 338664 Extract of the mysql.db table : 192.168.10.4 | DB1 | JForvC15 | Y ( select priv ... ) Thanks to all. David - 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
Re: [Error] Mysql server has gone away
hi, I always have my problem and i didn't find any solution pour the moment. Please, if somone have an idea ... :( Thanks David Subject: [Error] Mysql server has gone away 3.23.53a Date: Thu, 19 Dec 2002 17:23:31 +0100 I've some problem with my Mysql servers since the beginning of this week. I've got plenty of error like this : Failed to connect to database: Error: Access denied for user: ´[EMAIL PROTECTED]´ to database ´DB1´ MySQL server has gone away Error Nø1, please contact webmaster ... In fact, clients are C cgi compiled with libmysql ( in this package MySQL-devel-3.23.49-1.i386.rpm ) The Cgi is ok and have worked well since several mounths. Error Nø1 : connection mysql ok, can't execute my query It seems that mysql disconnect me before i can execute my query. But query is just after the connection in the code ... :( Mysql server is always up and didn't gone away any more ... Server : Linux Redhat 2 * PIII 1 Ghz 1Go ram Scsi raid 5 mysql-3.23.53a-pc-linux-gnu-i686.tar.gz ( binary ) As you can see, i connect to my server via a 100Mb LAN. The LAN is ok, all ethernet card are 100Mb Full Duplex without any collision or packet dropped. Exemple : RX packets:259950587 errors:0 dropped:0 overruns:0 frame:0 TX packets:1809122347 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:3130833186 (2985.7 Mb) TX bytes:1637489037 (1561.6 Mb) Here's the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=512 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 set-variable= thread_concurrency=4 I can't see anything in the error log. Mysqld is still alive and still responding 99% of the time. Status : Threads: 3 Questions: 3065350 Slow queries: 46 Opens: 91 Flush tables: 1 Open tables: 66 Queries per second avg: 9.052 Show Status : Aborted_clients 4401 Aborted_connects374 Bytes_received332129641 Bytes_sent 1691254415 [...] Created_tmp_disk_tables 0 Created_tmp_tables211 Created_tmp_files 1290 Handler_delete | 11135 | Handler_read_first | 226| Handler_read_key | 2005755| Handler_read_next| 1714626349 | Handler_read_prev| 0 | Handler_read_rnd | 2613047| Handler_read_rnd_next| 62634691 | Handler_update | 357300 | Handler_write| 304440 | Key_blocks_used | 62869 | Key_read_requests| 97082717 | Key_reads| 60114 | Key_write_requests | 487582 | Key_writes | 392106 | Max_used_connections | 23 | Not_flushed_key_blocks | 0 | Not_flushed_delayed_rows | 0 | Open_tables | 66 | Open_files | 102| Open_streams | 0 | Opened_tables| 91 Questions| 3065612| Select_full_join | 0 | Select_full_range_join | 0 | Select_range | 600| Select_range_check | 0 | Select_scan | 51701 | Slave_running| OFF| Slave_open_temp_tables | 0 | Slow_launch_threads | 0 | Slow_queries | 46 | Sort_merge_passes| 645| Sort_range | 99887 | Sort_rows| 2613047| Sort_scan| 20030 | Table_locks_immediate| 1841001| Table_locks_waited | 1977 | Threads_cached | 0 | Threads_created | 1226346| Threads_connected| 2 | Threads_running | 1 | Uptime | 338664 Extract of the mysql.db table : 192.168.10.4 | DB1 | JForvC15 | Y ( select priv ... ) - 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
[Error] Mysql server has gone away 3.23.53a
Hi all, I've some problem with my Mysql servers since the beginning of this week. I've got plenty of error like this : Failed to connect to database: Error: Access denied for user: ´[EMAIL PROTECTED]´ to database ´DB1´ MySQL server has gone away Error Nø1, please contact webmaster ... In fact, clients are C cgi compiled with libmysql ( in this package MySQL-devel-3.23.49-1.i386.rpm ) The Cgi is ok and have worked well since several mounths. Error Nø1 : connection mysql ok, can't execute my query It seems that mysql disconnect me before i can execute my query. But query is just after the connection in the code ... :( Server : Linux Redhat 2 * PIII 1 Ghz 1Go ram Scsi raid 5 mysql-3.23.53a-pc-linux-gnu-i686.tar.gz ( binary ) As you can see, i connect to my server via a 100Mb LAN. The LAN is ok, all ethernet card are 100Mb Full Duplex without any collision or packet dropped. Exemple : RX packets:259950587 errors:0 dropped:0 overruns:0 frame:0 TX packets:1809122347 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:3130833186 (2985.7 Mb) TX bytes:1637489037 (1561.6 Mb) Here's the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=512 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 set-variable= thread_concurrency=4 I can't see anything in the error log. Mysqld is still alive and still responding 99% of the time. Status : Threads: 3 Questions: 3065350 Slow queries: 46 Opens: 91 Flush tables: 1 Open tables: 66 Queries per second avg: 9.052 Show Status : Aborted_clients 4401 Aborted_connects374 Bytes_received332129641 Bytes_sent 1691254415 [...] Created_tmp_disk_tables 0 Created_tmp_tables211 Created_tmp_files 1290 Handler_delete | 11135 | Handler_read_first | 226| Handler_read_key | 2005755| Handler_read_next| 1714626349 | Handler_read_prev| 0 | Handler_read_rnd | 2613047| Handler_read_rnd_next| 62634691 | Handler_update | 357300 | Handler_write| 304440 | Key_blocks_used | 62869 | Key_read_requests| 97082717 | Key_reads| 60114 | Key_write_requests | 487582 | Key_writes | 392106 | Max_used_connections | 23 | Not_flushed_key_blocks | 0 | Not_flushed_delayed_rows | 0 | Open_tables | 66 | Open_files | 102| Open_streams | 0 | Opened_tables| 91 Questions| 3065612| Select_full_join | 0 | Select_full_range_join | 0 | Select_range | 600| Select_range_check | 0 | Select_scan | 51701 | Slave_running| OFF| Slave_open_temp_tables | 0 | Slow_launch_threads | 0 | Slow_queries | 46 | Sort_merge_passes| 645| Sort_range | 99887 | Sort_rows| 2613047| Sort_scan| 20030 | Table_locks_immediate| 1841001| Table_locks_waited | 1977 | Threads_cached | 0 | Threads_created | 1226346| Threads_connected| 2 | Threads_running | 1 | Uptime | 338664 Extract of the mysql.db table : 192.168.10.4 | DB1 | JForvC15 | Y ( select priv ... ) Thanks to all. David - 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: Sub-Select query
I have the following query. When executed in MySQL I get an error on the first sub-select, and I can't find why: http://www.mysql.com/doc/en/News-4.1.x.html You'll see that sub-select query will be in future 4.1.xx release ... David - 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: Start up problem
Hello, I upgrade my server from Mandrake 8.2 to Mandrake 9.0 and i have a little problem with MySql 3.23 (Ver 3.23.52 for pc-linux-gnu on i686): When i try to start MySQL with this command with root user : # safe_mysqld --user=mysql I obtains the following response: # Starting mysqld daemon with databases from /var/lib/mysql # 021205 17:40:17 mysqld ended Then when i try to run under mysql user # safe_mysqld Its OK I have make many reinstall and i have always the same problem. Any idea ? Have a look to the mysql error log to see if you can find your problem ... This file should be in /usr/local/mysql/data David - 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: Start up problem
Have a look to the mysql error log to see if you can find your problem ... This file should be in /usr/local/mysql/data I have no log file, the only file created is serveur name.err with the number of the thread who crash. touch13 This file IS the mysql error log. On one of mine i can see this for example : 021128 10:48:21 mysqld ended 021128 14:12:42 mysqld started /usr/local/mysql-3.23.53a/bin/mysqld: ready for connections So if mysql don't start, there'll normally be something there . David - 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: Tuning MySQL Server Parameter
I wish to tune our MySQL Server Parameter to increase the speed of Join. I was trying to do a simple join with two tables. One is big (~2,500,000 records); the other one is small. The current join seems to take forever to finish even on the indexed attribute. I am trying to learn from http://www.mysql.com/doc/en/Server_parameters.html but not confident enough to play with our server yet. Any advice will be much appreciated. I am running mysql3.23.49 on linux7.3 with 4 GB memory. So I want to try the following from that doc: shell safe_mysqld -O key_buffer=64M -O table_cache=256 -O sort_buffer=4M -O read_buffer_size=1M My questions: if I run the above command (as root), should I run it every time when the server starts? If so, how can I set the above option automatically when server starts. Thanks! Modify you my.cnf to add or change this parameter and mysql will normally read this cnf file each time you launch it via mysql.server script ... David - 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: getting name of columns
How can i extract names of columns from table? I am doing select * from table, then i want to get name of columnsis it possible? Use desc or describe. http://www.mysql.com/doc/en/DESCRIBE.html David - 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: Socket Error
Hi All, What's wrong if this error message appears when I try to run mysql: cannot connect to local Mysql server through socket '/var/lib/mysql/mysql.sock' If I run mysql_config, this appears: --socket /tmp/mysql.sock What should I do to correct this? Thanks. Modify your my.cnf file ... David - 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: mySQL logs on Linux
Could someone tell me where the logs are written to by default, for a 3.23 install of MySQL on Linux? I looked in /usr/share/mysql/english and each directory leading up to it, but I didn't see anything resembling a log file. Thanks. Neal Logs are in your data directory with the Tables ... Perhaps /var/mysql/data or /usr/local/mysql/data David - 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: load problems on mysql 3.23.52 with and without Max
This problem is due to the glibc used by mysql AB for building binary ... Have a look to mysql 3.23.53 change log : We've hopefully fixed the problem with spurious load spikes on Linux systems when accessing the Database via TCP/IP. This was caused by the static glibc files we used to link against and should now be resolved But 3.23.53 have other problems, so if you want to upgrade, wait some hours for 3.23.53a binary ... David - 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: mysql highload
MR We run MySQL on a 2xP3 1Ghz (Coppermine), 1 GB RAM, 1 software RAID1 (2 x MR SCSI), Linux 2.4.9-31 (RedHat patched kernel). We have run a older version MR of MySQL (we run only binary releases) for a long time (6 months) with MR great success. We had uptimes like 100 days and almost 200 queries / MR second (as reported by status). MR MR We had to reinstall the OS and MySQL software along with it (but the MR data dir of MySQL has not been touched). We installed a binary release MR of last MySQL (3.23.52). We use the huge configuration file (the one for MR 1-2GB systems), with only change of the thread-concurency from 8 we made MR it 4 ( 2 x number of CPUs ). We even increased the priority of MySQL using MR (re)nice commands (made it max with -20 value). Problem is that from time MR to time (once a day) MySQL stops working properly. It starts to increase MR the load average of the system, a ps ax show almost all mysqld processes MR in state of R (running). The system is dedicated MySQL only so it seems to MR be problems related to mysqld. We dont have the my.cnf used in the old MR installation (we dont know for sure which version we used at that time, MR but we know it was 3.23.49 or 3.23.48). The solution for now is a MR watchdog script which checks for system load and restarts mysql MR (mysqladmin shutdown) when load is too high :( MR Can somebody give us any hint ? Also if I need to give more info about the MR configuration of this server please ask. Hi, This problem occurs to several people like me. I have quite the same hardware than you. In fact it's a problem with the binary version of mysql. Solution for me was to downgrade mysql to version prior than 3.23.49 ... And still waiting for a working binary version ... David - 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: MyISAM / Performance / Nb of table per DataBase
DB I found my problem and why Mysql lock my table for a select. In DB fact, mysql sometimes don't use the right index and so does a 'Table DB sort' that lock my table. I modify the query to add USE INDEX DB clause and now, all seems to work well ... I just need to test DB during several days to be sure that's i'm right .. JZ Did you first try an ANALYZE TABLE to update the optimizer stats? Yes ... I'm still having problem in fact, my search query is faster but also lock my table. But it's just a select :( I'm doing TCP connections to mysql and not localhost, perhaps the problem is here ... The network is ok and my app server is linking with the mysql server over a 100Mb LAN. The network have been tested and tested and all is well. I've given a try to fulltext index, not really better in query time processing but indeces have gone bigger drasticully ... Now, i should give a try to InnoDb. And i 'll also try to split my MyISAM table into smaller ones ... Thanks to all David - 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
Max NB of MyISAM tables / DB ( Ext3 linux )
Hi list, I've just a little question for the end. I planned to have around 10K tables under a DB and this number surelly grow up to 20K. I know that a database is a directory and a table is 3 files. I just want to know is mysql have a limit in the number of table per database. If not, i know that ext3 can have ten of thousands files in a directory. But commande like 'ls' will become slower and slower ... Is this also slowing mysql ? Thanks - 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: No my.cnf file on Linux?
Hi, I use mysql 3.23.4 max on linux and it have worked just fine. Now I want to use Transactions so I thougt that I should use the my.cnf to configure for that. Trouble is there is no my.cnf file on my computer. Do I have to create the file myself ? or is the InnoDB settings configured right by default? have a look to the support_files sub-directory of you mysql directory. ( Something like /usr/local/mysql/support-files ) You'll find some examples of my.cnf ... David - 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: MyISAM / Performance / Nb of table per DataBase
DBI still have performance problem with my big MyISAM table. DBIn fact, mysql keep locking my table, even for doing select :( PDB That's how MyISAM works -- it uses table locks for everything. PDB Given that fact, if it didn't lock the table for reading, that would PDB allow other clients to update the table at the same time -- leading PDB to disastrous results. DBI don't know why but it's very very annoying. DB1% of my query are search ones. DBTheses queries are slow, too slow. I found my problem and why Mysql lock my table for a select. In fact, mysql sometimes don't use the right index and so does a 'Table sort' that lock my table. I modify the query to add USE INDEX clause and now, all seems to work well ... I just need to test during several days to be sure that's i'm right .. Thanks David - 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
MyISAM / Performance / Nb of table per DataBase
Hi list, I still have performance problem with my big MyISAM table. In fact, mysql keep locking my table, even for doing select :( I don't know why but it's very very annoying. 1% of my query are search ones. Theses queries are slow, too slow. So i'm thinking about splitting my table to lot of little tables. Can i have around 5000 Tables under the same database ? Or should i keep my big table and do not split ? Thanks David Config : Mysql 3.23.45 ( tar.gz binary ) Linux Red-hat 7.1 kernel 2.4.19 1Go ram - 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
MyISAM and Lock table question
Hi all, Just a little question on the MyISAM table lock implementation ... I just see ( with show full processlist ) that my version of mysql ( 3.23.45 binary linux tar.gz ) locked my MyISAM table at every query and not only for update / delete / insert. Why is it locking table for select query ? Did i miss something somewhere ? Thanks David - 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: Mysql on a separate server from webserver
Hi, I have written previous websites where I used perl or PHP to access a Mysql DB wen bith the webserver and the Mysql db are on the same server. My questions is how different is it to access or connect to the Mysql database when the database is on a separate server(machine)? Juste modify the DBI args in you .pl file to set IP of the mysql server. That was 127.0.0.1 or locahost when you are on the same server. Other thing, verify that -skip-networking is not 'ON' in the mysql config if you wish to allow network connection ... David - 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: Help please - MySQL 3.23 stability / binary
From: borracho 138 [EMAIL PROTECTED] I've searched for more information on these issues, without luck. The system randomly spirals out of control - load avg increases very rapidly, resulting in loads of 200-300 in 2/3 minutes. At other times the server is fine, handling the busiest loads. It doen't appear to be using any swap space at all (0K) - the 'mysqld' process size gradually has grown to the point where no free RAM was available and 'spiralled' , but that isn't the general case. Restarting MySQL cures the problem, although system memory isn't being reclaimed (see below) I also have a memory related problem - the system doesn't free memory when MySQL is stopped and started. (I suspect this may be a kernel related issue) RH have released a kernel update 2.4.18-10, which apparently fixes a problem with the filesystem cache - could this be related? (Again, I've been unable to find more information on the subject) As a result I'm tempted to try a compiled version (all tests ran fine, as did MySQL 3.22 in the past) Is a stability/corruption free MySQL guaranteed using gcc 2.95.3? Several users ( like me ) have reported load problem using 3.23.51 and 3.23.52 linux binary version of mysql. Something wrong with the glibc used by mysql AB i think ... The only cure that works for me is not to use 3.23.51 and 3.23.52 binary until that a new stable one will be released or building his own binary ... David - 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: Help please - MySQL 3.23 stability / binary
From: borracho 138 [EMAIL PROTECTED] Many thanks for the reply - nice to know that I'm not the only one with this issue(!) Seems like my problem is related to 2 areas: - I'm concerned about the lack of memory 'freeing' with the system, but a kernel upgrade may fix that Don't have this problem i think or if i have it, don't have see for the moment ... - The other is with the MySQL binary Yep. Which RPM versions of the RH kernel, gcc and glibc are/were you using? Currently my system has: kernel-2.4.18-3 gcc-2.96-110 glibc-2.2.5-36 glibc-devel-2.2.5-36 mine : kernel : 2.4.7-10smp gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) using mysql binary from tar.gz. It looks like I'll try to install gcc-2.95.3 from source and then build mysql-3.23.52 from that. Were there are particular build options you used for gcc (and mysql )? Read doc for build here : http://www.mysql.com/doc/en/Installing_source.html ( all 2.3.xx chapters ) http://www.mysql.com/doc/en/Linux.html You can try too amd athlon optimisation but i don't know if it's working well. NB : i can't downgrade gcc on my server so i'm only using binary distribution of mysql. Perhaps it's not the best solution, i don't know ... David - 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: Slow select query, need some clues to speed it up please ...
BS Have you tried the command : EXPLAIN SELECT select_options BS (http://www.mysql.com/doc/en/EXPLAIN.html). Maybe it can help you to see BS what Indexes are used ... Yep i do that but varchar and text fields aren't index so ... I'm also having a look to FULLTEXT index, but i think that the index will be really really big ... :( I'm also reading docs about locking. In fact i saw while using show full processlist that when mysql is executing the query, all other ones are 'locked' but why lock table for a SELECT query ? And that's a good question. I surely missed something about the lock statement ... David - 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: Slow select query, need some clues to speed it up please ...
From: Shashank Tripathi [EMAIL PROTECTED] This will be fast if you have the right index-- field5 like 'John%' These will NOT be fast -- field5 like '%John%' field5 like '%John' Putting a wildcard character (%) before the term will avoid the index. Yep, and it's for that point that an index on varchars and text will not help me ... alter table mytable add index (myvarchar, mytext(100)) I think that the index will too big to be usefull for me. Other way if i need an index, so perhaps i can try FULLTEXT. I'm currently building a fulltext index on a test server but it's lonnng to do that :) One last resort that may work for you is to try the regexp instead of LIKE. The functionality of regexps for me has at best been unpredictable, sometimes it is faster, sometimes at par with LIKEs. So, where CONTENT like '%word1%' or CONTENT like '%word2%' or CONTENT like '%word3%' becomes, where CONTENT regexp 'word1|word2|word3' Why not regexp but this is not i mean, in fact where CONTENT like '%word1%' or CONTENT like '%word2%' or CONTENT like '%word3%' shoud be : where CONTENT1 like '%word%' or CONTENT2 like '%word%' or CONTENT3 like '%word%' So i'll be 3 regexp i think ... Other big problem is why mysql locked all queries during executing this one ... Thanks David - 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: Slow select query, need some clues to speed it up please ...
From: Shashank Tripathi [EMAIL PROTECTED] | Why not regexp but this is not i mean, in fact | where CONTENT like '%word1%' | or CONTENT like '%word2%' | or CONTENT like '%word3%' | shoud be : | where CONTENT1 like '%word%' | or CONTENT2 like '%word%' | or CONTENT3 like '%word%' | So i'll be 3 regexp i think ... No, that'll be one regexp. That is the beauty of using regexp. Try it. I'll have a look to doc and try it soon ... | Other big problem is why mysql locked all queries during | executing this one ... Agree on this one, this is a little unfortunate. But MySQL 4 should give you row level locking, and well, using index on '%WORD%' as well. Meanwhile, a full text index should be the only way out for you. Its big but its worth it in your case. As soon as fulltext index will be built, i'll try some test and see what's is better ... Thanks David - 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
Slow select query, need some clues to speed it up please ...
Hi all, I've discovered that we have a select query that blocked all others query to this table. 99% of query used indexs, no join ... But this one is a problem : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John')' part is the problem. Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; select count(*) from MyTabe ; 4381036 Table is growing about 10 000 record a day ... So, what can i do to have better performance ? Create a big index for Field3,Field4, Field5 ? Not a very good idea i think because it'll be a very very big index and Field 3-5 are varchar or text ... But why not ... Perhaps doing 3 select, one with Field3, one with Field4 and the other with Field5 and then doing some code in the app for joining the 3 select results. Or did i miss something in the select syntax that can help me ? Thanks David - 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: Slow select query, need some clues to speed it up please ...
ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David sql,query - 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: Slow select query, need some clues to speed it up please ...
DB Already done something like optimize : DB myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable ME And? No result? Some good result but nothing enough good :( I'll have a look to fulltext search, perhaps i'll find something good. I know that fulltext search only search for full word but perhaps it'll be enough ... ME P.S. Can you send a copy to mysql-list, please. Already done ... David - 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: Slow select query, need some clues to speed it up please ...
From: Franz, Fa. PostDirekt MA [EMAIL PROTECTED] KF an Index wouldnt help much , because of the 'LIKE %... '. KF If a wildcart is at the beginning of the search-string , an Index KF cannot help much. Ok as i thought, index text field is a bad thing ... KF You have to think about why are there 3 fieds in your Table , KF in which it is possible that the same kind of data can appear. KF This structure forces you to query like this. KF If you cannot get rid of this , it might be helpfull to make another field KF for searching , where you concat Field 3,4 and 5 and just search on KF this field. I can't create a new field, this will increase dramatically my table growth ... KF I think it is better , if you change the way the data is inserted in your table , KF that you can search more specific - without LIKE '%...' Hmm i've think about it but is quite difficult to find a better way. All the app is well, except this search ... Thanks David sql,query - 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: Slow select query, need some clues to speed it up please ...
From: Andrew Izsof [EMAIL PROTECTED] DB I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE DB '%John')' part is the problem. AI The formula : '%John%' always sweeps through all of the records, because it AI can't utilize any indexes, sorting, etc. But if you leave the wildcard % AI from the beginning, then the optimizer quickly sorts out the relevant AI records. Of course this reduces the effectiveness of the search pattern, but AI makes the query ultra-fast. Hmm don't be enough for me :(. AI Another way to speed up the whole thing (I recommend this one, because of AI the big text areas) is using fulltext indexes. The only drawback with AI fulltext, that it can only search for full words. I'll try to have a look to this ... Thanks David - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] ME Check the query plan ME EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM ME MyTable WHERE ME Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR ME Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+---++-+-+-- -++ | MyTable | ref | ReplyTo_Numero,indexF | indexF | 8| const,const | 51145 | where used | 1 row in set (0.01 sec) Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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: Slow select query, need some clues to speed it up please ...
- Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] ME Can you send result of the query, please: ME select * from MyTable PROCEDURE ANALYSE(); Here you are : mysql select * from MyTable PROCEDURE ANALYSE(); +--+--+--++---+- --+-+--+ |Field_name |Min_length|Max_length|Empties_or_zeros|Nulls |Avg_value_or_avg_length|Std |Optimal_fieldtype | +--+--+--++---+- --+-+--+ |MyTable.Field1| 1| 7 | 0 | 0|3988722.5930 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field2| 1| 7 |573688 | 0|3212734.0355 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field3| 1| 47 | 204 | 0|8.2074 |NULL|VARCHAR(47) NOT NULL | |MyTable.Field4| 1| 50 |71 | 0|24.5052 |NULL|VARCHAR(50) NOT NULL | |MyTable.Field5| 1| 51723 | 1 | 0|174.5134 |NULL|TEXT NOT NULL | |MyTable.Field6| 12| 14 | 3809075 | 0|63688541485.2995 |0. |BIGINT(14) UNSIGNED NOT NULL | |MyTable.Field7| 1| 4 | 0 | 0|1646.5029 |2596.7715 |SMALLINT(4) UNSIGNED NOT NULL | +--+--+--++---+- --+-+--+ NB: Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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: Slow select query, need some clues to speed it up please ...
- Original Message - From: Dicky Wahyu Purnomo [EMAIL PROTECTED] DB SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE DB Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR DB Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; DWP have you try to remove the ORDER BY Field6 ? and compare the query time ? Yes but don't change anything in most of case. Best i can do without the order by is 0.01s less than with ... Thanks David - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] ME IMHO the problem is in this condition ME ...Field5 LIKE '%John'... ME Can you remove it from query and try again? In fact it was Field5 LIKE '%John%', but Field5 LIKE '%John' and Field5 LIKE '%John%' don't change query speed at all except about 0.01s .. I try remove it and i was surprised, speed grow and do not decrease. Perhaps because mysql has to analyse more rows before to have enough rows ... Thanks David - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... But it is different query... :) I mean that queries return different results. Yep ... On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( I know that and that's a problem ... So... question: Do you actually need this condition? ;) I need it yes, but i'm thinking about doing 2 or 3 queries or modify some code of my app about this search ... Thanks David - 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: Slow select query, need some clues to speed it up please ...
ME In this case... ME Can you try again? Sure. query 1 : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) ORDER BY Field6 LIMIT 0,20; Results : query | time 1 0.78s 2 1.20s 1 0.77s 2 1.21s 1 0.78s 2 1.22s IMHO there are 2 ways: 1. It will work much faster. 2. It won't change speed of execution significantly. Euh Third one ? Remove a condition slow the query ... Perhaps i've a problem somewhere, but where .. Perhaps with some cache variables ? I don't know. This is the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 Server have got 1Go and run only mysql ... Table have 4M rows and index. Mytable.MYD : 1109586816 bytes Mytable.MYI : 93065216 Thanks David - 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: Re: Re: Next working binary version ?
Config for us : Linux Red-Hat Kernel 2.4.7-10smp #1 SMP Dual intel PIII 1 GB RAM The problem occurs with these versions : mysql-3.23.51-pc-linux-gnu-i686.tar.gz and mysql-3.23.52-pc-linux-gnu-i686.tar.gz Cordialement David BORDAS / JEUXVIDEO.COM --- http://www.jeuxvideo.com : Le site numéro 1 des jeux video en français http://boutique.jeuxvideo.com : Pour acheter tous ses jeux sur le net --- - 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: Next working binary version ?
Victoria Monday, August 19, 2002 11:58 AM, you wrote : VR Hi, David! VR You can find new 3.23.52 binary packages at www.mysql.com VR Could you install new packages and check if loading problem occurs VR again? One of our production server is running 3.23.52 ( the tar.gz one ). Uptime is 26 Hours and all is ok for the moment ... Thanks David - 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: Re: Next working binary version ?
David, Thursday, August 22, 2002, 9:48:14 AM, you wrote: DB Victoria DB Monday, August 19, 2002 11:58 AM, you wrote : VR Hi, David! VR You can find new 3.23.52 binary packages at www.mysql.com VR Could you install new packages and check if loading problem occurs VR again? DB One of our production server is running 3.23.52 ( the tar.gz one ). DB Uptime is 26 Hours and all is ok for the moment ... Hi! Glad to hear it! Thank you for your feedback. Oups forget that i said :( Serveur has just crashed, load average 200 ! Same problem that 3.23.51 but appear some hours after. Surely 'cause we have less people during these days ... Do we come back to 3.23.45 ... David - 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: Mysql command from the shell
IK hi list IK Can I also execute mysql commands from the shell. IK Something like this: IK % mysql -u root -pmypass --execute=use mydb; select * from mytable; You can do something like this i think : % mysql -u root -pmypass mydb --execute='select * from mytable' David - 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
Next working binary version ?
Hi list, I've just a tiny question. I used mysql 3.23.49 linux binary package but i've got some problems with it. I should upgrade quite shortly because 3.23.49 binary package is buggy ... ). We have load problems, memory eating ... But i can't use mysql 3.23.51 binary package. ( see Re: 3.23.51 bug ? ( was RE: Load problems with 3.23.51 )) My linux boxes have got gcc 2.96 and not patched glibc. So i can't compile my own mysql until i upgrade or downgrade gcc ... So, did someone have an idea of a date for a new mysql linux binary package version ? Next thing, for best performance should i use my own binary or mysql one ? I thought that mysql ones are better but perhaps it depends lots of things ... Thanks in advance. David - 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: Performance Monitoring/Counters
Hi, perhaps Mytop will be good for you ... http://jeremy.zawodny.com/mysql/mytop/ David - Original Message - From: Lasse Laursen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 10:56 AM Subject: Performance Monitoring/Counters Hi all, Which tools would you recommend to performance monitoring of a MySQL database server? I'm thinking of the following: System: CPU utilization, disk I/O. RDBMS: buffer cache hit ratio, transactions/sec, connections. Yours -- Lasse Laursen [EMAIL PROTECTED] - Systems Developer NetGroup A/S, St. Kongensgade 40H, DK-1264 København K, Denmark Phone: +45 3370 1526 - Fax: +45 3313 0066 - Web: www.netgroup.dk - We don't surf the net, we make the waves. - 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
Re: Load problems with 3.23.51 ( same here )
Hi list, Same thing here, mysql 3.23.51 works well during about 2 hours then suddently load average grow up to 200 and more ... Load average is less than 4 with 3.23.49. We're using binary tar.gz non max versions from Mysql.com OS : Linux Red hat 7.2 kernel : 2.4.10 1 Go RAM 2 * Intel 1 Ghz During the load pic i've run a ps -eo pid,tt,user,fname,tmout,f,wchan and that's an extract of the output : [...] 29309 ?mysqlmysqld - 040 rt_sigsuspend 29347 ?mysqlmysqld - 040 rt_sigsuspend 29390 ?mysqlmysqld - 040 rt_sigsuspend 29391 ?mysqlmysqld - 040 - 29417 ?mysqlmysqld - 040 rt_sigsuspend 29418 ?mysqlmysqld - 040 rt_sigsuspend 29420 ?mysqlmysqld - 040 rt_sigsuspend 29423 ?mysqlmysqld - 040 rt_sigsuspend 29424 ?mysqlmysqld - 040 rt_sigsuspend 29425 ?mysqlmysqld - 040 rt_sigsuspend 29428 ?mysqlmysqld - 040 rt_sigsuspend 29432 ?mysqlmysqld - 040 - 29433 ?mysqlmysqld - 040 rt_sigsuspend 29434 ?mysqlmysqld - 040 rt_sigsuspend 29436 ?mysqlmysqld - 040 rt_sigsuspend 29439 ?mysqlmysqld - 040 rt_sigsuspend 29442 ?mysqlmysqld - 040 rt_sigsuspend 29443 ?mysqlmysqld - 040 rt_sigsuspend 29445 ?mysqlmysqld - 040 rt_sigsuspend 29448 ?mysqlmysqld - 040 rt_sigsuspend 29450 ?mysqlmysqld - 040 rt_sigsuspend 29451 ?mysqlmysqld - 040 rt_sigsuspend 29453 ?mysqlmysqld - 040 rt_sigsuspend 29454 ?mysqlmysqld - 040 rt_sigsuspend 29455 ?mysqlmysqld - 040 rt_sigsuspend 29458 ?mysqlmysqld - 040 rt_sigsuspend 29462 ?mysqlmysqld - 040 rt_sigsuspend 29463 ?mysqlmysqld - 040 rt_sigsuspend 29464 ?mysqlmysqld - 040 rt_sigsuspend 29465 ?mysqlmysqld - 040 rt_sigsuspend 29468 ?mysqlmysqld - 040 rt_sigsuspend [...] So we come back to 3.23.49 wich have a glibc bug and memory eating bug but we can't upgrade to 3.23.51 :( Thanks again. David NB : this is the my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 # Start logging # log [...] - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: 'Mysql' [EMAIL PROTECTED] Sent: Friday, July 12, 2002 6:17 PM Subject: RE: Load problems with 3.23.51 Just a note: I tried MySQL 4.0.2 and it works fine. Seems to be only 3.23.51 built by MySQL itself that has the issue. Releases before, and now a release after (albeit a 4.0.x version) work fine. Sincerely, Steven Roussey http://Network54.com/?pp=e I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and tried 51. No dice. It does not handle load, CPU and the load average go through the roof. I'm using Red Hat Linux 7.2 and the official mysql binaries. It appears to be slow to connect, causing 0.5 to 1.0 second delay on connection. Using persistent connections from PHP does not make much of a difference. I thought it might be the hostname lookup changes so I chose skip-grant-tables. This doesn't actually skip the hostname lookup though and had no effect. Most queries are shorter than 1 second so this problem causes catastrophic problems by making queries last a multiple times longer, which make the number of concurrent queries jump exponentially. This is a bad thing. And sadly makes 3.23.51 unusable. - 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
3.23.51 bug ? ( was RE: Load problems with 3.23.51 )
Hi list, Same thing here, mysql 3.23.51 works well during about 2 hours then suddently load average grow up to 200 and more ... Load average is less than 4 with 3.23.49. We're using binary tar.gz non max versions from Mysql.com OS : Linux Red hat 7.2 kernel : 2.4.10 1 Go RAM 2 * Intel 1 Ghz During the load pic i've run a ps -eo pid,tt,user,fname,tmout,f,wchan and that's an extract of the output : [...] 29309 ?mysqlmysqld - 040 rt_sigsuspend 29347 ?mysqlmysqld - 040 rt_sigsuspend 29390 ?mysqlmysqld - 040 rt_sigsuspend 29391 ?mysqlmysqld - 040 - 29417 ?mysqlmysqld - 040 rt_sigsuspend 29418 ?mysqlmysqld - 040 rt_sigsuspend 29420 ?mysqlmysqld - 040 rt_sigsuspend 29423 ?mysqlmysqld - 040 rt_sigsuspend 29424 ?mysqlmysqld - 040 rt_sigsuspend 29425 ?mysqlmysqld - 040 rt_sigsuspend 29428 ?mysqlmysqld - 040 rt_sigsuspend 29432 ?mysqlmysqld - 040 - 29433 ?mysqlmysqld - 040 rt_sigsuspend 29434 ?mysqlmysqld - 040 rt_sigsuspend 29436 ?mysqlmysqld - 040 rt_sigsuspend 29439 ?mysqlmysqld - 040 rt_sigsuspend 29442 ?mysqlmysqld - 040 rt_sigsuspend 29443 ?mysqlmysqld - 040 rt_sigsuspend 29445 ?mysqlmysqld - 040 rt_sigsuspend 29448 ?mysqlmysqld - 040 rt_sigsuspend 29450 ?mysqlmysqld - 040 rt_sigsuspend 29451 ?mysqlmysqld - 040 rt_sigsuspend 29453 ?mysqlmysqld - 040 rt_sigsuspend 29454 ?mysqlmysqld - 040 rt_sigsuspend 29455 ?mysqlmysqld - 040 rt_sigsuspend 29458 ?mysqlmysqld - 040 rt_sigsuspend 29462 ?mysqlmysqld - 040 rt_sigsuspend 29463 ?mysqlmysqld - 040 rt_sigsuspend 29464 ?mysqlmysqld - 040 rt_sigsuspend 29465 ?mysqlmysqld - 040 rt_sigsuspend 29468 ?mysqlmysqld - 040 rt_sigsuspend [...] So we come back to 3.23.49 wich have a glibc bug and memory eating bug but we can't upgrade to 3.23.51 :( Thanks again. David NB : this is the my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 # Start logging # log [...] - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: 'Mysql' [EMAIL PROTECTED] Sent: Friday, July 12, 2002 6:17 PM Subject: RE: Load problems with 3.23.51 Just a note: I tried MySQL 4.0.2 and it works fine. Seems to be only 3.23.51 built by MySQL itself that has the issue. Releases before, and now a release after (albeit a 4.0.x version) work fine. Sincerely, Steven Roussey http://Network54.com/?pp=e I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and tried 51. No dice. It does not handle load, CPU and the load average go through the roof. I'm using Red Hat Linux 7.2 and the official mysql binaries. It appears to be slow to connect, causing 0.5 to 1.0 second delay on connection. Using persistent connections from PHP does not make much of a difference. I thought it might be the hostname lookup changes so I chose skip-grant-tables. This doesn't actually skip the hostname lookup though and had no effect. Most queries are shorter than 1 second so this problem causes catastrophic problems by making queries last a multiple times longer, which make the number of concurrent queries jump exponentially. This is a bad thing. And sadly makes 3.23.51 unusable. - 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
TCP TIME_WAIT strange problem ( LINUX mysql 3.23.49 )
Hi list, I've got lots of troubles with one of my mysql dedicated server. The problem occurs time to time ( during peac or idle time ). In fact mysql seem not to answer all connections. Netstat show several connections TIME_WAIT and show process_list only show 1 ou 2 queries running ... But on other way, 80% of the time, mysql works and answers well. At those moments netstat show lots of ETABLISHED; All connections are made through TCP/IP on a private LAN ( ip like 192.168.x.xx ). Server don't have access to internet. ifconfig show me that me eth0 config is clear : UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) Here is the config : uname -a Linux 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown Red Hat 7.2 2 * 1Ghz Intel 1 Go of ram 2 Go of swap Raid 5 scsi hdds Running mysql 3.23.49 from mysql team tar.gz binary. I've got only one MYISAM table like this : 8922 jun 19 07:43 Forums.frm 980150372 jui 3 17:24 Forums.MYD 85792768 jui 3 17:24 Forums.MYI mysql show table status; | Name | Type| Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 |252 | 980155640 | 4294967295 | 85792768 | 0 | | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 | 252 | 980155640 | 4294967295 | 85792768 | 0 | my.cnf : [mysqld] port= 3306 #socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= thread_cache_size=8 set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 I don't where to search. Is that an hardware problem ? Why not but the server isn't old ( only 3 mounths ). Linux ? Have a second mysq server with same hardware, same linux and same mysql and this one don't have any problem. Best rgds David - 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: TCP TIME_WAIT strange problem ( LINUX mysql 3.23.49 )
| | Sort_merge_passes| 16 | | Sort_range | 33150 | | Sort_rows| 993404 | | Sort_scan| 8522 | | Table_locks_immediate| 556209 | | Table_locks_waited | 113411 | | Threads_cached | 0 | | Threads_created | 65680 | | Threads_connected| 92 | | Threads_running | 87 | | Uptime | 132998 | +--++ 111 rows in set (0.00 sec) Thanks David - Original Message - From: David BORDAS [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 03, 2002 7:03 PM Subject: TCP TIME_WAIT strange problem ( LINUX mysql 3.23.49 ) Hi list, I've got lots of troubles with one of my mysql dedicated server. The problem occurs time to time ( during peac or idle time ). In fact mysql seem not to answer all connections. Netstat show several connections TIME_WAIT and show process_list only show 1 ou 2 queries running ... But on other way, 80% of the time, mysql works and answers well. At those moments netstat show lots of ETABLISHED; All connections are made through TCP/IP on a private LAN ( ip like 192.168.x.xx ). Server don't have access to internet. ifconfig show me that me eth0 config is clear : UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) Here is the config : uname -a Linux 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown Red Hat 7.2 2 * 1Ghz Intel 1 Go of ram 2 Go of swap Raid 5 scsi hdds Running mysql 3.23.49 from mysql team tar.gz binary. I've got only one MYISAM table like this : 8922 jun 19 07:43 Forums.frm 980150372 jui 3 17:24 Forums.MYD 85792768 jui 3 17:24 Forums.MYI mysql show table status; | Name | Type| Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 |252 | 980155640 | 4294967295 | 85792768 | 0 | | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 | 252 | 980155640 | 4294967295 | 85792768 | 0 | my.cnf : [mysqld] port= 3306 #socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= thread_cache_size=8 set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 I don't where to search. Is that an hardware problem ? Why not but the server isn't old ( only 3 mounths ). Linux ? Have a second mysq server with same hardware, same linux and same mysql and this one don't have any problem. Best rgds David - 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
Can't create a new thread (errno 12). ( 3.23.49 and Linux )
Hi list, just have this when connecting to my mysql dedicated server :( The server is like this : bi PIII 1 Ghz 1Go of ram Raid 5 scsi hdds Here is a tail of the error log : 020619 08:03:09 mysqld started /usr/local/mysql/bin/mysqld: ready for connections The system don't swap. Using Linux 2.4.7-10smp #1 SMP And mysql : 3.23.49 from tar.gz binary Here's the my.cnf : # The MySQL server [mysqld] port= 3306 #socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_cache_size=8 set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 I only have one database and one table ( MyIsam ). Forums.frm ( 8922 bytes ) Forums.MYD ( 935258712 bytes ) Forums.MYI ( 77645824 bytes ) Show staus say me that max_used_connection is 611. Did someone have an idea about this ? Thanks David - 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
Binary update log how to disable ?
Hi list, just a tiny question, i use mysql 3.23.51 ( tar.gz binary ) under linux. All is fine, mysql is started without a problem ... 020618 14:28:04 mysqld started /usr/local/mysql-3.23.51/bin/mysqld: ready for connections I can see in /usr/local/mysql-3.23.51 some files like : localdomain-bin.xxx I assume that it's the binary update log, but i haven't that with my old 3.23.48. So please, how can i disable this log ?? Rgds David - 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
Use mysql binary or build my own ?
Hi list, I've got a mysql dedicated server. Some piece of hardware : SMP : 2 * Intel PIII 1 Ghz 1Go of ram Scsi - Raid 5 Hdds Linux red-hat 7.2 This system run mysql 3.23.49 (tar.gz binary from Mysql.com ). I only have one MyIsam table with around 5 Millions of records ( around 1.2 Go disk space and 100 Mo indexes ) I plan to upgrade to 3.23.51. So, this is my question : Should i use a tar.gz binary mysql version or should i build my own ? I've read mysql docs like here : http://www.mysql.com/doc/L/i/Linux.html, and i know that if i want build my own mysql i should upgrade glibc and gcc ( currently have the bad 2.96 one ). Thanks David - 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: can't start mysql.server
Have a look to mysql error log located in /usr/local/mysql/data David - Original Message - From: Paul [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 12:15 PM Subject: can't start mysql.server hi, my machine is compaq alphastation DS20E, and the mysql verision is mysql-3.23.49-dec-osf5.1-alphaev6(binary package) after executed scripts/mysql_install_db, I copy the mysql.server from /usr/local/mysql/support-files to /usr/local/mysql/bin, then executed mysql.server start and some error message showed: /usr/local/mysql/bin/mysql.server: @HOSTNAME@: not found /usr/local/mysql/bin/mysql.server: my_print_defaults: not found # chown: unknown username mysql Starting mysqld daemon with databases from /usr/local/mysql/data 020604 17:02:58 mysqld end what can I do? Thanks for your help Best regards, Paul - 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
Re: processlist
Hi, Just connect to mysql and use this query : show processlit; http://www.mysql.com/doc/S/H/SHOW_PROCESSLIST.html You can also use slow query logs. http://www.mysql.com/doc/S/l/Slow_query_log.html David - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 1:07 PM Subject: processlist Hello all, Please can any one tell me how do i see all the queries in my mysql server in real-time? this is for checking witch ones are longer. regards tsa -- E-Mail: [EMAIL PROTECTED] Date: 04-Jun-2002 Time: 12:04:29 This message was sent by ME! -- - 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
Re: processlist
Think that even if mysql don't have connection, there are severals processes running ... For waiting connection for exemple. with 11.5 Mgs or so This memory is i think a buffer or a cache shared by each mysql process such as indexes for exemple ... David From: [EMAIL PROTECTED] To: Sergey S. Kostyliov [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 1:33 PM Subject: Re: processlist thanks Sergey, but that command line only shows the total processlist, it doesn't show exactlly witch queries are happening on my tables besids my one that says 'show full processlist'.The others are sleep , but if they are really, why is my mysql server running severls processes with 11.5 Mgs or so? Do i need some kind of compilation option to show me more info on the processlist? best regards, tsa On 04-Jun-2002 Sergey S. Kostyliov wrote: On Tuesday 04 June 2002 15:07, [EMAIL PROTECTED] wrote: Hello all, Please can any one tell me how do i see all the queries in my mysql server in real-time? this is for checking witch ones are longer. `mysqladmin -v proc` Or (in mysql client) mysql SHOW PROCESSLIST; regards tsa -- Best regards, Sergey S. Kostyliov [EMAIL PROTECTED] Public PGP key: http://sysadminday.org.ru/rathamahata.asc - 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 -- E-Mail: [EMAIL PROTECTED] Date: 04-Jun-2002 Time: 12:26:38 PGP Fingerprint = 5519 0CE9 2553 6877 A929 8270 91C0 D92D 4063 559E This message was sent by ME! -- - 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
Re: mysql-3.23.50
I think you'll find some usefull infos here : http://www.mysql.com/documentation/mysql/bychapter/manual_News.html#News-3.2 3.x Just one thing : 3.23.50 tar.gz and rpm is little different because Mysql team used a newer gcc version ... Bye David - Original Message - From: Inbal Ovadia [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 12:44 PM Subject: mysql-3.23.50 Hi all i have mysql-3.23.41 should i install 3.23.50? what are the differences between this versions? thanks - 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
a date for 3.23.50 declared as stable ?
Hi list, i know that 3.23.50 is a pre-released version. But if someone can tell me when this version will be declared as stable so i will upgrade my linux production servers running redhat 7.2 and gcc 2.96 with the tar.gz build mysql. Thanks David filter : sql,query - 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: Re: mysql uses 99% cpu under freebsd 4.3
SMP is System Multi Processor Like a server with 2 or more CPUs ... David - Original Message - From: webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 13, 2002 1:07 PM Subject: Re: Re: mysql uses 99% cpu under freebsd 4.3 So far I've experienced it on one SMP box and one single CPU box. I Silly question: what is SMP...? Best regards,=20 Eivind :-) sql, query - 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
Re: mysqldump
- Original Message - From: Harpreet Kaur [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 23, 2002 5:44 PM Subject: mysqldump Dear group members I did a mysqldump on my machine at home to backup a database. I saved the file to the /home/backup folder at my work and am trying to restore the database using mysql databasename /home/backup/backup_databasename.sql I get the following error: Cannot connetc to local mysql server through socket 'var/lib/mysql/mysql.soc (2) What is wrong. Please help. Regards, Harpreet kaur It seems that your mysql server isn't launched. Try ps -Al | grep mysql to see if there are somme mysql process up ... Hope this help David - 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: Select with Order By that don't use my INDEX :(
- Original Message - From: MySQL Newsgroup @[EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 2:55 PM Subject: Re: Select with Order By that don't use my INDEX :( Subject: Re: Select with Order By that don't use my INDEX :( From: Vic Cekvenich [EMAIL PROTECTED] === Better yet, remove the order by. Make it sort by the index, ie, create an index that matches your select. Vic Dr. Frank Ullrich wrote: Can someone tell me more about this index that matches select ? My query is always different : explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; 1Field28000 like category number in fact ... Field4 is a datetime Thanks David - 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
Select with Order By that don't use my INDEX :(
I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select with Order By that don't use my INDEX :(
- Original Message - From: Joseph Bueno [EMAIL PROTECTED] To: David BORDAS [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 19, 2002 10:34 AM Subject: Re: Select with Order By that don't use my INDEX :( David BORDAS a écrit : I'm using mysql 3.23.49 on Linux redhat dedicated server. Here is my query : mysql show index from MyTable; Table: MyTable Non_unique : 0 Key_name: PRIMARY Seq_in_index: 1 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 1 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: Index1 Seq_in_index: 2 Column_name : Field0 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL # Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 1 Column_name : Field2 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 2 Column_name : Field3 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL Table: MyTable Non_unique : 0 Key_name: questions Seq_in_index: 3 Column_name : Field4 Collation : A Cardinality: NULL Sub_part : NULL Packed: NULL Comment : NULL MyTable info : Field0 : int(10) unsigned primary key Field1 : int(10) unsigned Field2 : int(10) unsigned Field3 : int(10) unsigned Field4 : datetime Null: Yes default -00-00 00:00:00 ( but this field don't have null values ) Field5 : varchar(50); mysql explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY Field4 DESC LIMIT 0,20; ** table : MyTable type : ref possible_keys: Index1,questions key: questions key_len : 1 ref : const,const rows : 390 Extra : where used; Using filesort 1 row in set (0.00 sec) We can see that index questions which have Fields 2, 3 and 4 isn't use for the order by. Why ? Thanks in advance David - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Hi, MySQL 3.23.x doesn't use the index with DESC option of order by. It seems that it is fixed in 4.x but I haven't tested it yet. Hope this helps -- Joseph Bueno NetClub/Trader.com Arf, i've tried with an ASC query and index is used :( now i must wait a new mysql release :( Thanks - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
TIMESTAMP(14) or Bigint ??
Hi, i'd like to alter a table which have a date and time field so 3 Bytes for date and 3 Bytes for time. I'd table to add a new field to store date like this : MMDDHHMMSS. In fact, I can't use indexes on sql query like select with order by with 2 fields date + time and with one unique field i can. So, for this new field that i'll insert and update manually, should i use a Bigint which required 8 Bytes or a Timestamp(14) with 4 Bytes ? Timestamp looks great but, can i insert and update it manually ?? Thanks David - 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: TIMESTAMP(14) or Bigint ??
Sent: Tuesday, April 09, 2002 2:14 PM Subject: Re: TIMESTAMP(14) or Bigint ?? David, I could be wrong but since bigint isn't a date or time oriented data type I imagine this would be completely useless to you unless you are storing unix timestamps. In fact i'm looking to a way to store date and time in only one field and with the smallest space. I can take date and time with my C client client and then create a string like MMDDHHMMSS manually ... You also have the option of using datetime data types. I'm not quite sure what you are asking nor what difference it makes how big the field is in bytes. Timestamp and Datetime are pretty much the same except the first timestamp field of every row will automatically be timestamped when you do update's or inserts with a null value in the field. Ric. As we can see here : http://www.mysql.com/doc/n/o/node_367.html TIMESTAMP is 4 Bytes and DATETIME is 8 Bytes. So, 4 Bytes difference per 5 Millions records = a 20 MB bigger table ... But i think the feature timestamp field of every row will automatically be timestamped ... will anoy me. In fact the mos efficient way to store date and time will be 2 fields : date = 3 Bytes, time = 3 Bytes but when i'm doing sql query like this : select * from Table where field4 = 15 order by date , time; i can use index for the where but not for the order by, explain say me file sort So i thought to store date and time in only one field ... David - 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: TIMESTAMP(14) or Bigint ??
Sent: Tuesday, April 09, 2002 2:14 PM Subject: Re: TIMESTAMP(14) or Bigint ?? David, I could be wrong but since bigint isn't a date or time oriented data type I imagine this would be completely useless to you unless you are storing unix timestamps. In fact i'm looking to a way to store date and time in only one field and with the smallest space. I can take date and time with my C client client and then create a string like MMDDHHMMSS manually ... You also have the option of using datetime data types. I'm not quite sure what you are asking nor what difference it makes how big the field is in bytes. Timestamp and Datetime are pretty much the same except the first timestamp field of every row will automatically be timestamped when you do update's or inserts with a null value in the field. Ric. As we can see here : http://www.mysql.com/doc/n/o/node_367.html TIMESTAMP is 4 Bytes and DATETIME is 8 Bytes. So, 4 Bytes difference per 5 Millions records = a 20 MB bigger table ... But i think the feature timestamp field of every row will automatically be timestamped ... will anoy me. In fact the mos efficient way to store date and time will be 2 fields : date = 3 Bytes, time = 3 Bytes but when i'm doing sql query like this : select * from Table where field4 = 15 order by date , time; i can use index for the where but not for the order by, explain say me file sort So i thought to store date and time in only one field ... David - 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: Transfert Data from one DB to another
Yes but in fact i forgot to say that i have to change a value of a column, Ex dump all data from table where category = 2 and insert all this data into the other mysql with category = 4 and not 2 I think with too connection i can select all data then insert in new server ben delete on the old server with a C script but'll not be very efficient ... and not easy to make it working Look at mysqldump utility: http://www.mysql.com/doc/m/y/mysqldump.html Look at replication: http://www.mysql.com/doc/R/e/Replication.html i know mysqldump but in this case, i think this can't help me. Replication ? i don't know, i'll have a look Thanks David - 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
