Re: How NOT to log SHOW INNODB STATUS in the query log.
Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Im -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linux Threads MySQL
I got this error, You seem to be running 32-bit Linux and have 308 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/en/Linux.html What is the meaning? I use i386.rpm from mysql.com with 4 Gbytes RAM (dedicated for MySQL) = cut here == 060125 15:17:01 [Note] /usr/sbin/mysqld-max: ready for connections. Version: '5.0.15-max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL) 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=12578816 max_used_connections=489 max_connections=910 threads_connected=308 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 308 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/en/Linux.html thd=0xb111c5c8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x8e79defc, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x816add7 0x4004a618 0x83bf301 0x8348ad1 0x83463ff 0x83394df 0x8319677 0x831bb9b 0x820cd08 0x820ce05 0x81bb036 0x81ba240 0x81b9f30 0x81aae65 0x81ac129 0x81a8b61 0x817d030 0x81837aa 0x817b5e3 0x817aeb4 0x817a3d4 0x400452b6 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xae81ea78 is invalid pointer thd-thread_id=23942 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. pure virtual method called pure virtual method called Fatal signal 6 while backtracing pure virtual method called Number of processes running now: 0 060126 19:03:02 mysqld restarted 060126 19:03:02 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060126 19:03:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 49 2685434817. InnoDB: Doing recovery: scanned up to log sequence number 49 2690677248 InnoDB: Doing recovery: scanned up to log sequence number 49 2695920128 InnoDB: Doing recovery: scanned up to log sequence number 49 2701163008 InnoDB: Doing recovery: scanned up to log sequence number 49 2706405888 InnoDB: Doing recovery: scanned up to log sequence number 49 2711648768 InnoDB: Doing recovery: scanned up to log sequence number 49 2716891648 InnoDB: Doing recovery: scanned up to log sequence number 49 2722134528 InnoDB: Doing recovery: scanned up to log sequence number 49 2727377408 InnoDB: Doing recovery: scanned up to log sequence number 49 2732620288 InnoDB: Doing recovery: scanned up to log sequence number 49 2737863168 InnoDB: Doing recovery: scanned up to log sequence number 49 2743106048 InnoDB: Doing recovery: scanned up to log sequence number 49 2748348928 InnoDB: Doing recovery: scanned up to log sequence number 49 2753591808 InnoDB: Doing recovery: scanned up to log sequence number 49 2758834688 InnoDB: Doing recovery: scanned up to log sequence number 49 2764077568 InnoDB: Doing recovery: scanned up to log sequence number 49 2769320448 InnoDB: Doing recovery: scanned up to log sequence number 49 2774563328 = cut here == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL Overhead
Hi Guys, What sort of overheads am I expecting to get when running MySQL 5.0.18 in SSL mode? I need to decide whether to run MySQL in SSL mode, or use CIPE (on RHEL 3). Thanks, Khalid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Advanced Data Generator 1.6.0 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.6.0) A fast test-data generator tool that comes with a library of real-life data, can generate data to your database, SQL script or CSV files, many filling options, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes include MySQL 5 Stored Procedure support (MySQL Edition), Microsoft SQL 2005 support (Pro), large font systems enhancements and several bugfixes. With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error inserting text containing a ? character
This morning I began noticing some errors coming from my MySQL database that appear to be the result of a user inserting text which contains a question mark anywhere in it. The error is: Parameter '?' must be defined ... Is there a way to tell MySQL to just treat the ? as another character in the string, rather than as a parameter? Maybe have my application replace all occurrances of ? with something else that will represent a ? to the database before issuing the query? For reference, I'm using the MySQLConnector .NET for connecting my application to a MySQL 4.x database. Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
Hello.
I do not see the CHARACTER SET of your table (usually SHOW CREATE
includes it, may be you have NO_TABLE_OPTIONS in @@sql_mode), so I
assume it is the same as database character set - ascii. Check if the
problem disappears after changing the character set of your fields to utf8.
Chris wrote:
Sorry, I am unable to work the command line. I have used this script
instead.
$sql = show variables like '%char%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
$count = mysql_num_rows($result);
//echo $count;
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
echo BR;
$sql = show variables like '%collation%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
hope this provides the appropriate info.
Thanks
Gleb Paharenko [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
0=character_set_client
1=latin1
0=character_set_connection
1=latin1
0=character_set_database
1=ascii
0=character_set_results
1=latin1
0=character_set_server
1=latin1
0=character_set_system
1=utf8
0=character_sets_dir
1=C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
show variables like '%collation%';
0=collation_connection
1=latin1_swedish_ci
0=collation_database
1=ascii_general_ci
0=collation_server
1=latin1_swedish_ci
Include the CREATE statement for your table as well.
CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
Chris wrote:
I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Stra?e')
But if I try to execute the insert using a php script I get the error
1406
record too long. Using the same insert but without the ? character, the
sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like ? can be inserted without
problems.
Thanks
chris
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about recovery with binlog
Hello. At least it replaces the contents of my test file. If you're able to provide the test case where replace utility doesn't work please provide it to the list with the contents of the file. wangxu wrote: I execute follow statement. - shellreplace @@session.sql_mode=0 @@session.sql_mode=1 -- 1.01 - But string @@session.sql_mode=0 doesn't replace to @@session.sql_mode=1. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 23, 2006 7:56 PM Subject:Re: question about recovery with binlog Hello. There a lot of different ways to perform this operation. See: http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html man sed man awk wangxu wrote: How to replace it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.18: Bind on unix socket: Permission denied
Hello. Please, next time always CC your messages to the list as well. I'm not a lampp expert and can only point you to: http://dev.mysql.com/doc/refman/5.0/en/program-options.html This advice proved very helpful. This is where its at now: I noticed that the .err file belonged to user 'nobody'. *Somehwere* in the lampp scripts (or is compiled?) it forces this user. If I change into /opt/lampp/sbin and do ./mysqld -umysql it comes up! BUT... I don't know what cnf it reads and/or what else lampp needs to feed it upon startup. I text searched the entire lampp subdir, but I cant figure out where I can tell lamp to use user mysql. (Two obvious places, don't work.) If anyone knows, please advise. Thanks; -nat -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to connect tomcat with mysql pl help
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/error-access-denied.html sankar subramanian wrote: Hi All, Iam using tomcat 5x and mysql5.x the proble is when i try to connect mysql and tomcat using j/connector 3.x tomcat throws error as access denied to [EMAIL PROTECTED] host using password 'YES'. Please help me to overcome this problem. Thanks in advance sankar - Do you Yahoo!? With a free 1 GB, there's more in store with Yahoo! Mail. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL Overhead
Hello. When running our benchmark tests using secure connections (all data encrypted with internal SSL support) performance was 55% slower than with unencrypted connections. See: http://dev.mysql.com/doc/refman/5.0/en/compile-and-link-options.html Khalid Hanif wrote: Hi Guys, What sort of overheads am I expecting to get when running MySQL 5.0.18 in SSL mode? I need to decide whether to run MySQL in SSL mode, or use CIPE (on RHEL 3). Thanks, Khalid -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A propos de l'upgrade MySQL et de l'interclassement...
Bonjour, J'utilise une technologie CMS, installée depuis l'année dernière sur une base MySQL 4.0.16-nt. Le CMS à crée lui meme lus tables et y fait référence en utilisant l'encodage UTF-8. Depuis, nous avons migré à la version MySQL 4.1.16-nt et l'encodage par défaut choisi a été latin_swedish_ci. Evidemment mon CMS affiche les caractères n'importe comment, il me faut donc corriger cela. Question: 1. Est ce que mes chaines de caractères initialement UTF-8 sont maintenant véritablement des chaines de caratères latin_swedish_ci ou alors est ce que le paramètre latin_swedish_ci n'est qu'un attribut et que le contenu est résté le mûmu, c'est à dire UTF-8? Difficile à évaluer depuis l'interface graphique de phpmyadmin laquelle à son propre interclassement. 2. Comment modifier rapidement l'ensemble des tables et des champs latin_swedish_ci vers UTF-8? Pour info, phpmyadmin n'offre qu'une interface rudimentaire pour changer, table après table et de façon très laborieuse, l'interclassement sinon l'application du changement à la base n'a d'effet que sur inodb... Par ailleurs, j'ai fait un test export/import en changeant le default_char_set mais cela n'impact pas les paramètres des champs actuels. 3. Mon CMS spécifie UTF-8 mais à quel UTF-8 est ce que cela correspond vraiment? utf8_unicode_ci, utf8_general_ci, utf8_bin ??? Merci pour votre aide. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error inserting text containing a ? character
Hello. Check that you're using the fresh enough versions of Connector/NET and MySQL. See: http://bugs.mysql.com/bug.php?id=5392 David P. Donahue wrote: This morning I began noticing some errors coming from my MySQL database that appear to be the result of a user inserting text which contains a question mark anywhere in it. The error is: Parameter '?' must be defined ... Is there a way to tell MySQL to just treat the ? as another character in the string, rather than as a parameter? Maybe have my application replace all occurrances of ? with something else that will represent a ? to the database before issuing the query? For reference, I'm using the MySQLConnector .NET for connecting my application to a MySQL 4.x database. Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of MEMORY/HEAP-tables compared to mysql-cluster?
Hi, Did anybody ever benchmark heap-tables against a cluster? I have a table with 900.000 rows (40 fields, CHARs, INTs and DOUBLEs, Avg_row_length=294) that gets around 600 updates/sec (grouped in about 12 extended inserts a minute inserting/updating 3000 rows each). This is currently a HEAP-table (and get replicated onto a slave, too). I experience locking-problems on both the master and the slave, queries that usually respond within 0.0x seconds suddenly hang and take 10 seconds or sometimes even longer. I wonder if a cluster setup would give me any speedup in this issue? I will be doing some benchmarking myself next week, but It would be very helpful if anybody could share experiences with me so I don't have to start from scratch... It is difficult and very time-consuming to set up a test-suite comparable to our production systems... Any tips will help! Thanks! regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1025 when doing ALTER TABLE to change a myisam-table to a CLUSTER table?
Hello, I am just doing my first testing on a mysql-cluster system. Curently, I habe 1 management node running and 2 Data-Nodes that also run a mysqld each. The servers are Dual-Opterons with 6GB of RAM each. I did a dump of a database of one of our production systems (about 1.5GB mysqldump-file) and piped that into the first of the new servers. I then startet doing alter table abc type=ndb-queries and everything looked fine at the beginning. After having moved 70-80% of the tables into the NDB-Engine (they all show up correctly on the other mysql-server and everything seems to work) I suddenly got the following error: ERROR 1025 (HY000): Error on rename of './master/#sql-e80_1' to './master/status_system_hist' (errno: 708) I could not find any information on how to fix this and what the reason could be. When I manually create a new table with the same definition in the NDB-Engine and then do a insert into .. select from... I have no trouble. I should not be hitting the memory-limit yet, ndbd only uses 56% of the RAM so far. I attached some SQL-Output, part of the config.ini and the top-output. Can anybody help me with this? thanks Jan [NDBD DEFAULT] NoOfReplicas=2 DataMemory=4200M IndexMemory=1000M NoOfFragmentLogFiles=100 MaxNoOfConcurrentOperations=50 mysql show table status; +-++-++-++-+-+--+---++-+-+-+---+--++---+ | Name| Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-++-++-++-+-+--+---++-+-+-+---+--++---+ [...] | status_system_hist | MyISAM | 10 | Dynamic| 270413 | 91 |24721832 | 281474976710655 | 4409344 | 0 | NULL | 2006-01-27 15:03:48 | 2006-01-27 15:04:12 | NULL| latin1_swedish_ci | NULL || | [...] mysql alter table status_system_hist type=ndb; ERROR 1025 (HY000): Error on rename of './master/#sql-e80_1' to './master/status_system_hist' (errno: 708) mysql show create table status_system_hist; ++--- + | Table | Create Table |
Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
a cluster would not necessarily give you speed but would give you scalability, basically it increases your concurrency at which you can service clients, also in your case the lockups are occuring because of the obvious reason that the threads are competing for the system resources, so a cluster may be a good option, but you can also use replication and have multiple slaves and distribute the load, if you have the resources to do that .. Kishore Jalleda On 1/27/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, Did anybody ever benchmark heap-tables against a cluster? I have a table with 900.000 rows (40 fields, CHARs, INTs and DOUBLEs, Avg_row_length=294) that gets around 600 updates/sec (grouped in about 12 extended inserts a minute inserting/updating 3000 rows each). This is currently a HEAP-table (and get replicated onto a slave, too). I experience locking-problems on both the master and the slave, queries that usually respond within 0.0x seconds suddenly hang and take 10 seconds or sometimes even longer. I wonder if a cluster setup would give me any speedup in this issue? I will be doing some benchmarking myself next week, but It would be very helpful if anybody could share experiences with me so I don't have to start from scratch... It is difficult and very time-consuming to set up a test-suite comparable to our production systems... Any tips will help! Thanks! regards Jan -- 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: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
Why are you using a heap table? My company has tables with much more information than that, that get updated much more frequently. We use InnoDB tables, with very large buffer sizes and have tweaked which queries use the cache and which don't, on a system with lots of RAM (10Gb). Basically we've set it up so everything is in memory anyway. Perhaps a similar setup would help for you? Sincerely, Sheeri Kritzer On 1/27/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, Did anybody ever benchmark heap-tables against a cluster? I have a table with 900.000 rows (40 fields, CHARs, INTs and DOUBLEs, Avg_row_length=294) that gets around 600 updates/sec (grouped in about 12 extended inserts a minute inserting/updating 3000 rows each). This is currently a HEAP-table (and get replicated onto a slave, too). I experience locking-problems on both the master and the slave, queries that usually respond within 0.0x seconds suddenly hang and take 10 seconds or sometimes even longer. I wonder if a cluster setup would give me any speedup in this issue? I will be doing some benchmarking myself next week, but It would be very helpful if anybody could share experiences with me so I don't have to start from scratch... It is difficult and very time-consuming to set up a test-suite comparable to our production systems... Any tips will help! Thanks! regards Jan -- 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: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
sheeri kritzer schrieb: Why are you using a heap table? We started out with a myisam-table years ago when the table was much smaller und less frequently updated. We tried innodb about 2 or 3 years ago and couldn't get a satisfying result. We then changed it to HEAP and everything was fine. Now we are getting locking-Problems as the number of updates and selects constantly increases and need to upgrade our server-hardware anyway. I like the scalability of clusters for load-balancing and HA and we have had problems with our mysql-replications on the heavy load servers (total 2000 updates/Sec average) every 2-3 months that we couldn't reproduce. Other replications with less throughput run stable for years (same kernel, same mysqld). I'd get rid of all my replication problems when I put the most frequently updatet tables into a cluster... My company has tables with much more information than that, that get updated much more frequently. We use InnoDB tables, with very large buffer sizes and have tweaked which queries use the cache and which don't, on a system with lots of RAM (10Gb). Basically we've set it up so everything is in memory anyway. Perhaps a similar setup would help for you? that sounds interesting since we couldn't get good performance using innodb in our case - but thats a few years ago. things may have changed? I'll definitely give it a try next week, too. Could you give me more information on your system? hardware, size of the table, average number of updates/sec? thanks for your suggestions Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
Hello, Another consideration besides the performance aspects, are the characteristics between MEMORY and the NDB storage engines. (You'll be gaining or losing functionality depending on how you look at it.) Briefly: MEMORY - in memory, table locks, hash B-tree indexes, no disk i/o or persistence NDB - in memory, supports transactions, persistence, row-level locks, hash T-tree indexes Also, moving to cluster means more machines, and as stated by Kishore, Cluster really buys you scalability, not necessarilly performance right off the bat (unless you plan on using the NDB API to access data.) As, Sherri suggests another storage engine might be a better play here. Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 11:11 AM To: Jan Kirchhoff Cc: mysql@lists.mysql.com Subject: Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster? Why are you using a heap table? My company has tables with much more information than that, that get updated much more frequently. We use InnoDB tables, with very large buffer sizes and have tweaked which queries use the cache and which don't, on a system with lots of RAM (10Gb). Basically we've set it up so everything is in memory anyway. Perhaps a similar setup would help for you? Sincerely, Sheeri Kritzer On 1/27/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, Did anybody ever benchmark heap-tables against a cluster? I have a table with 900.000 rows (40 fields, CHARs, INTs and DOUBLEs, Avg_row_length=294) that gets around 600 updates/sec (grouped in about 12 extended inserts a minute inserting/updating 3000 rows each). This is currently a HEAP-table (and get replicated onto a slave, too). I experience locking-problems on both the master and the slave, queries that usually respond within 0.0x seconds suddenly hang and take 10 seconds or sometimes even longer. I wonder if a cluster setup would give me any speedup in this issue? I will be doing some benchmarking myself next week, but It would be very helpful if anybody could share experiences with me so I don't have to start from scratch... It is difficult and very time-consuming to set up a test-suite comparable to our production systems... Any tips will help! Thanks! regards Jan -- 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: mysql 5.0.18: Bind on unix socket: Permission denied
[Sorry] I didn't realize that hitting Reply (using Gmail) sent a private message. Usually on lists the message ends up on the list. Will need to observe the header in the future. tx; -nat On 1/27/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Please, next time always CC your messages to the list as well. I'm not a lampp expert and can only point you to: http://dev.mysql.com/doc/refman/5.0/en/program-options.html This advice proved very helpful. This is where its at now: I noticed that the .err file belonged to user 'nobody'. *Somehwere* in the lampp scripts (or is compiled?) it forces this user. If I change into /opt/lampp/sbin and do ./mysqld -umysql it comes up! BUT... I don't know what cnf it reads and/or what else lampp needs to feed it upon startup. I text searched the entire lampp subdir, but I cant figure out where I can tell lamp to use user mysql. (Two obvious places, don't work.) If anyone knows, please advise. Thanks; -nat -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- 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 NOT to log SHOW INNODB STATUS in the query log.
On 1/27/06, Imran Chaudhry [EMAIL PROTECTED] wrote: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Yep! It's a test environment, and MySQL-Administrator is often open for long periods in the background. Thanks; -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
No problem: Firstly, how are you measuring your updates on a single table? I took a few binary logs, grepped out for things that changed the table, counting the lines (using wc) and then dividing by the # of seconds the binary logs covered. The average for one table was 108 updates per second. I'm very intrigued as to how you came up with 2-300 updates per second for one table. . . did you do it that way? If not, how did you do it? (We are a VERY heavily trafficked site, having 18,000 people online and active, and that accounts for the 108 updates per second. So if you have more traffic than that. . .wow!) my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache= 80 ft_min_word_len = 3 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 max_connections = 2200 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 binlog-do-db= manhunt binlog-do-db= phpAdsNew binlog-do-db= mobile max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_files_in_group = 2 innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 5G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 # Query Cache Settings query_cache_size = 32M query_cache_type = 2 -- table info for the table in question: Name: Sessions Engine: InnoDB Version: 9 Row_format: Dynamic Rows: 10600 Avg_row_length: 792 Data_length: 8404992 Max_data_length: NULL Index_length: 24297472 Data_free: 0 Auto_increment: NULL Create_time: 2005-12-01 15:04:52 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: InnoDB free: 317440 kB -- We're running MySQL Version 4.1.12 on Fedora Core 3 x86_64. - The hardware is a Dell 2850 with 4 Intel(R) Xeon(TM) CPU 3.40GHz processors, 1024 KB cache size. Total RAM: 8199448 kB (8G) - We're quite beefy because of the amount of queries per second (3000) and updates. We are not aware of locking issues. Our customers are quite vocal (there are 8 folks in IT, and over 20 in Customer service!) so when things are slow we know about it. Sincerely, Sheeri Kritzer On 1/27/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: that sounds interesting since we couldn't get good performance using innodb in our case - but thats a few years ago. things may have changed? I'll definitely give it a try next week, too. Could you give me more information on your system? hardware, size of the table, average number of updates/sec? thanks for your suggestions Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
sheeri kritzer schrieb: No problem: Firstly, how are you measuring your updates on a single table? I took a few binary logs, grepped out for things that changed the table, counting the lines (using wc) and then dividing by the # of seconds the binary logs covered. The average for one table was 108 updates per second. I'm very intrigued as to how you came up with 2-300 updates per second for one table. . . did you do it that way? If not, how did you do it? (We are a VERY heavily trafficked site, having 18,000 people online and active, and that accounts for the 108 updates per second. So if you have more traffic than that. . .wow!) Thanks for your hardware/database information. I will look at that close tomorrow since I want to go home for today - it's already 9 pm over here... I need beer ;) We are not running a webservice here (actually we do, too, but thats on other systems). This is part of our database with data of major stock exchanges worldwide that we deliver realtime data for. Currently that are around 900,000 quotes, during trading hours they change all the time... We have much more updates than selects on the main database. Our Application that receives the datastream writes blocks (INSERT ... ON DUPLICATE KEY UPDATE...) with all records that changed since the last write. It gives me debug output like [timestamp] Wrote 19427 rows in 6 queries every 30 seconds - and that are numbers that I can rely on. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4 GIG Limitation
Greetings, I recently was told that MYSQL has a 4 GIG size limitation. I am not sure if this is for a table size or for the actual size of the database itself. Is there such a limatation for MYSQL 11.18 running on RedhatAS 3? Thanks, asadiq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4 GIG Limitation
Adnan mailto:[EMAIL PROTECTED] wrote: Greetings, I recently was told that MYSQL has a 4 GIG size limitation. I am not sure if this is for a table size or for the actual size of the database itself. Is there such a limatation for MYSQL 11.18 running on RedhatAS 3? Thanks, asadiq http://dev.mysql.com/doc/refman/5.0/en/table-size.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4 GIG Limitation
Hello, Check out http://dev.mysql.com/doc/refman/5.0/en/table-size.html Also, not sure if it's a typo but MySQL 11.8 is a ways off. Did you mean 4.1 or 5.0? Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA Phone: (713) 636-9239 -Original Message- From: Adnan [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 3:39 PM To: mysql@lists.mysql.com Subject: 4 GIG Limitation Greetings, I recently was told that MYSQL has a 4 GIG size limitation. I am not sure if this is for a table size or for the actual size of the database itself. Is there such a limatation for MYSQL 11.18 running on RedhatAS 3? Thanks, asadiq -- 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: NOT IN vs IS NULL
Devananda, Could anyone give me some insight as to which of the following queries is "better" (and why) - or if there is another query that would be faster than either? It's late on Friday so I could be missing something, but wouldn't the following be simpler? SELECT offer_id FROM paytable AS pt LEFT JOIN publisher_advertiser_blocks as pab USING (login_id) WHERE pab.login_id IS NULL; PB - In general, I try to stay away from very large IN(..) lists because I have seen them regularly degrade performance, but in this case the alternative that I have found doesn't seem to perform any faster. Could anyone give me some insight as to which of the following queries is "better" (and why) - or if there is another query that would be faster than either? I am using MySQL 4.1.14. There are three tables, offers: CREATE TABLE `offers` ( `offer_id` int(11) NOT NULL auto_increment, `advertiser_id` int(11) NOT NULL default '0', ... PRIMARY KEY (`offer_id`), KEY `advertiser_id` (`advertiser_id`) ) paytable: CREATE TABLE `paytable` ( `offer_id` int(11) NOT NULL default '0', `login_id` int(11) NOT NULL default '0', ... PRIMARY KEY (`offer_id`,`login_id`) ) publisher_advertiser_blocks: CREATE TABLE `publisher_advertiser_blocks` ( `login_id` int(11) NOT NULL default '0', `advertiser_id` int(11) NOT NULL default '0', PRIMARY KEY (`login_id`,`advertiser_id`) ) The goal of these queries is to select all offer_id's from `paytable` for a known login_id where that login_id is not "blocked" from that offer_id. While testing I simply selected count(*) to keep my result set from crowding the screen. The two queries have about the same execution time but very different EXPLAIN results... without further ado, here they are: explain select count(*) from paytable where login_id=# and offer_id NOT IN ( select distinct offer_id from offers left join publisher_advertiser_blocks pab using (advertiser_id) where pab.login_id=# ); +++--++---+-+-+--+-+- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +++--++---+-+-+--+-+- -+ | 1 | PRIMARY | paytable | index | NULL | PRIMARY | 5 | NULL | 1773152 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | offers | eq_ref | PRIMARY,advertiser_id | PRIMARY | 4 | func | 1 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | pab | eq_ref | PRIMARY | PRIMARY | 8 | const,affiliate.offers.advertiser_id | 1 | Using where; Using index | +++--++---+-+-+--+-+- -+ 3 rows in set (0.00 sec) explain select count(*) from paytable left join ( select distinct offer_id from offers left join publisher_advertiser_blocks pab using (advertiser_id) where pab.login_id=# ) as a using (offer_id) where login_id=# and a.offer_id IS NULL; ++-++---+---+---+-+-+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+---+-+-+-+---+ | 1 | PRIMARY | paytable | index | NULL | PRIMARY | 5 | NULL | 1773152 | Using where; Using index | | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 309 | Using where; Not exists | | 2 | DERIVED | pab | ref | PRIMARY | PRIMARY | 4 | | 2 | Using where; Using index; Using temporary | | 2 | DERIVED | offers | ref | advertiser_id | advertiser_id | 2 | affiliate.pab.advertiser_id | 8 | Using where | ++-++---+---+---+-+-+-+---+ 4 rows in set (0.01 sec) Thanks in advance, No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in
@@sql_mode).
The database has a Collation = ascii_general_ci. The only other option is
ascii_bin.
With respect to the table, it also has Collation of the same,
ascii_general_ci. There are many Collation types which the table may be
change to, including several of the utf8 verity (utf8.bin,
utf8.danish.ci,) but no utf8 without an extension. I tried setting the
table to utf8.unicode.ci, but still encounter the INSERT error as before.
Thanks
cw
Gleb Paharenko [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello.
I do not see the CHARACTER SET of your table (usually SHOW CREATE
includes it, may be you have NO_TABLE_OPTIONS in @@sql_mode), so I
assume it is the same as database character set - ascii. Check if the
problem disappears after changing the character set of your fields to
utf8.
Chris wrote:
Sorry, I am unable to work the command line. I have used this script
instead.
$sql = show variables like '%char%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
$count = mysql_num_rows($result);
//echo $count;
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value)
echo
$key=$valueBR;
echo BR;
$sql = show variables like '%collation%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value)
echo
$key=$valueBR;
hope this provides the appropriate info.
Thanks
Gleb Paharenko [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
0=character_set_client
1=latin1
0=character_set_connection
1=latin1
0=character_set_database
1=ascii
0=character_set_results
1=latin1
0=character_set_server
1=latin1
0=character_set_system
1=utf8
0=character_sets_dir
1=C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
show variables like '%collation%';
0=collation_connection
1=latin1_swedish_ci
0=collation_database
1=ascii_general_ci
0=collation_server
1=latin1_swedish_ci
Include the CREATE statement for your table as well.
CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
Chris wrote:
I think I have a problem with mysql related character sets and
collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a
MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES
('5','Unterwinkel
Stra?e')
But if I try to execute the insert using a php script I get the error
1406
record too long. Using the same insert but without the ? character, the
sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like ? can be inserted
without
problems.
Thanks
chris
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
i have one doubt
i have to write one query DivisionUnitsYear ameerpet 200 2004 ameerpet 300 2005 ameerpet 500 2006 From these values i want to retreive as like this DivisionUnitsYear ameerpet 200 2004 ameerpet 100 2005 ameerpet 200 2006 Means difference of the Units values by year can u give me this query. -- veerabhadrarao narra, -- veerabhadrarao narra, +91-988-556-5556 Database Administrator, I-ONE TECH LABS Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I NEED QUERY on Difference between values (Any Group Functions are there)
i have to write one query DivisionUnitsYear ameerpet 200 2004 ameerpet 300 2005 ameerpet 500 2006 From these values i want to retreive as like this DivisionUnitsYear ameerpet 200 2004 ameerpet 100 2005 ameerpet 200 2006 Means difference of the Units values by year can u give me this query. -- veerabhadrarao narra, -- veerabhadrarao narra, +91-988-556-5556 Database Administrator, I-ONE TECH LABS Pvt Ltd. -- veerabhadrarao narra, +91-988-556-5556 Database Administrator, I-ONE TECH LABS Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to start mysql with --old-password
Dear Friends, I need to start mysql with --old-passwords but i did not know how to do so. Actually i had mysql installed with someone else. I did not know how have he installed that. He have placed an entry in /etc/rc.d/rc.local as /usr/local/mysql/bin/mysqld_safe to start mysql when server starts. Also i have no file as my.cnf . I have 3 ques: 1)Is it correct ot add that entry into this file 2)Also how to start mysqld with --old-passwords etc. 3)How to add a configuration file. Pl. do help me with the asnwers. I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： Re: 回复： Re: MySQL 4.1 and PHP 4.4
--- Gleb Paharenko [EMAIL PROTECTED]写道: You can change the character_set_xxx variables using SQL queries. I usually put 'SET NAMES' at the beginning of my scripts. Must i add mysql_query(SET NAMES 'utf8') before every occurance of real mysql_query()? I have added mysql_query(SET NAMES 'utf8') in one php script which is included by all other php scripts but this doesn't solve my problem. My site is http://www.cnads.org/ . it is still not human readable. Regards, Lionel ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
