Re: MYSQL is getting slow
On Thu, 9 Dec 2004 13:13:10 -0600 Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 09 December 2004 01:06 pm, Patrick Marquetecken wrote: and for ansewring Jeff Smelser i have installed mysql 4.x on linux and then dit from the commandline create database and then run the create table commands from snort. No idea.. you can do a show create table db.table to find out the engine type.. I ask because those should be myisam.. If they are. I cant see how memory is getting so high. If they are innodb.. I would highly recommend looking into converting them into myisam.. myisam was created for this type of work, would be a easier on the mem. i checked it, and its indeed myisam. Patrick Just some avenues to look at. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
On Thu, 9 Dec 2004 12:34:36 -0800 Dathan Pattishall [EMAIL PROTECTED] wrote: This is a very broad question, I sometimes do this myself. But to answer I know, but where must i begin ... the question to the best of my ability I have to ask a few. Are you using RAID? If so what RAID level? no raid What are you're my.cnf settings? standaard execpt for the bind address What type of Indexes are you using? On the larged table that has only 2 fields there are primary indexes on both fields. And each table has its ons primary index, and some other normal indexes. What does vmstat and iostat say? NIDS-console distfiles # free total used free sharedbuffers cached Mem:507508 497872 9636 0 2232 399024 -/+ buffers/cache: 96616 410892 Swap: 506036 62384 443652 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 2 62384 5044 2380 40163611 251187 522 15 6 79 0 1 0 62384 4412 2392 40229600 451113 1272 1859 17 5 79 0 0 2 62384 4504 1840 40274400 455672 1325 1977 16 8 76 0 2 0 62412 4960 1808 40140800 3959 0 1170 1866 35 13 52 0 What Filesystem are you using? ext3 What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Typically for most orginizations mysql dedicated on the box below is wicked fast, even when not optimized because of system cache etc. But if your running out of diskspace then that's a problem in itself. I got a lot of disk space left. -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:49 AM To: [EMAIL PROTECTED] Subject: MYSQL is getting slow Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Porting Tables Between Windows and Linux Version of MySQL
We have 2 distinct groups of developers - Windows vs. Linux. Our production server is going to run on Solaris. The issue is that all developers run there own instance of MySQL server Linux or Windows based and the same version 4.1.7. Also, each developer has the database for their particular project so everything from their MySQL server instance would be ported. As we move the systems to the Solaris production server is there any simple way to port all the work onto this platform (backup/restore, etc.)? Any help or weblink, reference material recommendations are appreciated.
return from load_defaults when .my.cnf not found
Can any body tell me what the 4.1.x client library loads_defaults function returns if it can't find the .my.cnf file? It's listed in the header as int load_defaults() but the documentation for it is atrocious. Even Paul fails to check/use the return in his MySQL definitive guide, just assumes it works. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. **
Re: spaces in table/column name
On Thu, 9 Dec 2004, sharif islam wrote: How mysql deals with spaces in table / column name? I am also using mysqlcc. If I try the following in the doesn't work. Creating table name with spaces from mysqlcc didn't give any error. But the following does: INSERT INTO 'tbl name with spaces' (col1, 'col name with spaces') VALUES(15,16); It really is a bad idea to use spaces and most non-alphanumeric characters in database, table and column names. Spaces are used as separators in most operating systems (recent versions of Windows and MacOS excepted) and although you may find you can create databases, tables and columns containing spaces if you enclose them in '' or quotes, you will sooner or later run into problems if you access these outside of MySQL or using MySQL running on a different system. If you must put in a space, why not use the '_' underscore character? This is legal in all operating systems I know of (MS-DOS doen't like the plain '-' hyphen, for example). Hope this helps, Andy # include std-disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
You are probably a little short of RAM for that size db. Lack of swap may bite you as well. How big are your keys? Pat... - Original Message - From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 10, 2004 10:48 AM Subject: Re: MYSQL is getting slow At 09:49 AM 12/9/2004, Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick Patrick, Try running Analyze Table to get MySql to rebalance the key distribution. See http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html Make sure you have plenty of disk space and time before running it. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to rebuild indexes in InnoDB
Hi, any one give me an idea how to rebuild indexes in InnDB database. database users are complaining that response time is slow. right now what I am doing is dropping indexes on table and recreating them. After that Database is working fine for some days and again it is giving problem. dropping and recreating indexes taking lot of time. any alternative Thanks in advance. Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Increasing the InnoDB log file size
Hi, I need to increase the innodeb_log_file_size parameter, but when I set it higher, mysql don´t starts and give the follow erro: 041210 13:27:40 mysqld started InnoDB: Error: log file ./ib_logfile0 is of different size 0 100663296 bytes InnoDB: than specified in the .cnf file 0 134217728 bytes! 041210 13:27:40 [ERROR] Can't init databases 041210 13:27:40 [ERROR] Aborting 041210 13:27:40 [Note] /usr/local/libexec/mysqld: Shutdown complete 041210 13:27:40 mysqld ended Does anybody knows how can I do it? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Increasing the InnoDB log file size
Hi, First shutdown the mysql cleanly. #mysqladmin shutdown then drop log files in data directory. restart mysql. it will create new log files. Thanks Anil NOC -Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 11:08 PM To: [EMAIL PROTECTED] Subject: Increasing the InnoDB log file size Hi, I need to increase the innodeb_log_file_size parameter, but when I set it higher, mysql don´t starts and give the follow erro: 041210 13:27:40 mysqld started InnoDB: Error: log file ./ib_logfile0 is of different size 0 100663296 bytes InnoDB: than specified in the .cnf file 0 134217728 bytes! 041210 13:27:40 [ERROR] Can't init databases 041210 13:27:40 [ERROR] Aborting 041210 13:27:40 [Note] /usr/local/libexec/mysqld: Shutdown complete 041210 13:27:40 mysqld ended Does anybody knows how can I do it? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to rebuild indexes in InnoDB
ALTER TABLE tablename TYPE = InnoDB; Will rebuild the indexes all at the same time. -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 9:06 AM To: [EMAIL PROTECTED] Subject: How to rebuild indexes in InnoDB Hi, any one give me an idea how to rebuild indexes in InnDB database. database users are complaining that response time is slow. right now what I am doing is dropping indexes on table and recreating them. After that Database is working fine for some days and again it is giving problem. dropping and recreating indexes taking lot of time. any alternative Thanks in advance. Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection during query when using SSH tunneling
Hello, I have a MySQL database hosted with my ISP. They provide direction as well as thru SSH tunneling. I can very well connect to it diredctly from any app but I prefer to use SSH tunneling. So I use Putty to create the SSH tunneler and then connecting to the local port. Now the problem, is that whenever I execute a long query or something that returns big resultset, i get an error - Lost connection during query. If I use direct connection - everythings fine. Is this a known issue? Is this a bug with Putty? Regards, Karam __ Do you Yahoo!? Send holiday email and support a worthy cause. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing the InnoDB log file size
Anil, First shutdown the mysql cleanly. #mysqladmin shutdown then drop log files in data directory. restart mysql. it will create new log files. Do you know if is there a risk of loosing any data? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Increasing the InnoDB log file size
if mysql shutdowns cleanly. then there is no chance of loosing data. for safe side take compleate database backup. -Original Message- From: Ronan Lucio [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 11:39 PM To: Anil Doppalapudi; [EMAIL PROTECTED] Subject: Re: Increasing the InnoDB log file size Anil, First shutdown the mysql cleanly. #mysqladmin shutdown then drop log files in data directory. restart mysql. it will create new log files. Do you know if is there a risk of loosing any data? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection during query when using SSH tunneling
- Original Message - From: Karam Chand [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 10, 2004 1:08 PM Subject: Lost connection during query when using SSH tunneling Hello, I have a MySQL database hosted with my ISP. They provide direction as well as thru SSH tunneling. I can very well connect to it diredctly from any app but I prefer to use SSH tunneling. So I use Putty to create the SSH tunneler and then connecting to the local port. Now the problem, is that whenever I execute a long query or something that returns big resultset, i get an error - Lost connection during query. If I use direct connection - everythings fine. Is this a known issue? Is this a bug with Putty? Regards, Karam I don't have a lot of information for you, mostly just educated guesses. I think you are experiencing a timeout due to the length of the query. It seems reasonable to me that there would be a limit on how long a query could run before it got killed; otherwise, if it was looping, you'd never get break out of the loop. If I was writing the framework in which your query was executing, I'd want a query to timeout automatically if it had been running over a set amount of time. I'd set that value at a reasonable number and give the user some way to change it if the default was too high or too low. Unfortunately, I don't know for certain that this is what's happening but it seems pretty reasonable to me. I use WinSSH to do the same sort of tunnelling you are doing and I have never timed out yet. Then again, I haven't run any really long queries or built any really large result sets either so maybe I've just been lucky. I had a look at the WinSSH software and I don't see any kind of a timeout setting in it. Therefore, I suspect that the timeout is being set at the server itself. Unfortunately, I'm not sure where to start looking. Perhaps if you search on 'SSH' in the manual, you will find that MySQL has a setting that affects timeouts for SSH tunnels; if not, it might be a more global setting somewhere on the server, perhaps in Apache. Sorry I couldn't be more help. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost connection during query when using SSH tunneling
-Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 12:08 PM To: [EMAIL PROTECTED] Subject: Lost connection during query when using SSH tunneling So I use Putty to create the SSH tunneler and then connecting to the local port. Now the problem, is that whenever I execute a long query or something that returns big resultset, i get an error - Lost connection during query. If I use direct connection - everythings fine. Putty has an option in the Connection category of the configuration for Seconds between keepalives. It's set to zero (off) by default. If you set it to 0 number, putty will send a null packet at the server to try and keep the connection alive. Have you tried that? -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost connection during query when using SSH tunneling
I am not sure of Putty as I am using plink.exe that is the SSH tunneler for tunnel. The thing is that I call up plink.exe from my app to create an ssh tunnel and then set the correct values for mysql_real_connect() in my app. What I am doing is that I am bundling plink.exe with my app and then ask the user for SSH info. Depending upon that I create a plink.exe process and then connect my app to the SSH process. I referred to Putty as many people seem to be using it and putty usese plink.exe which is the back end for it. Should I look into some options like what you suggested? Regards, Karam --- Adams, Pat 006 [EMAIL PROTECTED] wrote: -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 12:08 PM To: [EMAIL PROTECTED] Subject: Lost connection during query when using SSH tunneling So I use Putty to create the SSH tunneler and then connecting to the local port. Now the problem, is that whenever I execute a long query or something that returns big resultset, i get an error - Lost connection during query. If I use direct connection - everythings fine. Putty has an option in the Connection category of the configuration for Seconds between keepalives. It's set to zero (off) by default. If you set it to 0 number, putty will send a null packet at the server to try and keep the connection alive. Have you tried that? -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Jazz up your holiday email with celebrity designs. Learn more. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost connection during query when using SSH tunneling
-Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 2:17 PM To: Adams, Pat 006; [EMAIL PROTECTED] Subject: RE: Lost connection during query when using SSH tunneling I am not sure of Putty as I am using plink.exe that is the SSH tunneler for tunnel. The thing is that I call up plink.exe from my app to create an ssh tunnel and then set the correct values for mysql_real_connect() in my app. What I am doing is that I am bundling plink.exe with my app and then ask the user for SSH info. Depending upon that I create a plink.exe process and then connect my app to the SSH process. I referred to Putty as many people seem to be using it and putty usese plink.exe which is the back end for it. Should I look into some options like what you suggested? plink can use putty's saved sessions, so you could set one of those up. Unfortunately it doesn't look like there's a command-line option to set the timeout or keepalive settings of plink. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems Moving from 3.23 to 4 (Win2k)
I made a copy of my entire Mysql 3.23 data directory and moved it on to a box with a fresh install of MySQL 4. While the NT service starts up fine (I'm using 4.0.22-nt-max), I can't login. I know that there are supposed to be mods made to the mysql database tables, but I thought there was at least a minimum compatibility. The only way to get access to anything is to shut down MySQL, put in the default table that came with the install. What am I doing wrong here? -- A. Clausen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcc
I'm obviously missing something. I can use mysqlcc to change data in a row of a table and save it. But, if I try to insert a row, add data and save it, it never gets saved. What am I overlooking? -- /\ ASCII RIBBON Robin Lynn Frank \ / CAMPAIGN Director of Operations X AGAINST Paradigm-Omega, LLC / \ HTML MAILhttp://www.paradigm-omega.com/ = Spambots visit http://paradigm-omega.net/cgi-bin/custmail.cgi = Don't mind me. I am having a bad (day|week|month|year). signature.asc Description: This is a digitally signed message part
Why is this simple query so slow?
Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec)
Re: Problems Moving from 3.23 to 4 (Win2k)
What errors are being displayed when you attempt to log in? Is anything being written to the error log itself? A. Clausen wrote: I made a copy of my entire Mysql 3.23 data directory and moved it on to a box with a fresh install of MySQL 4. While the NT service starts up fine (I'm using 4.0.22-nt-max), I can't login. I know that there are supposed to be mods made to the mysql database tables, but I thought there was at least a minimum compatibility. The only way to get access to anything is to shut down MySQL, put in the default table that came with the install. What am I doing wrong here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this simple query so slow?
Have you tried optimizing or run an analyze table command on this table? Aaron wrote: Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL is getting slow
What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Nothing is proprietary when it comes to mysql, you can turn on the slow query log which is the first thing, do you have it turned on? What is the slow query set to? Are any showing up? If so, then it must be time to add some indexes until they go away. The next thing you can always do is turn on the general query log, and that will pretty much log everything, and then you can see everything that is being done. I've use a similar product that worked with snort in the past, it worked fine for us, but snort is boring. You need to pull the data right from the switch, using something like netflow and logging like 600mb/s of netflow logs to mysql is much more fun! Donny -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 3:03 AM To: [EMAIL PROTECTED] Subject: Re: MYSQL is getting slow On Thu, 9 Dec 2004 12:34:36 -0800 Dathan Pattishall [EMAIL PROTECTED] wrote: This is a very broad question, I sometimes do this myself. But to answer I know, but where must i begin ... the question to the best of my ability I have to ask a few. Are you using RAID? If so what RAID level? no raid What are you're my.cnf settings? standaard execpt for the bind address What type of Indexes are you using? On the larged table that has only 2 fields there are primary indexes on both fields. And each table has its ons primary index, and some other normal indexes. What does vmstat and iostat say? NIDS-console distfiles # free total used free sharedbuffers cached Mem:507508 497872 9636 0 2232 399024 -/+ buffers/cache: 96616 410892 Swap: 506036 62384 443652 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 2 62384 5044 2380 40163611 251187 522 15 6 79 0 1 0 62384 4412 2392 40229600 451113 1272 1859 17 5 79 0 0 2 62384 4504 1840 40274400 455672 1325 1977 16 8 76 0 2 0 62412 4960 1808 40140800 3959 0 1170 1866 35 13 52 0 What Filesystem are you using? ext3 What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Typically for most orginizations mysql dedicated on the box below is wicked fast, even when not optimized because of system cache etc. But if your running out of diskspace then that's a problem in itself. I got a lot of disk space left. -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:49 AM To: [EMAIL PROTECTED] Subject: MYSQL is getting slow Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this simple query so slow?
In the last episode (Dec 10), Aaron said: The query below takes around 8 seconds, and returns 3253 rows. Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72; 3253 rows in set (8.00 sec) Explain says: Bad word-wrapping fixed: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+-+---+-+--++--++-+ | Offers_To_Buy | 1 | subcategory |1 |subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 |subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | +---++-+--+-+---+-+--++--++-+ 11 rows in set (0.00 sec) As Donny said, your FORCE INDEX(scdd) is pessimizing you. But even moving to the subcategory key won't help you much, since you're still going to be doing 3253 random reads into your table to fetch 'id'. Try creating an index on (subcatID,ID). This will let mysql retrieve all the data it needs from an index range scan. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlcc
MySQL CC seems to be discontinued. Nowadays I prefer SQLyog (http://www.webyog.com) Karam --- Robin Lynn Frank [EMAIL PROTECTED] wrote: I'm obviously missing something. I can use mysqlcc to change data in a row of a table and save it. But, if I try to insert a row, add data and save it, it never gets saved. What am I overlooking? -- /\ ASCII RIBBON Robin Lynn Frank \ / CAMPAIGN Director of Operations X AGAINST Paradigm-Omega, LLC / \ HTML MAILhttp://www.paradigm-omega.com/ = Spambots visit http://paradigm-omega.net/cgi-bin/custmail.cgi = Don't mind me. I am having a bad (day|week|month|year). ATTACHMENT part 2 application/pgp-signature name=signature.asc __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spaces in table/column name
We have always found using anything other than alphanumeric characters and maybe an occasional underscore for clarity is a bad idea. It will always come back to bite you at some point. Try not using spaces or punctuation in table or column names and your life will be easier. White space and punctuation are frequently used as delimiters in other programs, os's and applications, so when you use them in elements other than strings you often limit the portability (i.e. import and export) of your structures. I hope you find this information valuable. Pat... Patrick Sherrill CocoNet Corporation SW Florida's 1st ISP - Original Message - From: sharif islam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 09, 2004 4:07 PM Subject: spaces in table/column name How mysql deals with spaces in table / column name? I am also using mysqlcc. If I try the following in the doesn't work. Creating table name with spaces from mysqlcc didn't give any error. But the following does: INSERT INTO 'tbl name with spaces' (col1, 'col name with spaces') VALUES(15,16); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
At 09:49 AM 12/9/2004, Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick Patrick, Try running Analyze Table to get MySql to rebalance the key distribution. See http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html Make sure you have plenty of disk space and time before running it. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eliminating special string quoting requirements, for non single-quote characters (ANSI/ISO)
Hi all, Does anyone know fo a way to run the server in more of a string-quoting standards compliant mode, so that a generic SQL string quoting function only has to escape single quotes, instead of also '', '\', and '\0'? I've tried the ANSI_QUOTES startup option, but that apparently only changes the treatment of double quotes, and requiring single quotes for strings... '\' for example still needs a non-standard treatment. What I'd like to be able to do is have \,\\,\0 characters treated as any other so that statements become portable. Thank you, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Porting Tables Between Windows and Linux Version of MySQL
The only gotcha I'm aware of is that you must set the *nix server to use case-insensitive table names, since on windows the SQL table references didnt need to match case. I'll leave you to you own devices to find that documentation (I just dont have it handy, sorry) select 8 from testtable || select 8 from testTable == okay on windows select 8 from testtable || select 8 from testTable == error on *nix. As far as moving the tables over, just a straight file copy of data/ should be fine. my.ini - my.cnf is the only stand-out other translation I can think of. Best, ken Alan Scott - Yahoo Acct wrote: We have 2 distinct groups of developers - Windows vs. Linux. Our production server is going to run on Solaris. The issue is that all developers run there own instance of MySQL server Linux or Windows based and the same version 4.1.7. Also, each developer has the database for their particular project so everything from their MySQL server instance would be ported. As we move the systems to the Solaris production server is there any simple way to port all the work onto this platform (backup/restore, etc.)? Any help or weblink, reference material recommendations are appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Increasing the InnoDB log file size
Hello, I had the same issue myself, i read the manual and there says that you have to stop mysql, then change the parameter in my.cnf, then i moved to another directory the log file just to be cautious, and then restarted mysql. Everything went right. Best regards Alejandro -Mensaje original- De: Ronan Lucio [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Diciembre de 2004 11:38 a.m. Para: [EMAIL PROTECTED] Asunto: Increasing the InnoDB log file size Hi, I need to increase the innodeb_log_file_size parameter, but when I set it higher, mysql don´t starts and give the follow erro: 041210 13:27:40 mysqld started InnoDB: Error: log file ./ib_logfile0 is of different size 0 100663296 bytes InnoDB: than specified in the .cnf file 0 134217728 bytes! 041210 13:27:40 [ERROR] Can't init databases 041210 13:27:40 [ERROR] Aborting 041210 13:27:40 [Note] /usr/local/libexec/mysqld: Shutdown complete 041210 13:27:40 mysqld ended Does anybody knows how can I do it? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing the InnoDB log file size
Anil and Oropeza, if mysql shutdowns cleanly. then there is no chance of loosing data. for safe side take compleate database backup. Ok, thank you very much for your help. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding Connection Context
[EMAIL PROTECTED] wrote: CONTEXT USE Example: Do *you* really understand what this feature is supposed to do? Can you explain it to us in (low-level) detail? No, MySQL doesn't have a feature like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is this simple query so slow?
Aaron, Three things. 1. Do a show create table Offers_To_Buy 2. And why in the world would you have force index(scdd) when your where clause is on subcatID? If you can explain what you are trying to do, I'm sure many people can help you get exactly what you are looking for. 3. Why so many indexes? Do you search on every one of those fields? If not, then you are probably wasting diskspace and speed. Donny -Original Message- From: Aaron [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 8:12 PM To: [EMAIL PROTECTED] Subject: Why is this simple query so slow? Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
