Re: Error on MySQL-5.0
Moon's Moon's Father escreveu: You may execute mysql_fix_privileges_table script to upgrade all of your mysqld. I did it, but the problem persists... :-/ +-+---+--++ | Table | Op| Msg_type | Msg_text | +-+---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_fc2a_0`" to fix it! | | information_schema.ROUTINES | check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_fc2a_0`" to fix it! | | information_schema.TRIGGERS | check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_fc2a_0`" to fix it! | | information_schema.VIEWS| check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_fc2a_0`" to fix it! | +-+---+--++ Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cluster
Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error on MySQL-5.0
Hi, I installed MySQL-5.0.67_1. When I execute "CHECK TABLE information_schema.COLUMNS FOR UPGRADE" I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_43b6_0`" to fix it! | ++---+--++ The same occurs for tables ROUTINES, TRIGGERS and VIEWS. If I execute "REPAIR TABLE COLUMNS" I got: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I "GRANT ALL ON information_schema.* TO 'root'@'localhost';" and got the same error: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I backed to command shell and " mysqlcheck -u root -p --repair information_schema". It gives me no error, but the problem persists: When I execute "CHECK TABLE information_schema.COLUMNS FOR UPGRADE" I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do "REPAIR TABLE `/var/tmp/#sql_43b6_0`" to fix it! | ++---+--++ Any help would be appreciate. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC Compatibility
Hi, Suppose a simple query: SELECT client_name as name FROM clients WHERE client_id = $client_id Using JDBC 5.0.8 it returns column name as "name", that it's the expected for me. Using JDBC 5.1.7 it returns column name as "client_name", although I have asked for an alias. Is it right? I can't change the queries of my whole application to be compliant with JDBC 5.1.7. It's thousands+ queries. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham, I was under the impression that in FreeBSD 5, you didn't need to change the kernel, just the entry in /boot/loader.conf... Even if you use kern_securelevel="1"? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham, /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Did you changed the values of MAXDSIZ in the kernel file? I use this: options MAXDSIZ=(1536UL*1024*1024) for MySLQ can you up to 1,5 Gb of RAM memory. I realy don´t know if it will solve your problem, but, if MySQL is tring to use more RAM memory than the permited, it seems to be the case. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to generate efficient backups?
Hi, Yesterday I had a problem in a InnoDB table that I needed to DROP and reimport the table. When I tryed to reimport the table, most of the data was lost. So, I´ve tryed to reimport the data from the backups (created via mysqldump) and these datas were still corrupted. So, it brings to my mind some doubts: 1) Is it common? Is difficult to have to trust in database that can´t gives you a real security about the data ingrity. 2) Is there a way to look for the corrupted tables in the whole database, automaticaly? I´d like to create some monitor where it would send me an alert when a table is corrupted 3) Is there a way to check the backup to be sure it´s OK? I can´t depends on a backup that I´m not sure it´s OK. I´m running MySQL-4.1.7 I´ve had some problems with big MyISAM tables, but in these cases REPAIR and OPTIMIZE table have solved. Yesterday the problem happened in a InnoDB table. Any help would be appreciated, Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to compose index?
I have following query: SELECT SQL_NO_CACHE users.user_name assigned_user_name, accounts.* FROM accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where (accounts.assigned_user_id='1') AND accounts.deleted=0 ORDER BY phone_office asc LIMIT 620300,20 In your opinion, what group of indexes should i use to gain maximum performance out of this query? Table users index_id: id Table accounts == index_id: assigned_user_id, deleted index_phone: phone_office After that, do an EXPLAIN in the query. I´m not sure about index_phone will help you. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
that depends because SugarCRM doesn't delete anything from DB just marks item as deleted using this field. Do you have an index for deleted column? It should help. Can you apply any more filter in the users table (like date)? You are requesting all rows. no, because purpose of this query is to list all contacts, and using LIMIT contacts are then paginated on web site. Pay attention that a "ORDER BY name" would be executed in all rows, regardless the LIMIT clause. How many rows do you have in the users table? We are not sure how many user will be in our DB, but my asumption is that there should not be more than 40 - 50, but don't take my word on it... Hmmm, so you should do better job in the accounts table. If this query is used frequently, perhaps filtering by only one user should help. Will it be enough to use only my-huge.cnf file or we can do some more tweaks? I guess so. The bellow documentation has helped me a lot, although it´s for 3.23 version: http://www.tnt.uni-hannover.de/print/plain/soft/database/MySQL/Docs/manual_Performance.html Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Marko, Do you have an index "deleted + name" on the accounts table? How many rows have "deleted=0"? Can you apply any more filter in the users table (like date)? You are requesting all rows. How many rows do you have in the users table? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Martijn, I have a table with 5,000,000 records that takes about 1 second to show the results. All rows? No. The queries return an average of 30 rows. I just wanted to say that the problem isn´t the table size, but the query X indexes used. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Marko, I have a table with 5,000,000 records that takes about 1 second to show the results. Perhaps your queries aren´t optimized properly. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF
Hello, I´m using MySQL-4.1.7 and I need to create a UDF. For all I have understood, I need to create it in C/C++ sintaxe. My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)? Do I need to learn C/C++ to have a UDF? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: general question
Octavian, I am using a MySQL database on a web site, and I would like to know what happends if someone searches in the database using a form, but after a few seconds MySQL starts the query, that user hit the "Stop" button of the browser. Will MySQL continue its searching and also create the cache, or it will stop automaticly? Interactivity between the webserver and the database is server-side. So, when the user clicks on the stop button, it should simply ignore the response client-side. In other words: The server will complete his job and send you the result but your browser will ignore it. It´s just my thoughts. I´m not sure about it, but the logic is this. If it will also stop, can I do something to let it continue searching in order to create the cache and the next time another visitor searches for the same thing it will get the results from the cache? If you use query cache in server side (on database or on your programing language), yes. It should works. If you use cache base on proxy or in the client browser. Once the result was ignored, there is no page to cache. I hope this help you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about query optimization
Eric, Can you send us the actual show indexes from table and explain output that isn't shortend? Thank you for answer my question. Actually, the real query and tables are much more complex than the data in the previous message. A just tryed to optimize the information for you better understand the trouble. I think found what´s happening. A SELECT WHERE city = 1 returns more rows thant a full table scan in the table "front" (the real states that appear in the front page). So, it seems MySQL choose the second option, once it has less rows to optimize. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about query optimization
Hello, I´m using MySQL-4.1.7, and I have a query that seems, even using where clause, an specific table isn´t being optimized properly: I have four tables: real_state: cod, name, city, ag_cod, sell agency: cod, name, ag_status front: cod, rs_cod, ag_cod photo: cod, rs_cod These table have the following indexes: real_state: cod (pri) city ag_cod agency: cod name front: cod rs_cod ag_cod photo cod rs_cod When I EXPLAIN the query: EXPLAIN SELECT front.rs_cod, photo.cod, real_state.descr FROM real_state, agency, front, photo_foto WHERE real_state.city = 1 AND real_state.sell = 1 AND front.rs_cod = real_state.cod AND photo.rs_cod = front.rs_cod AND agency.cod = real_state.ag_cod AND ag_status = 'A' It shows me (in a short): tablekeyrows ====== frontrs_cod 2085 real_statecod1 agencycod1 photo rs_cod 1 But the trouble is: If I execute: SELECT COUNT(*) FROM real_state, front WHERE real_state.city = 1 AND real_state.cod = front.rs_cod Returns: 271 So, Why the index front.rs_cod isn´t being used? Any help would be appreciated, thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Mathias, To drop multiple IP, you can use distinct : mysql> SELECT year, month, day, group_concat(distinct ip),count(*) AS access -> FROM access -> WHERE year = 2005 -> AND month = 5 -> GROUP BY year, month,day -> ORDER BY year, month, day; +--+---+--+---++ | year | month | day | group_concat(distinct ip) | access | +--+---+--+---++ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +--+---+--+---++ 3 rows in set (0.00 sec) Thank you very much for your attention. It also answer my question, but I think the Shawn´s tip is more optimized. Any way, I appreciate your help. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several lines of the same day (one per IP), plus the total. I need that every year-month-day-ip be counted as 1. And I need this total per day. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hello, I have a table where is saved all site´s access: access id year month day weekday hour minute ip Any column has multiple lines, and I have the follow query that returns the amount of access per day of month: SELECT year, month, day, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day ORDER BY year, month, day Now, I need to do the same query, but for unique access, in other words, with DISTINCT year, month, day, ip. I tryed to use the query: SELECT year, month, day, ip, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day, ip ORDER BY year, month, day but it returns me several lines of the same day and the amount of access per IP, and I need the amount of access from different IPs. Could anybody help me? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Shawn, Thank you very much for your tips. I´ll do that. Ronan - Original Message - From: <[EMAIL PROTECTED]> To: "Ronan Lucio" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, January 18, 2005 6:36 PM Subject: Re: Doubt about Performance "Ronan Lucio" <[EMAIL PROTECTED]> wrote on 01/18/2005 02:53:39 PM: > Shawn, > > Your tips are realy good but I can´t use cache neither for > queries nor for pages because our site serves hundreds of > clients, each one with your own code make a different > query, returning different rows. > > It would be too many queries to be cached. > I know I didn´t say it in the previous message. > > Thank you for the help, > Ronan > > You would run your "queries" against your cached data using your web site's application code. You can create additional arrays to act as indexes against the data so that you will not need to do a full "array scan" every time. Load your data into your arrays in the order of the "customer" parameter, then you have already isolated each customer's data to a contiguous portion of the data array. Trust me, if you do it right (sorted and "indexed" data + fast lookup routine), it should be 10-20 times faster than trying to read through the same data from the database each and every time ([array search + array seek + looped scan] instead of [SQL parsing + query processing + net lag + data transfer time]). I do not recommend doing this to every page on your site, only to those pages that handle the highest traffic and only for data that doesn't change quickly (on the order of several changes per hour or per day, not several changes per second). For rapidly changing data, data you don't need often, or unpredictable queries, read the data from the database. It saves you no time to take the effort to cache that kind of data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Sasha, > I assume you mean "has serious performance impact" when you say "weight". If > this is not what you meant, please correct/clarify. Yes, you´re right. >> >> 1) Is a SELECT DISTINCT over 5,000 records a weight >> query? (Supposing it has about 20 different option the the >> DISTINCT key). > > This query will most likely result in a creation of a temporary table with 20 > columns and a key over all of them that will have no more than 5000 records, and > will take 5000 attempted inserts to populate. Assuming that your WHERE clause > is ok, this query should take no more than 3 seconds or so on modern hardware. > However, this could be bad if you are doing this frequently and there is other > activity going on. On the other hand, the query cache could save you. If it does > not, consider creating and maintaining a summary table. Hmmm, I wanted to say the SELECT DISTINCT should return about 20 lines. The table should have about a million records, but WHERE clause should filter it to up to 5,000 and such query should be in the site´s home... :-/ Perhaps work with summaries would be a better choice. >> >> 2) Is SELECT ORDER BY RAND() over 1,500 records >> a weight query? > > Does the table have only 1,500 records, and is it going to stay that way? Are > you selecting only a few reasonably sized columns? If yes, unless you are Yahoo > or Google, you'll do fine on modern hardware - this query under those > curcumstances should take the order of maginitude of 0.01 s. However, if you > have more records in the table, and the WHERE clause is not optimized, things > could get bad, and this time the query cache does not save you. The table should have about a million records, but WHERE clause should filter it to up to 5,000 and such query should be in the site´s home. I don´t know if it can put the site in performance troubles or if it´s paranoia of mine. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about Performance
Greg, Thank you very much for your help. > An index the full size of the distinct key is probably very quick especially > if it only has 20 distinct values. OTOH, if you already know you have a > small number of distinct values, could you just store them normalized in a > different table? Actually, it is. It has some tables: features = - id - description groups = - id - description products == - id - description - group_id - feature_id And I´ll use a SELECT like this: SELECT DISTINCT features.description FROM products LEFT JOIN features ON (products.feature_id = features.id) WHERE products.group_id = $var_group AND products.features_id > 0 The table products should have a million of records, but the filtered query should goes over a thousand records (filtered by group_id) and return about 20 distinct lines. It´s my situation but I don´t know how heavy such query is for the database and how viable such query is. Thanks in advance, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about Performance
Hello, Trying to keep the database and the application fast, I´d like to clearify my mind about it. 1) Is a SELECT DISTINCT over 5,000 records a weight query? (Supposing it has about 20 different option the the DISTINCT key). 2) Is SELECT ORDER BY RAND() over 1,500 records a weight query? I need to put these two queries in the first page of our site. So, I´ve been worried if it can slow down our site in the pics. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function
Hello, Is there a way to create my own function on MySQL (4.0.18)? We have a site where a product can have it´s price in differents currencies, so, I need to make a query that returns the correct product´s price. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about query optimization
Hello, Supposing I have 2 tables: product == - id - name price = - id - product_id - sell_price - rent_price If I want to make a SELECT for the products having the rent_price between 300,00 and 500,00, so I use the query: SELECT rent_price FROM product LEFT JOIN price ON (product.id = price.product_id) WHERE rent_price BETWEEN 300.00 and 500.00 My doubt is if the table product will be optimized. Will optimization process be done over all rows from the product table or only the rows related to the price table? In other words: If table price has other columns and LEFT JOIN is needed anyway, even that would be better to move the columns sell_price and rent_price to the product table? My table has so many columns, and, for structural and maintainance reasons it would be better to divide the columns in two tables, but I can´t compromisse the application performance because of 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
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: 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]
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: 4.0 vs 4.1
Greg, > Never had table corruption, though I have had index corruption in myisam > tables many times on sound hardware. Usually, this is due to an unclean > shutdown, though I have seen it happen other times when the server is > supposedly running with no problems. A repair table tablename always > fixes the problem. I agree with you, but my worry is in cases that IÂm not accessible. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 vs 4.1
Jeff, >> Both, because I have tables that have few inserts and too few >> updates/deletes, >> like states, cities and so on, but I plan transform every table in InnoDB >> to avoid corruption issues. > > Nothing here has slowed down. A few innodb stuff I use, has, but thats it.. I > run linux on a x86. Nothing I probably couldnt iron with some variable > changes. You might want to look into that first. > > what kind of corruption issues have you had? All my syslog daemons log to > mysql, couple hundred inserts per min, and I never get these 'issues'. These > are all on myisam tables. Actually, I´ve never had such problem. I´m just afraid of it because I´ve read some issues about corruption in MySQL tables and the own MySQL Manual says that exist a command just to repair MyISAM tables (myisamchk - http://dev.mysql.com/doc/mysql/en/Table_maintenance.html). Once I dealing with hangs problems with MySQL + FreeBSD-4.x, I´m afraid that the table could crashs when MySQL hangs... :-/ Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 vs 4.1
>> Once I plan to run FreeBSD-5.3 + MySQL-4.1.7 in my server, it can >> affect me. > > I may have missed it, but myisam, or innodb? Both, because I have tables that have few inserts and too few updates/deletes, like states, cities and so on, but I plan transform every table in InnoDB to avoid corruption issues. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 vs 4.1
Jeff, >> Has anyone any benchmark between 4.0 and 4.1? >> >> MySQL website says that version 4.0 has some performance >> improvements but I´ve heard that MySQL-4.0 is 20% faster >> than MySQL-4.1. > > As far as what? Thats a very general statement. How do you even know that > affects you? The following thread appeared in the FreeBSD list: --- > Here are the results of the brief benchmarks that I ran. I posted the > best and worse results out of running each test 5 times. > > - > System: > > Dual Opteron 244s > RAM 1GB > 3 x 36GB U320 SCSI RAID-5 on Adaptec 2120s > > - > OS: > > FreeBSD 5.3-RELEASE FreeBSD 5.3-RELEASE #2: Mon Nov 15 10:35:13 > PST 2004 i386 > > --- > MySQL 4.1.7 WITH_OPENSSL BUILD_OPTIMIZED > --- > master# super-smack update-select.smack 30 1 > Query Barrel Report for client smacker > connect: max=15ms min=4ms avg= 8ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index30 3 0 4365.04 > update_index30 4 0 4365.04 > > master# super-smack update-select.smack 30 1 > Query Barrel Report for client smacker > connect: max=12ms min=0ms avg= 7ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index30 3 0 4799.58 > update_index30 3 0 4799.58 > > master# super-smack select-key.smack 30 1 > Query Barrel Report for client smacker1 > connect: max=10ms min=7ms avg= 9ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index60 3 0 10614.75 > > master# super-smack select-key.smack 30 1 > Query Barrel Report for client smacker1 > connect: max=10ms min=6ms avg= 7ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index60 4 0 10666.14 > > --- > MySQL 4.0.22 WITH_OPENSSL BUILD_OPTIMIZED > --- > master# super-smack update-select.smack 30 1 > Query Barrel Report for client smacker > connect: max=13ms min=9ms avg= 11ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index30 3 0 4839.98 > update_index30 3 0 4839.98 > > master# super-smack update-select.smack 30 1 > Query Barrel Report for client smacker > connect: max=22ms min=0ms avg= 17ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index30 3 0 4963.34 > update_index30 2 0 4963.34 > > master# super-smack select-key.smack 30 1 > Query Barrel Report for client smacker1 > connect: max=19ms min=5ms avg= 9ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index60 2 0 12387.57 > > master# super-smack select-key.smack 30 1 > Query Barrel Report for client smacker1 > connect: max=15ms min=0ms avg= 7ms from 30 clients > Query_type num_queries max_timemin_timeq_per_s > select_index60 2 0 13201.30 > > -- > > It seems 4.0.22 is faster in each benchmark. Around a 20% increase. I > wonder why this is? --- Once I plan to run FreeBSD-5.3 + MySQL-4.1.7 in my server, it can affect me. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opinions: notes field ideal length?
Chris, > Quick question. What's a reasonable length for a notes-type field? I > couldn't really find any guidelines on the web so I'm thinking of just > setting it to 65,535. Or is that ridiculously long? For performance reasons, the data type should be as short as possible. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank, Wouldn´t it because the client driver (ODBC version)? Perhaps it´s faster in the local server because it uses a mysql-4.1.7 client. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Hmmm... The MySQL-client is 4.1.7, too? I realy don´t know if it make some difference, but perhaps it does. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank, If you provide more informations like the query and how many rows it returns, it should be easier help you. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump from 4.1 to 4.0 - It´s solved
Hi, Now I found this line the MySQL Manual: " If you run mysqldump without the --quick or --opt option, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database. As of MySQL 4.1, --opt is on by default, but can be disabled with --skip-opt. " So, I got to import the data with the following mysqldump line: mysqldump --skip-opt --add-drop-table --default-character-set=latin1 dbname > dbname.sql thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump from 4.1 to 4.0
Hi,
I need to import a dump from a mysql-4.1 to mysql-4.0.
I have already read the MySQL Manual page but I didn´t
find the answer, even importing the data with the option
--default-character-set=latin1.
When executed mysql < dump.sql, it happend an error
because mysql-4.1 generates the dump including:
CREATE TABLE xyz {
} ENGINE=MyISAM DEFAULT CHARACTER SET=latin1;
but when I import this dump, mysql-4.0 gives an error with
this sintaxe.
So, to import the dump I had to remove the text
" DEFAULT CHARACTER SET=latin1" from the CREATE
TABLE statements.
It imported the dump file successfully but now we having
accentuation problems.
It seems that the words were recorded with a wrong accentuation
character.
Any help would be appreciated.
thanks,
Ronan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0 vs 4.1
Hi, Has anyone any benchmark between 4.0 and 4.1? MySQL website says that version 4.0 has some performance improvements but I´ve heard that MySQL-4.0 is 20% faster than MySQL-4.1. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Ajay, Could you send the error messages (.err file in the mysql dir) and your my.cnf file? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tunning Problem
Sasha, > Ronan: > > InnoDB complains it cannot allocate memory. With your configuration you are > likely to run out of memory: > > You are telling InnoDB to allocate at least 256 MB + 20 MB for the buffer pool. > On top of that, you are telling MyISAM to use 384 MB for the key buffer. So this > is already over 700 MB. Then you start connecting. Each time you connect, you > have some overhead on the order of a few megabytes. Times 55, and you can easily > eat up the remaining 300 MB. Also, mysqld is probably not the only process on > the system. Hmmm, you´re right. Thanks for clearify my thoughts. A good tunning seems to be a hard task. The MySQL manual page says: --- # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB --- (http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html) for a computer with 2 Gb of memory, and in some tunning docs that I´ve been looking I´ve found this: --- If you have much memory (>=256M) and many tables and want maximum performance with a moderate number of clients, you should use something like this: shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M &---(http://www.tnt.uni-hannover.de/print/plain/soft/dat abase/MySQL/Docs/manual_Performance.html) If you know a good documentation about it or if you have suggestions how can I improve my configuration, please tell me. Thank you very much -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tunning Problem
Hi All, I´m having a trouble where my server is falling to it's knees when a certain number of connections are match. Well, in a short, I configured the my.cnf files to accept up to 120 connections. When the server receives about to 55 connections, it hangs the connections and I don´t get me even enter the MySQL interactive mode. I see in the list people configuring the MySQL tu accept up to 1000 connections and my server don´t get to hold 100... :-/ I think some queries of the applications was badly designed, but even thus I need to optimize it. My server is a P4-2.4 with 1 Gb RAM. MySQL 4.0.18 The my.cfn file has the following sets: -- [mysqld] port=3306 socket=/tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= sort_buffer_size=512K set-variable= record_buffer=512K set-variable= read_buffer_size=512K set-variable= max_connections=120 set-variable= max_connect_errors=50 set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= myisam_sort_buffer_size=8M set-variable= thread_cache=16 log-bin server-id = 1 #set-variable = bdb_cache_size=64M #set-variable = bdb_max_lock=10 #skip-innodb innodb_data_file_path = ibdata1:10M:autoextend #innodb_data_home_dir = c:\ibdata #innodb_log_group_home_dir = c:\iblogs #innodb_log_arch_dir = c:\iblogs #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=16 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=8M set-variable= sort_buffer=8M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=8M set-variable= sort_buffer=8M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout -- The error message is: -- 041125 12:13:14 InnoDB: Started /usr/local/libexec/mysqld: ready for connections. Version: '4.0.18-log' socket: '/tmp/mysql.sock' port: 3306 InnoDB: Fatal error: cannot allocate 81920 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 322902064 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=520192 max_used_connections=55 max_connections=120 threads_connected=55 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 515615 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -- Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb corrpution. Very Urgent
Calos, > Well i didn´t change nothing at no config file. Any suggestions? > thanks. If you didn´t change anything, it´s supposed to be time to you configure the my.cnf file. A better tunning should solve your problem. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb corrpution. Very Urgent
Carlos, > 041124 14:13:18 Innodb: fatal error: cannot allocate 2147500032 bytes of > innodb: memory with malloc! total allocated memory > innodb: by inndodb 16975556 bytes. Operating system errno: 8 > innodb: Cannot continue operation! > innodb: check if you should increase the swap file of ulimits > innodb: of your operating system > innodb: On freeBSD check you have compiled the OS with > innodb:a big enough maximum process size It seems a tunning problem. Perhaps if you make some changes in your my.cnf file MySQL could run again. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
Carlos, Give us more details about our system: What are the table types you´re using? What are the configs in your my.cnf file? Ronan - Original Message - From: "Carlos Augusto" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 23, 2004 3:41 PM Subject: Mysql Performance Well that´s me again with a new problem. I am runnig another database with 7gb of data and this db can´t stop. All the time we have queries being inserted by a plain text file and all the time customers get information of this database. I obvserved that for a insert query is taking about 5 to 15 seconds for each insert. I saw some variables like: slow_query that output me a number of 1388 slow queries. I am new in mysql and database stuff and i have a task to improve mysql´s performance. I need help since in a simple Pentium 4 the same operations are almost 10 times higher(in time of inserting a querie) and this machine that is too slow for inserting is an dual xeon, 4gb ram and 500gb hard disk. I aprecciate if some one has a solution for improving this server performance. Thanks. Carlos -- 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]
Subqueries X Joins
Hello, I wondering about Subqueries X Joins. What is the fastest technic? I´ve read the Manuals and I found some texts saying Joins are faster and other saying Subqueries are faster. Well, if both so. When Subqueries are faster and when Joins are faster? If this question has been answered before (I didn´t found it in the archives), please tell me the thread subject or the link for the documentation. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Strange error in DELETE query
Hi, I think I found out what was wrong. ColfFusion has a tag that prevents SQL Injection. Probably was removing anything after comma. Without using the code works perfectly. So, I had to create a UDF to remove everything except digits and commas. Thanks in advance, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange error in DELETE query
Michael, > What are you talikng about? Queries don't halt on the first row matched. > For example: It´s my thought, too. But it isn´t happen in my MySQL Server. Now, doing the same tests you did I got the same results of you. Well, I´ll inspect my code again looking for some error that I didn´t see, yet. thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange error in DELETE query
Jay, > It is not a bug, just say it out loud > "AND row_id is 2 OR 5 OR 7" > > Once the OR condition is satisfied once, the query will halt. The problem is that if I use OR in the where clause, MySQL wont use the indexes in the row_id column. One important thing that I forgot to say is I run a SELECT with the same where clause: SELECT * FROM table WHERE client_id = 1 AND row_id IN (2,5,7) and it returns me three rows, thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange error in DELETE query
Hi, I have a MySQL-4.0.18 installed on a FreeBSD system. When I run the follow query: DELETE FROM table WHERE client_id = 1 AND row_id IN (2,5,7) only the first record is deleted. Am I doing something wrong or is it a MySQL bug? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE LOW PRIORITY
Gleb, > So, when it returns, all updates will be commited and select statement will return the correct result. It´s exactly what I´d wanted to know. Thank you very much, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE LOW PRIORITY
Jay, > From TFM, "If you specify the LOW_PRIORITY keyword, execution of the > UPDATE is delayed until no other clients are reading from the table." Yes, I had alread read it, but it only specify when the data will be commited, not what will be returned. Let´s change the question: Before the that be commited (until no other clients are reading from the table) the results for SELECTs will be either the data on disk or the date on the cache? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE LOW PRIORITY
> What is "the correct result"? The correct result is the data in the updated column after commit. For example, if I have: Table1 = - id - name INSERT INTO Table1 (id, name) VALUES (1, 'AAA') UPDATE LOW PRIORITY Table1 SET name = 'BBB' SELECT name FROM Table1 Will it return BBB? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE LOW PRIORITY
Hi All, I have o little (I think) doubt: If I use a query UPDATE LOW PRIORITY and right after I execute a SELECT in the same table/column. Will I receive the correct result or only after MySQL commit the data? Any help would be appreciated, Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Shawn, Thank you very for your answer. Actually, I thought that a main SELECT couldn´t be filtered by the WHERE clause refered to a field in a LEFT JOIN. Now, looking better in the JOIN documentation I see this issue. Thank´s, Ronan This is a very FAQ: SELECT t1.* FROM TABLE_1 t1 LEFT JOIN TABLE_2 t2 ON t1.id = t2.table1_id WHERE t2.id is null Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Ronan Lucio" <[EMAIL PROTECTED]> wrote on 10/27/2004 10:12:42 AM: > Hi, > > I have two tables: > > TABLE_1 > === > - id > - name > > TABLE_2 > === > - id > - table1_id > - name > > How could I make a select on table_1 that returns me only the > rows that don´t have any reference in table_2? > > Any help would be appreciated. > > Thank´s, > Ronan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Help with query
Hi, I have two tables: TABLE_1 === - id - name TABLE_2 === - id - table1_id - name How could I make a select on table_1 that returns me only the rows that don´t have any reference in table_2? Any help would be appreciated. Thank´s, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN in the same table
Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? Perhaps I´d get this with sub-selects but I´m using MySQL-4.0.18. Any help would be appreciated. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join and Performance Question
Hi, Supposing we have for tables: One master table (student) and three independent tables. There is no relationship between them. Each of the three table have a relationship with table student. City | Teacher | TestLocal | === | = city_id | teac_id | test_id city_name | teac_name | test_name Student === stud_id stud_name city_id teac_id test_id When the application shows the student screen, it´s need to make 4 SQL queries. Is it wise to make only one query even don´t having any relations ship between the three ones? SELECT city_name, teac_name, test_name FROM City, Teacher, TestLocal WHERE city_id = Student.city_id AND teac_id = Student.teac_id AND test_id = Student.test_id AND Student.stud_id = $cod_student Or is it better to make 3 distinct SELECT? What should be better for the application and DB? Thanks, Ronan
Re: Help with query
Shawn > SELECT CityName, Count(ClientID) as ClientCount > FROM City > INNER JOIN Client > on City.CityID = Client.CityID > GROUP BY CityName > HAVING ClientCount > 30; Thank you very much, It should solve by problem... :-) Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hello, A have two tables: City: CityID CityName Client: ClientID ClienteName CityID How can I make a SELECT that returns me only the cities that have more than 30 (example) clients? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving data
> I want to copy data from one table to another. However I need to do this > carefully because I haven't got much of space left. I was thinking of maybe > selecting data from every 10 rows of the old table, inserting it into > the new table, then deleting those rows from the old table. > > Could someone help me out with the SQL for this please? Or tell me if > there's a better way of doing this? CREATE TABLE new_able SELECT * FROM old_table; Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server hanging
Hello, I had a problem with our MySQL server-4.0.18 where it suddenly stoped working. Even a KILL command didn´t killed the mysqld process. I looked for any error message in the file /usr/local/mysql/hostname.err, but I couldn´t see any error message about such time. Does anyone knows what could make MySQL hangs? And where can I find error messages that could tell me what happend? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: huge innodb data files
Mayuran, Well, I´m not a MySQL expert, but I think that a good configuration in the my.cf file can make it better. Ronan - Original Message - From: "Mayuran Yogarajah" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 26, 2004 5:37 PM Subject: huge innodb data files Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- 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]
Query Help
Hi, I sorry for I neither didn´t find the information that I need in the documentation nor found the correct words for a search in the mailing archivers. I have a query like this: SELECT cod, descr FROM table WHERE AND cod != 7 AND cod != 10 AND cod != 13 AND cod != 14 AND cod != 15 AND cod != 20 AND cod != 25 AND cod != 30 AND cod != 31 ORDER BY descr Is there a SQL command to make this query cleaner? I find something like: WHERE cod NOT IN (7,10,13,14,15,20,25,30,31) Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
Blake, > Query > UPDATE table SET 1 = '1' WHERE id = '1' > > |#1064 - You have an error in your SQL syntax. Check the manual that > corresponds to your MySQL server version for the right syntax to use > near '1 = '1' WHERE id = '1'' at line 1 Is the column 1 string type? If it´s numec type. You should use SET 1=1 instead of SET 1='1' Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting works fine but I cant update.
> I am having a update problem, I have a table that has 28 columns they > are name 1-28. I am able to insert data into the table. When I try to > update info into the table it does not work. Any ideas? 1) What are the QUERY you´re using to UPDATE datas? 2) What is the error message? 3) What are the version of MySQL you are using? 4) Do you have GRANT privileges to UPDATE data in such table? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table types
Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting in a auto-inremental column
Hi, Can I insert specifying the data into a auto-incremental column? Explain better: We have a database (MySQL-4.0.17) that I need to syncronize with a off-line software. If a client was inserted in the off-line software, I´ll need to import the client datas to the central database. The client ID is an auto-incremental column, but if I leave MySQL set the client ID, it´s supposed to be different from the off-line software. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple questions!!
Kirti, > (1) I am going to setup a MySQL Client machine on a different Server. I > notice I can't install > a Client MySQL from Binary MySQL distro. Is it correct? I´m not sure, but I think you´ll have to install altmost the complet MySQL package on the client machine. Perhaps because the libraries. But you´ll can use MySQL normaly from a client machine. For instance, you can install MySQL Control Center in a Windows station and execute queries normaly. > (2) On a Client MySQL machine, it it necessary to START MySQL or just > connecting to > the MySQL Server does the job? Just connect and MySQL does the job. > (3) On a Client MySQL machine, is there any need to install support for > Perl/DBI/DBD or > support comes from the MySQL Server? Support comes from the MySQL Server. > (4) After I installed Perl/DBI/DBD, the cursor was still on "cpan>". How do > I get out of this > shell? Forexample: When one is in mysql shell, QUIT takes out of the mysql > shwll. It seems you´re still in the CPAN interactive shell. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database structure
Hi, I´m working in a project of a database that should be grow to more than 25,000,000 of clients. For all I´ve read in MySQL manual pages it´s too much records to place in only one table. So, my main doubt is how to divide it. I divide the client table in few tables according with the different kinds of clients. Even getting some duplicated records and getting some difficulties importing and exporting clients from one table to another it should take the database load cooler. But, I think I´ll need to place all logins and access levels in the same table. Would it be a problem? Any idea how can I deal with it? I´m thinking to use InnoDB tables. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary Key
Hi, Is the Primary Key Column mandatory? Supposing: If I have two tables: Clients and Cars, and a third table Clients_R_Cars, that is a relationship between Clients and Cars. I only need to know what cars the clients have. So, I just need to two columns "CliCar_ClientsID" and "CliCar_CarsID", the will be my index keys. Even thus do I need to create a Primary Key Column "CliCar_ID"? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb + full text searching
> Hi there, I am aware this isnt possible. I would like the power of Innodb, > but one of my applications also requires the boolean search within blocks of > text, how can i do this efficiently ? I know it isn´t so efficient, but you can use: SELECT * FROM yourtable WHERE text_column LIKE '%word%' Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Standard of Column Names
Harald, > I don't see the necessity of the latter naming scheme since > > SELECT cod, name, description FROM car > > can also be written as > > SELECT car.cod, car.name, car.description FROM car Do you know how it would be about portability? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt about TINYINT type
Paul, > I don't know what "minor than" means Sorry by my english. I´d like to say "less than" (or something like this). > , but TINYINT is a single-byte > type. The range for TINYINT is -128 to 127, and the range for > TINYTINT UNSIGNED in 0 to 255. OK, I understood it, but I didn´t understand why is there an option TINYINT(n) Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Standard of Column Names
Ruslan, > IMHO: > 1) Table name as prefix is unnecessary for me. It's norwegian notation > which I hate. > 2) Also I recomend look into ANSI SQL standard for reserved keywords. > I've got experience of porting DB from MySQL(allow some keywords) to > another DB, it's pain. Thank you your answer. Do you know where can I find a documentation about ANSI SQL Standards and what is the ANSI SQL standard implemented by MySQL? I ask it because I´ll prefer to work with column types that are in the ANSI SQL Stantard like INTEGER instead of MEDIUMINT. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt about TINYINT type
Hi, If I create a column with the type TINYINT(2) UNSIGNED. Will the content can have the value minor than 510 (like a number 499), or will the content can have two values minor than 255 (like 11)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Standard of Column Names
Hello, I´m doing the planing for an application that will use MySQL as database. So, I´d like to know your opinions about the standard for the column names. Supposing that I should create a table named car. Is it better to have either the column names (cod, name, description) or (car_cod, car_name, car_description)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
> The timestamp is when the query was written to the log. You will have to > subtract the query time to get when the query began. Unless you have queries > that are running for long durations the timestamp and actual time should be > close. OK, so, slow query shouldn´t be my problem, because there is no slow query logged when the system is consuming too many bandwidth. > Are you performing reads or writes to this disk? The machine is only database server. It has many write disk process, but all of them are mysql task like temporary tables and update queries. > Have you captured or monitored the CPU and memory usage during this time? Yes, it seems to be fine. The main problem seems to be the large outgoing traffic there I captured with MRTG graphics. And I know that this isn´t an attack or anything else because the MRTG show the traffic going out from the database server and going in to the application server. Running a top in the application server, it show just the coldfusion process using the CPU and in the database server, just the mysql using de CPU. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
Victor, > Do you have any around the approximate time? I know the time that the problem occurred. In the slow-log, it shows the line "Time". Is it line the hour the query ran? > The timestamp will be will the > query was written to the slow log and how long it took. What are the > symptoms that lead you to believe that it is a slow running query? Actually, I don´t know if the problem´s origem is a slow query. I´d like to know what query was running in such moment to see if exist a query receiving too many data. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Process Monitoring
Victor, > Are you logging slow queries? If so, have you looked in the slow query log? Yes, I am. But, it shows many of them and no one at this time. Or, it doesn´t show slow queries when the problem is occurring. I restarted the server 1 hour ago and after I did it, the server seems to be OK. I´m thinking that MySQL can be losting itself in the memory usage. Is it possible? But, I can´t understand what can made so many traffic going out from the server... :-/ Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Process Monitoring
Hi, We have a MySQL-Server-4.0.17 installed in our enterprise working fine... :-) Some times our application takes too bandwidth from the database server. So, I´m trying to figure out what (perhaps what select) is taking so manu bandwidth. SHOW FULL PROCESSLIST shows me the follow: mysql> show full processlist; +--+-+---+-+-+-- +---+---+ | Id | User| Host | db | Command | Time | State | Info | +--+-+---+-+-+-- +---+---+ | 3303 | filmeemcasa | 200.247.127.132:43570 | filmeemcasa | Sleep | 74 | | NULL | | 4226 | filmeemcasa | 200.247.127.132:33898 | filmeemcasa | Sleep | 0 | | NULL | | 4276 | filmeemcasa | 200.247.127.132:47532 | filmeemcasa | Sleep | 9 | | NULL | | 4280 | filmeemcasa | 200.247.127.132:47918 | filmeemcasa | Sleep | 11 | | NULL | | 4281 | filmeemcasa | 200.247.127.132:47933 | filmeemcasa | Sleep | 315 | | NULL | | 4308 | filmeemcasa | 200.247.127.132:50119 | filmeemcasa | Sleep | 72 | | NULL | | 4312 | filmeemcasa | 200.247.127.132:50284 | filmeemcasa | Sleep | 0 | | NULL | | 4315 | filmeemcasa | 200.247.127.132:50287 | filmeemcasa | Sleep | 71 | | NULL | | 4329 | spv | 200.247.127.132:54006 | spvvideo| Sleep | 76 | | NULL | | 4330 | filmeemcasa | pardal:3180 | NULL| Sleep | 2163 | | NULL | | 4331 | filmeemcasa | pardal:3184 | NULL| Sleep | 64 | | NULL | | 4333 | filmeemcasa | 200.247.127.132:54025 | filmeemcasa | Sleep | 72 | | NULL | | 4338 | filmeemcasa | 200.247.127.132:54640 | filmeemcasa | Sleep | 10 | | NULL | | 4339 | root| localhost | NULL| Query | 0 | NULL | show full processlist | +--+-+---+-+-+-- +---+---+ 14 rows in set (0.00 sec) but I can´t get to see the select that is being executed. Does anyone knows how can I find it? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search in Longtext fields
Hi, Is it possible to make a search for a word in a longtext column from a InnoDB database? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and searchs
Hi, We have a FULLTEXT search in our site. The is after we migrate the tables from MyISAM to InnoDB, this search stop working. Looking at the MySQL Documentation (http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html) I´ve seen that it´s support for MyISAM tables online. So, how can I make a search for a word in a LONGTEXT column? Will it be fast? Any help would be appreciated, Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Patrick, > >From your description, I assume you already have data in an existing version > 4 database that has existing table structures that are different from your > 3.23 dump. I didn´t have datas, but I had already run the mysql upgrade script. Because the original version of the MySQL in Linux distribution is 3.23.58 and choose to upgrade it first. > Alternatively, if you don't need the data from the existing database drop it > and recreate it. Then run your dump against it, followed by the necessary > alter table statements. May I drop even the columns_priv and db databases and after restore I gain all fuctionalities and performance from MySQL-4.0.x? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: starting mysql daemon.... help me !!!
The user that mysql run as, has permission to write in the /var/run/mysql dir? Ronan > muhammad alqama wrote: > > hi > > > > **(plz someone help me urgently)** > > > > i have tried the procedure mentioned below but of no avail. > > this is the exact message in .err file > > > > "can not start server : bind on unix socket : permission denied > > do you have another mysql server running on socket /var/run/mysql/mysql.sock ? > > /usr/libexec/mysqld : shutdown complete > > mysqld ended > > " > > > > ---> i am trying to run the server as root > > ---> there is no such file as /var/run/mysql/mysql.sock > > > > now what to do > > > > regards > > alqama > > > > > > > Hi! > > a few things to try: > 1.- you should never start the server as root, instead try starting > mysql as mysql user > 2.- do you have write permission under /var/run/mysql? > > Best Regards > > -- > > |...| > | _ _|Victor Medina M | > |\ \ \| | _ \ / \ |Linux - Java - MySQL | > | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | > | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | > |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | > ||Cel: +58-412-8859934 | > ||geek by nature - linux by choice | > |...| > > > -- > 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]
mysqldump
Hi All, I have another question: I have a .sql file from a mysqldump did in a mysql-3.23.x Is there a way to restore it in a mysql-4.0.17? I´m trying to do it but the mysql return some errors like: # mysql -u root -p < ./dump-database.sql Enter password: ERROR 1050 at line 192370: Table 'columns_priv' already exists So I cut the lines from the sql file that tried to create the existent table and executed the command again. I happened for some tables (columns_priv, db). Now mysql is returning the follow error: # mysql -u root --password=littlesohh < ./dump-database-bh.txt ERROR 1136 at line 7: Column count doesn't match value count at row 1 It is happening in the db table. OK, the table (db) struct is different from the 3.23 version, but, how is the better way to 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: MySQL on Linux
> Uhm, > what are you talking about?!? When I put our site on a Linux system, apache stop working when it´s logfile get major than 2 Gb. I was afraid of it´d happen with MySQL, too. > Linux has no such limitation. > you can grow files as large as you like. > right now I have an InnoDB dbase with Mysql on a linux > system and the file is over 60 GIGS in size! Great!!! So, I don´t need to worry about it... :-) Thanks Dan, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Linux
Hi All, I always worked with MySQL on FreeBSD systems. Now I need to install am MySQL with InnoDB and MyISAM tables in ta Linux RH system. So, do I need to care about the Linux file size limitation of 2 Mb? Or MySQL deal this situation with Linux FS? In other words, will my MySQL stop working when the database get major then 2 Mb? Or such situation won´t happen? thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf in MySQL-Server-4.0.17
Hi All, I´m trying to do some customization in a MySQL-Server-4.0.17 to gain a better performance. We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD on a FreeBSD-5.2.1 box dedicated for MySQL. My trouble is when I create the my.cnf file and start MySQL. MySQL don´t stat. the /usr/local/mysql/aguia.err file shows: - 040219 16:34:46 mysqld started mysqld in malloc(): error: allocation failed mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=1044480 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 335471 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. mysqld in free(): error: recursive call Fatal signal 6 while backtracing 040219 16:34:46 mysqld ended - My my.cnf file is: - [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1 innodb_data_home_dir = /usr/local/mysql/ innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/ innodb_log_arch_dir = /usr/local/mysql/ innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout - Without the /etc/my.cnf file the MySQL works fine, except in some moments that it get slow. Does anyone knows what could be wrong? Any help would be Appreciated. Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf on MySQL-Server-4.0.17
Hi All, I´m trying to do some customization in a MySQL-Server-4.0.17 to gain a better performance. We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD on a FreeBSD-5.2.1 box dedicated for MySQL. My trouble is when I create the my.cnf file and start MySQL. MySQL don´t stat. the /usr/local/mysql/aguia.err file shows: - 040219 16:34:46 mysqld started mysqld in malloc(): error: allocation failed mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=1044480 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 335471 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. mysqld in free(): error: recursive call Fatal signal 6 while backtracing 040219 16:34:46 mysqld ended - My my.cnf file is: - [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1 innodb_data_home_dir = /usr/local/mysql/ innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/ innodb_log_arch_dir = /usr/local/mysql/ innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout - Without the /etc/my.cnf file the MySQL works fine, except in some moments that it get slow. Does anyone knows what could be wrong? Any help would be Appreciated. Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Martijn, > > > 21 threads are idle. > > > > But, what could make so many threads get idle? > > Connecting, but don't doing anything. > > > Shouldn´t the queries just be executed, return the results and > > be closed? > > If you don't close your connection, what do you expect? > > > Does this mean some kind of congestion or it is normal? It´s a website, so it´s possible that the users closed his browsers before the query finishes? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW PROCESSLIST
Jeremy, > On Wed, Feb 18, 2004 at 04:01:57PM -0300, Ronan Lucio wrote: > > Hi All, > > > > I executed a SHOW PROCCESSLIST command and > > it returned me 39 rows which 21 of than show "Sleep" > > in the column "Command". > > > > What does this tell me? > > 21 threads are idle. But, what could make so many threads get idle? Shouldn´t the queries just be executed, return the results and be closed? Does this mean some kind of congestion or it is normal? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW PROCESSLIST
Hi All, I executed a SHOW PROCCESSLIST command and it returned me 39 rows which 21 of than show "Sleep" in the column "Command". What does this tell me? Thank´s, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Question About Replication
Hi All, A short question: If I have a slave MySQL Server (updating via logs). Supposing the master MySQL (for any reason) goes down. The users start using (and updating) the slave database. When the master MySQL goes up. Does it will update it´s data from the slave database? Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql control centre - How to view all the records for a table?
> Hi, Hi, > Can anyone help me that i want to view all the record from a table, but in mysql control centre, i only can view the first 1000 records, can anybody teach me how to make it can view for all the rest of the records? Click with the right button in the table´s name -> "Open table" - "Return Limit" Type the limit that you want and click OK. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Troubles in MySQL migration
Hello, I tried to migrate a database from a MySQL-4.0.14 to another one hardware with MySQL-4.0.17 installed. The database has InnoDB tables and I just executed a: # mysqldump -A --user root --password=my_password > dump_db.txt in the old server (MySQL-4.0.14) and # mysql -u root -p < dump_db.txt in the new server. I needed to create the user by hands and every thing seems to be there by when I put the MySQL to work, it gaves me several errors. Actually, I don´t know to say exactly what errors were happened, but the site looked to be crazy... some parts were loaded perfectly and some other, not. OK, May I forgot some step in the database migration? Thank´s Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Admin Question
Hello, We have an MySQL-4.0.14 Server installed and sometimes the it takes 90% from CPU. So, my question is: Is there a way to know what is taking so many CPU process? What query is causing such problem? Thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problem
Mike, > Ronan, > If your queries are only returning fewer than a hundred rows, and > if every join is indexed, I would have expected it to take only a second or > two at most. I´m sorry, but I´m not so good in SQL queries, rather joins. If my queries are taking about 15 seconds instead of 1 second. Can it be because some installation or running option? Today I saw another situation: These same SELECT statements are taking about 0.10 seconds... :-/ In other words: When a single execute a certain SELECT, the query returns in 0.10 second, but, when more the 30 users execute a SELECTs simultaneously, the same query returns in about 15 seconds. Would you know what can cause such problem? I think it may be because some MySQL configuration/parameter or a FreeBSD (5.1) limitation, perhaps with threads. > Have you put "Explain" in the front of the query to see if it is > using an index on all the joins? > Your "( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180" > won't use an index for f.dtrelease. You need to rewrite it so the column > name is not inside a function and it must be by itself on one side of a > comparison operator. Example: f.dtrelease >= Date_Sub(CurDate(), interval > 180 days). I'm not sure about the syntax but you get the idea. OK, I catched the idea. I´ll do it. Now, about EXPLAIN queries: The queries seems to be returning about 425 rows for the first index the 1 row for the last two indexes. Thank´s, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problem
Mike, > Ronan, > You haven't given us much to go on. I´m sorry. > Is this application running on a web server? Yes, but in a diferent machine connecting via TCP/IP. > 1) How many rows are your queries returning? (on average) and how long does > it take? Hmmm... Most of the queries are limited in 10 rows. > 2) How many connected users do you have? mysql> show status; +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 64| | Aborted_connects | 2 | | Bytes_received | 212069034 | | Bytes_sent | 262377934 | | Com_admin_commands | 29410 | | Com_alter_table | 8 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 100 | | Com_change_master| 0 | | Com_check| 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 2 | | Com_create_table | 11| | Com_delete | 1583 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 2563489 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_purge| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 138419| | Com_set_option | 58392 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 4 | | Com_show_fields | 450 | | Com_show_grants | 0 | | Com_show_keys| 9 | | Com_show_logs| 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 3 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 42| | Com_show_innodb_status | 0 | | Com_show_tables | 59| | Com_show_variables | 43| | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 0 | | Com_update | 38089 | | Connections | 61| | Created_tmp_disk_tables | 3572 | | Created_tmp_tables | 26901 | | Created_tmp_files| 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 16| | Handler_delete | 141469| | Handler_read_first | 1138 | | Handler_read_key | 58809799 | | Handler_read_next| 46576628 | | Handler_read_prev| 430340| | Handler_read_rnd | 413436| | Handler_read_rnd_next| 166792550 | | Handler_rollback | 1 | | Handler_update | 3691731 | | Handler_write| 12902461 | | Key_blocks_used | 93763 | | Key_read_requests| 240598220 | | Key_reads| 82544 | | Key_write_requests | 21429673 | | Key_writes | 16389083 | | Max_used_connections | 36| | Not_flushed_key_blocks | 6 | | Not_flushed_delayed_rows | 0 | | Open_tables | 346 | | Open_files | 375 | | Open_streams | 0 | | Opened_tables| 405 | | Questions| 2902623 | | Qcache_queries_in_cache | 22701 | | Qcache_inserts | 100527| | Qcache_hits | 101913| | Qcache_lowmem_prunes | 0 | | Qcache_not_cached| 37892 | | Qcache_free_memory | 35086904 | | Qcache_free_blocks
Performance Problem
Hi, I´m having a serious performance problem with my MySQL. The CPU is most of time with a load of 60%-95%. I´m using MySQL-4.0.14 on a FreeBSD-5.1 box. It´s a Celeron-2.0 Ghz - 512 Mb RAM - 40 Gb of Hard Disk. I think the main problem the queries struct wrongly build, but at this time I need to solve the main problem (slower pages) at first. And after that, change the site structure with more calm times. Does any good soul could give some tips to solve my problem? Upgrade my MySQL would solve part of the problem? What version should I use? My application has 71 tables + 261 indexes. It has 3 tables with almost 2.000.000 of records and the orthers have an average of 1.000 records. Any help would be appreciated. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
