Re: MySQL memory allocation
Which thread library is the mysqld linked against? Linuxthreads shows each thread as a separate process in top or ps output. All threads share the same memory. From your output, it is likely that you are using linuxthreads(all pids having the same VSZ and RES memory). Regards, Ravi Cabbar Duzayak wrote: Hi, Could you please tell how I can tell how much memory does mysql server allocate on a linux box? I tried doing: top -b -n 1 | grep mysql But, it printed out bunch of processes for mysql. Are these all using shared memory so each line gives you the total amount for mysql? How can one interpret the results of this top, i.e. how should I read Virtual/Resident/Shared columns for all these processes and find out how much shared + (resident + swap: virtual) memory it uses? Results of TOP for mysql: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 8205 mysql 12 0 75524 28M 26896 S 4.8 0.7 3:15 1 mysqld 2150 mysql 9 0 75524 28M 26896 S 0.4 0.7 3:04 1 mysqld 32011 root 9 0 628 500 500 S 0.0 0.0 0:00 0 mysqld_safe 32033 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:08 0 mysqld 32035 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:00 0 mysqld 32036 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:04 0 mysqld 1321 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 1 mysqld 1323 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:17 0 mysqld 2112 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:20 0 mysqld 2131 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 0 mysqld 2132 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:34 1 mysqld 2133 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 1 mysqld 2134 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 2135 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 2136 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:59 1 mysqld 2137 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 0 mysqld 2142 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 8080 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 0 mysqld 8082 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 8197 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 1 mysqld 8204 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:16 1 mysqld 16010 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 16011 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 1 mysqld 16138 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 4074 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:16 0 mysqld 17331 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:44 0 mysqld 17337 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:59 0 mysqld 22847 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:53 1 mysqld 22912 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:49 0 mysqld 22944 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:48 1 mysqld 23101 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:38 0 mysqld 23102 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:43 0 mysqld 23124 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:44 1 mysqld 23168 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:46 1 mysqld Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump corrupting utf8 data
How are you importing the dump into mysql? Importing by piping the dump file to mysql may broke some chars due to shell. Have you tried this: (with in mysql client) SET NAMES UTF8; SET CHARACTER SET UTF8 source /pathto/dump.sql Cheers, --Ravi Sean O'Hara wrote: Hi All, I've been googling all morning trying to find info on how to do a mysqldump of a utf8 encoded database from which I can restore without corrupting all the non ascii characters. If anyone has any pointers on this, I'd be most grateful. Here is my setup. I am building a ruby on rails app and all the data is being entered from that application. The data is displayed fine if when it hasn't undergone a backup with msyqldump. I'm using mysql server 4.1.16 on Fedora Core 4. Here is an example show create table on one of the relevant tables: artists | CREATE TABLE `artists` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `biography` text, `created_at` datetime default NULL, `updated_at` datetime default NULL, `sort_name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | The restored table has the same show create table definition. I've used enca on the dump file to see if it's been encoded properly: enca -L none testdump1.sql Universal transformation format 8 bits; UTF-8 So that seems to be in order. Here's the top of the dump file: 1 -- MySQL dump 10.9 2 -- 3 -- Host: localhostDatabase: alienrails_production 4 -- -- 5 -- Server version 4.1.16 6 7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 10 /*!40101 SET NAMES utf8 */; 11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 15 16 -- 17 -- Table structure for table `artist_images` 18 -- Obviously I'm mussing something, but I have no idea what. Thanks in advance, Sean --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: identify process that created the connection
This blog may help, http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/ Cheers, Ravi Rithish Saralaya wrote: Hello people. Is it possible to find the process that invoked the mysql thread, given a mysql thread id? We have a web application that runs on Linux-Apache-MySQL-PHP; and I sometimes see numerous mysql threads in sleeping mode when I run mytop. I think the sleeping mysql threads could be due to the fact that some of my web-page(s) have obtained a mysql connection, executed their queries, but have not terminated(and have not released the mysql connection also). If I could know the httpd processes that have created these connections, I would be able to find out the pages that are the culprit. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB Question
Tripp, ibdata files never shrink. You can try this to free up space: * Take a mysqldump of all tables that you may need, * delete the ibdata files, * Rebuild your tables by importing the dump. Using innodb_file_per_table, will freed the disk space whenever you run optimize table or delete the table. Performance wise, using individual tablespaces may help in I/O load balancing, if tablespaces are spread over disks. --Ravi Tripp Bishop wrote: Howdy all, I'm trying to clean up a development server so that we can retask it. I tried dropping a very large but no longer necessary database that uses INNODB tables exclusively. Unfortunately the disk usage on the box didn't change. We're using a completely default MySQL 5.0.15 install on the box (a small centos linux box) so we don't have innodb_file_per_table set in a my.cnf file. So I've got two basic questions... First, how can I get rid of the current ibdata file? It's taking up a lot of space. Do any of the mysql databases (information schema and mysql) use INNODB tables? What's the correct procedure for freeing up this disk space? Second, are there still issues with innodb_file_per_table and indexes or has that been eradicated? It seems like it was a problem early on in the mysql 4.1 life cycle. I just wanna be sure. Also, Does innodb_file_per_table enhance performance (even if the tablespaces are all on the same physical disk)? Just curious. Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~performance issues~
yes, that is the reason for slowness . Thanks, Ravi On Tuesday 11 April 2006 10:51, Mohammed Abdul Azeem wrote: Hi Ravi, Since the sync'ing is done to disk with sync_binlog=1, the update queries to server are slower compared to the server having sync_binlog=0 rite ? Thanks, Abdul. On Tue, 2006-04-11 at 10:51 +0530, Ravi Prasad LR wrote: Hi Abdul, When sync_binlog is set to 1, innodb fsyncs the binary log to disk after every single write to binary log, but not in the case of sync_binlog=0. From MySQL manual: snip If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value is 0 which does no sync'ing to disk. A value of 1 is the safest choice, because in case of crash you lose at most one statement/transaction from the binary log; but it is also the slowest choice (unless the disk has a battery-backed cache, which makes sync'ing very fast). This variable was added in MySQL 4.1.3. /snip --Ravi On Tuesday 11 April 2006 10:22, Mohammed Abdul Azeem wrote: Hi, I have a master/slave setup ( replication enabled ) for mysql in two different geographic locations ( one master/slave set up in each location). In one location i have configured the sync_binlog=1 . And the other location does not have the same. My problem is, when i run similar update processes on both the master servers, the server with sync_binlog=1 is very slower in terms of completing the update query as compared to the machine having sync_binlog=0. Is that a cause for slow performance ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~performance issues~
Hi Abdul, When sync_binlog is set to 1, innodb fsyncs the binary log to disk after every single write to binary log, but not in the case of sync_binlog=0. From MySQL manual: snip If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value is 0 which does no sync'ing to disk. A value of 1 is the safest choice, because in case of crash you lose at most one statement/transaction from the binary log; but it is also the slowest choice (unless the disk has a battery-backed cache, which makes sync'ing very fast). This variable was added in MySQL 4.1.3. /snip --Ravi On Tuesday 11 April 2006 10:22, Mohammed Abdul Azeem wrote: Hi, I have a master/slave setup ( replication enabled ) for mysql in two different geographic locations ( one master/slave set up in each location). In one location i have configured the sync_binlog=1 . And the other location does not have the same. My problem is, when i run similar update processes on both the master servers, the server with sync_binlog=1 is very slower in terms of completing the update query as compared to the machine having sync_binlog=0. Is that a cause for slow performance ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBD::mysql::st execute failed: MySQL server has gone away
Luke, Yup. Basically if I do a particular query from the command line, I get the following error: === InnoDB: Error: tried to read 16384 bytes at offset 1 3469819904. InnoDB: Was only able to read -1. 060327 8:25:41 InnoDB: Operating system error number 5 in a file operation. InnoDB: Error number 5 means 'I/O error'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html InnoDB: File operation call: 'read'. InnoDB: Cannot continue operation. 060327 08:25:41 mysqld restarted 060327 8:25:42 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... 060327 8:25:42 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2 2096716847. InnoDB: Doing recovery: scanned up to log sequence number 2 2096716847 InnoDB: Last MySQL binlog file position 0 79, file name ./ticketdb-bin.15 060327 8:25:42 InnoDB: Flushing modified pages from the buffer pool... 060327 8:25:42 InnoDB: Started; log sequence number 2 2096716847 /opt/csw/mysql4/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution It looks like your disk is having problems.What does 'dmesg' or /var/log/messages say. Thanks, Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Master Replication
Marvin, The process is working between Master, Slave1 and Slave2 but for some reason Slave2 is not writing bin-log information. So Slave3 is sitting empty and not getting any data because of no data in Slave2's bin-log. I guess u don't have 'log-slave-updates' flag enabled also along with bin-log in Slave1 and Slave2 in order to have the updates from master by slave sql-thread to be logged in their bin-log. Have a look here http://dev.mysql.com/doc/refman/5.0/en/replication-options.html --Ravi On Monday 27 February 2006 19:18, Marvin Wright wrote: Hi, I'm having a few problems at the moment with replication. We are in the process of migrating a cache to 2 new database machines. As the data is a cache its very large is refreshed constantly. Currently we have 2 machines, 1 master and 1 slave. I am trying to set up this configuration but I just can not get it to work. Master -- Slave1 -- Slave2 -- Slave3 Master has id 1 and bin-log enabled Slave1 has id 2 and bin-log enabled but also a do-bin-log=Cache Slave2 has id 3 and bin-log enabled Slave3 has id 4 The process is working between Master, Slave1 and Slave2 but for some reason Slave2 is not writing bin-log information. So Slave3 is sitting empty and not getting any data because of no data in Slave2's bin-log. The idea is to get this process working correctly, then the Slave2-Slave1 link broken and Slave2 will become the new Master. Any idea why this could be a problem ? All mysql versions are 4.1.12 and running on linux. Many Thanks Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Server crashes saying Page directory corruption
Hi all, Server version :4.0.26 OS: FeeBSD-4.10 The mysql server(slave) crashes with the following messages in its error log, and gets restarted, after which rollbacks and starts replicating from master Page directory corruption: supremum not pointed to 051226 10:02:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex .(many 0's) ;InnoDB: End of page dump 051226 10:02:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer 0xc9c1bff8 InnoDB: buf pool start is at 0x52a4000, number of pages 70400 051226 10:02:58InnoDB: Assertion failure in thread 20491 in file ../../innobase/page/../include/buf0buf.ic line 286 InnoDB: We intentionally generate a memory trap InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; it is a slave server replicating from a master. There are no reads happening while server crashed. This has happened couple of times. we tried restoring mysql with a backup of master. But it goes through fine for few days(2-3 days) , after which the same error reiterated. what does this error mean? Please help me in figuring out this error. Thanks , Ravi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Logs not updating
Hi James, If you want to configure a relay replication server, i.e. make the replication to pass data from slave to another slave as A-B-C you need to start B with --log-bin and --log-slave-updates.So check that log-slave-updates has been included in your my.cnf file. You can read more about this http://www.mysql.com/news-and-events/newsletter/2003-11/a000270.html Thanks, Ravi On Monday 26 September 2005 17:01, James Green wrote: Hi, Intend on the following replication scenario: A - B - C A is replicating to B no problem. B was copied to C. C was ordered to begin replicating from B, but saw no updates Checked on B, and although mysql 'show variables' states binary logging in 'ON', no updates are being written to the logs except the relay-log. Can someone please advise? Using InnoDB's hotbackup.pl file to perform the backups. Cheers, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
