-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Daniel,
Daniel Ouellet wrote: > Marian Hettwer wrote: > >> As soon as replication starts, mysql gets very unresponsive: >> - -bash-3.1$ time mysqladmin -uroot -p proc stat >> Enter password: >> +----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+ >> >> | Id | User | Host | db | Command | Time | State >> | Info | >> +----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+ >> >> | 4 | system user | | | Connect | 204 | Waiting for >> master to send event | >> | >> | 5 | system user | | | Connect | 8661 | Has read all >> relay log; waiting for the slave I/O thread to update it | >> | >> | 7 | root | localhost | | Query | 0 | >> | show processlist | >> +----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+ >> >> Uptime: 308 Threads: 1 Questions: 6328 Slow queries: 0 Opens: 0 >> Flush tables: 1 Open tables: 24 Queries per second avg: 20.545 >> >> real 0m15.463s >> user 0m0.010s >> sys 0m0.020s >> >> 15 bloody seconds to return mysqladmin proc stat ? >> That ain't good. > > > Wasn't it that your slave actually catch up to the master and replicate > all the tables your master had? > well, not all tables, but quite a lot of them. Some are ignored. See the my.cnf I provided. > You don't provide mysql.err logs, etc and we don't know if it actually > replicate your tables or not. I guess from this it did. > It is replicating the tables I have. my mysql.err file looks like that: 060915 17:33:29 mysqld started 060915 17:33:29 [Warning] /usr/local/libexec/mysqld: ignoring option '--low-priority-updates' due to invalid value 'ON' 060915 17:33:29 [Warning] /usr/local/libexec/mysqld: ignoring option '--low-priority-updates' due to invalid value 'ON' - --> Seems like that parameter doesn't exist anymore in MySQL 5.0 ... I'll look into it... 060915 17:33:29 [Warning] Could not increase number of max_open_files to more than 8096 (request: 8192) - --> You mentioned something about that later in your mail. Could be a problem, eh? 060915 17:33:29 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run - --> Yeah well, I could run mysql_fix_privilege_tables, however, I bet it has nothing todo with my problem. 060915 17:33:29 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them 060915 17:33:29 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=babelfish45-relay-bin' to avoid this problem. - --> As I'm not about to change the hostname, I'll fix that problem later. 060915 17:33:29 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.0.22-log' socket: '/tmp/mysql.sock' port: 3306 OpenBSD port: mysql-server-5.0.22 060915 17:33:29 [Note] Slave SQL thread initialized, starting replication in log 'foo-bin.000040' at position 358083515, relay log './babelfish45-relay-bin.000004' position: 37101832 060915 17:33:29 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'foo-bin.000040' at position 358083543 > Let see 308 seconds up only for the server, did 20.5 query per seconds > for that time with would be your 6328 queries there, of witch all finish > based on this show process and also looks like it finish to mirror it > and now is waiting for the master to send more. > That's right. And according to the queries per second it's continuesly getting data from its master (approx. 49 queries per second, all through replication) Uptime: 231027 Threads: 1 Questions: 11540813 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 11 Queries per second avg: 49.954 > I have no clue how big your database might be or not. Nor how many > tables, etc. > all in all it's 175 MyISAM files, but only a small part of them are actually open and in use. As you see above, only 11 tables are open. But some of them are rather large (400 - 600 MB). > The only think I know is that you did install from packages. Great. Then > started master/slave and look like it worked. And as I said, access to MySQL itself is pretty slow. As in: getting a "show slave status\G" needs between 4 and 14 seconds, or a "mysqladmin proc stat" needs up to 16 seconds. And this has really nothing to do with "how big is your database" or "how many open tables do you have". Not at all. > > Then you were trying to query the server I guess for data may be, but it > was up only for 5 minutes and based on the query listed, etc. It was > really busy to mirror the data from the master to the new slave. Wasn't > it what it was doing here? > It's still doing exactly that. IO_THREAD gets data from master, SQL_THREAD is working at nearly 50 queries / second to write all the data it got. > So, are you expecting to have all the data ready as soon as you start > MySQL on a slave. Look like you were expecting it to be ready right > away? I don't know but I know this. To query data, it has to be there > first. Oh it is there. Besides, I used a tarball to get a starting point to start the replication. This tarball is probably 1 hour behind in replication. So I should have seen a replication drift of approx 1 hour. Which I did. And while the box is replicating and trying to catch up, of course I should be able to connect to MySQL and do some queries. > > To me look like the box was up 308 seconds and started to mirror the > source, updated that databases/tables, may be creating the index as it > goes, I don't know your data see, and may be some of your data was > requesting the table to be lock when updated instead of may be insert > delay or something like that. > The statement I was talking about was "show slave status" or "show processlist". Both statements have nothing to do with locked tables or the like. > But now that's it's been up for 216000 seconds, how does it work? > Dunno. Sometimes I have doubts about how MySQL is calculating the time in a replication setup. Reminds me of checking that my OpenBSD box here has it's ntpd running and is getting the correct time :-) > Is your data mirror well or not. Any error in mysql.err file or not. > Responsive to query or not? > my data mirror? You're talking about the slave (the OpenBSD box), not the Master (some linux box), right? Well, according to .err it's doing okay... > I just wonder if you expected it to be all mirror and ready as soon as > you issue the start slave? > You probably haven't read my first email correctly. Once again, the "mysqladmin proc status" is taking very very long. This has nothing to do with catching up with the master. I do know that it'll take some time :) >> I do know MySQL quite well (MySQL 4.1 in fact) and for the OpenBSD >> Installation I followed the guidelines at www.openbsdsupport.org, which >> was basicly just increasing the kern.maxfiles and changing >> /etc/login.conf > > > There was more then that, including to make sure you start the daemon > with the class as well, etc. > That's true... so let's see wether I did a mistake there... - -bash-3.1$ ps aux | grep mysqld _mysql 18287 1.8 4.2 189292 88972 p0 D Fri05PM 131:53.49 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/mysql - --user=_mysql --pid-file=/var/mysql/babelfish45.mobile.rz.pid --open-files mhettwer 26116 1.0 0.0 372 468 p1 S+ 9:52AM 0:00.01 grep mysqld root 30015 0.0 0.0 488 472 p0 I Fri05PM 0:00.08 /bin/sh /usr/local/bin/mysqld_safe looks like I didn't. the mysqld is running as _mysql ... >> http://crivens.terrorteam.de/~rabauke/OpenBSD/MySQL/my.cnf.txt >> >> Ah, I nearly forgot: >> - -bash-3.1$ sysctl kern.maxfiles >> kern.maxfiles=8096 > > > Did you notice that the suggeted configuration have double the allow > files in the kernel oppose to the my.cnf configuration? > > There is a reason for that. Se the top of the document said that when > you open about 29 tables, you will get the error 9. However you see 64 > in the default limits right? Why you think that is? MySQL documentations > does explain that mysql always open two files minimum per tables in most > cases, so may be mysql should rewrite the meaning of max_open_files in > the configurations. But anyway, simple rule of thumb. But twice the > number of files limits in sysctl as you put in my.cnf, not the same as > you do here. > Thanks for pointing out. I'll fix that right now. open-files-limit is now at 4096 > Not that I think you hit that limit here as you didn't say anything > about error 9, but should one day start to have a lots of tables and > come close to this limits, then you will not know why that is. > I will know now... > > > Don't forget that the man page is clear on that subject the class is use > on login, but here the daemon doesn't login, see master.passwd. You > start it manually from your shell, it will use the default class, not > the _mysql class, unless you expressly tell it to do so. > > This is almost every time overlooked. > > Many thinks that by defining the class, daemon will use it. > > But when you start the daemon are you that user? No, you are not, so it > can't use that class then, but will use the default class. Just a word > of caution on this part as well if you didn't do it too. > > Proof. > > # su _mysql > This account is currently not available. > > So, it can't then use the _mysql class when you start it, unless you do > specify it. > > su -c _mysql root -c '/usr/local/bin/mysqld_safe &' > /dev/null & > It looks like doing a "sudo mysqld_safe" as my user has the same effect. mysqld is running as _mysql Although mysqld_safe is running as root. hm hm... I'll better go with the correct way, changing to _mysql. Thanks for pointing out. > Hope this help you some. Well let's see. But thanks so far.. ./Marian iD8DBQFFDlJIgAq87Uq5FMsRArxXAKDhA1L3LJeoXtOCUpVBe8lkohCNawCgpqQv +09kfnbLYoS2MxJL8rfUWeA= =7b0C -----END PGP SIGNATURE-----
