dump time progressively increasing with Innodb

Hi, I noticed that over the months the dump of my databases (very subject to modifications, but not subject to increase significantly in size) gets progressively slower: from ~8 minutes to almost 15 in 6 months. How can I avoid this degeneration? Thanks, Nico -- MySQL General Mai

Re: Extremely slow access to information_schema

Il giorno mar, 28/04/2009 alle 17.11 -0400, Baron Schwartz ha scritto: > Hi, > > On Tue, Apr 28, 2009 at 1:04 PM, Rolando Edwards > wrote: > > Make sure the tables that the information_schema are not locked. > > > > This is because MyISAM tables, that are constantly being inserted into > > (Each

Extremely slow access to information_schema

Hi, in Mysql 5.0 accessing information_schema.tables means almost certainly dropping down the DBMS. With my configuration 412 databases 357417 grants every query to information_schema.tables takes minutes, while the equivalent show tables from... (that I can't absolutely use) is immediate. Is

Re: Shutdown time

On Wednesday 15 April 2009 17:24:21 Baron Schwartz wrote: > Hi! > > I just blogged about this: > http://www.mysqlperformanceblog.com/2009/04/15/how-to-decrease-inno >db-shutdown-times/ > > Short version: > > mysql> set global innodb_max_dirty_pages_pct = 0; > > and wait until Innodb_buffer_pool_pag

Shutdown time

Hi, after many years that I've been using mysql (with almost all Innodb tables) I still can't make myself a reason of the unbearably long shutdown times: almost everytime it takes at least 4 minutes to stop completely and to kill the process; sometimes I even had to kill -9 mysqld. Currently

Repeatedly got signal 10 in Solaris

Hi, for 2 consecutive nights I got the following message in the log, followed by a restart: 090323 2:00:14 - mysqld got signal 10; 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 misconfigu

Usefulness of mysql logs when using innodb?

Hi, I guess that when I'm using only Innodb and no replication I can safely disable mysql's (bin-) log files (that grow to no end) because Innodb has its own log files. Is it correct? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe

Re: Help needed to tune Innodb on ZFS (on Solaris)

Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto: > On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi <[EMAIL PROTECTED]> wrote: > > The problem I have is that importing the same 7 MB sql dump > > takes 9 seconds if engine=Myisam and 98 when engine is Innodb. > > &g

Help needed to tune Innodb on ZFS (on Solaris)

Hi, I installed Mysql 5.0.45 on Solaris 10 01/08 configured to run the datadir on a ZFS filesystem on a Raid1 pool. Both myisam and innodb tables are on the same filesystem. Innodb is configured to run with a buffer_pool_size=256M, with doublewrite set to OFF and with file_per_table to Off. The log

Re: confirm subscribe to mysql@lists.mysql.com

On Wednesday 09 April 2008 13:03:39 [EMAIL PROTECTED] wrote: > To confirm that you would like > > [EMAIL PROTECTED] > > added to the mysql mailing list, please click on > the following link: > > > http://lists.mysql.com/s/mysql/47fca28b736fbf40/Nicola.Sabbi=poste. >it > > This confirmation serv

Re: confirm subscribe to mysql@lists.mysql.com

On Wednesday 09 April 2008 13:02:57 [EMAIL PROTECTED] wrote: > To confirm that you would like > > [EMAIL PROTECTED] > > added to the mysql mailing list, please click on > the following link: > > http://lists.mysql.com/s/mysql/47fca2617dc97f36/nsabbi=tiscali.it > > This confirmation serves two p

converting TEXT fields between charsets doesn't work

Hi, I followed the instructions to change columns values from an encoding another (in my case from latin1 to utf8), but the operation simply failed. The manual reads http://dev.mysql.com/doc/refman/5.0/en/alter-table.html : " If you want to change the table default character set and all ch

innodb_file_per_table set but ibdata still updated

Hi, in /etc/my.cnf I set innodb_file_per_table in order to avoid a centralized set of ibdata* files, but that file (8 MB) is still present and continually updated. What can I do to make it vanish for good? I'd like to have only per-table ibd files. The version of Mysql that I'm using is mys

How to log on the server refused connections?

Hi, I'm experiencing some sporadic "connection refused" from mysql-max server. I'd like to keep track of these events, so is there a way to log on the server these 3 items? - date and time - ip of the client - reason of the refusal Thanks, Nico -- Email.it, the professional e-mail, gratis

Re: MySQL 5.1.14 Beta has been released

Mads Martin Joergensen wrote: Dear MySQL users, We are proud to present to you the MySQL Server 5.1.14 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installin

Re: InnoDB Transaction and LAST_INSERT_ID()

Mike Kruckenberg wrote: mysql> SET @staff_id = LAST_INSERT_ID(); Query OK, 0 rows affected (0.01 sec) I don't know if this behaviour has changed in later versions of mysql, but using session variables, although lovely, was the quickest way to break replication (at least up to and including 4.

Re: More than one MyQSL in a server

Mário Gamito wrote: Hi, I have a 3.23 MySQL running in a server and i want to install 5.0.27 I made # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 # make # make install and then # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var # ./mysqld_safe --datadir=/

Re: Moving database to another machine

Chris wrote: An additional note - you can only do this while mysql is completely shut down. You cannot do this while mysql is running on either server. why is it that I received this mail 3 times? I can understand 2 (one to my and one to the list), but 3 ... -- Email.it, the profess

Re: Moving database to another machine

Ratheesh K J wrote: Hlo all, I wanted to move around 50 GB of data on Machine A to Machine B. Both the machines are of same architecture ( LAMP ) Dumping and restoring takes a lot of time. Is there a faster method to accomplish the same? Is there a way to tar the whole thing and untar on M

Re: HUGE load when user with few privileges execs "show databases"

Nico, as a first stab, I would try optimizing the tables in question. OPTIMIZE TABLE mysql.user etc. The one with 194,177 entries would be a good candidate for this especially. done, but with no improvement I wonder also if you would see something logged in the slow query log as this happe

HUGE load when user with few privileges execs "show databases"

Hello, as the title reads, when a user X with access to few tables runs "show databases" the query executes _very_ slowly and there's a sudden HUGE load (mysqld takes 99% cpu for the whole duration of the query): show databases; ++ | Database | +--

What's wrong in this Innodb status log?

Hi, after having noticed occasional load spikes I created an Innodb monitor; follows an excerpt of the output where I can read that certain transactions don't start and that accessing a table (H.albero) with a very low amount of records (185) seems to take a lot of time. What's wrong with thes

Execution time of "show databases" increases when fewer databasesare visible

Hi, I have a strange and annoying problem with Mysql 4.0.26 that I hope someone will help me to fix: the more databases a user has the less time it takes to execute "show databases" and vice versa: show databases; +--+ 5 rows in set (7.97 sec) ---

Replication of GRANTs

Hi, I don't understand how GRANTS are replicated in Mysql 4.0.26 (I can't upgrade to a newer version). The servers were all cleanly installed from scratch (not upgraded) using Mysql's 4.0.26 official rpm. According to the manual GRANT statements should be replicated and after executing 'flush

Mysql 4.0 always executes case insensitive queries

Hi, my mysql always executes case insensitive queries: SELECT username FROM workflow.user WHERE username = 'NicO' LIMIT 1; +--+ | username | +--+ | nico | +--+ 1 row in set (0.01 sec) that field is of varchar(255) type. I don't understand the reason for this beha

Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine

sorry for this monologue-thread, but... Nico Sabbi wrote: [snip] If it can help, seems that the largest value I can store in a blob field without triggering that error is 192 characters long. I just read the restrictions on Innodb tables, and I'm not convinced that what is going

Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine

Nico Sabbi wrote: Hi, with mysql 4.1[234], importing a dump of Innodb tables containing at least a blob field I invariably get_ ERROR 1030 (HY000) at line 21027: Got error 139 from storage engine I read in the bugzilla that this problem is due to low memory conditions, but surely it'

Innodb dealing with blobs in 4.1: Error 139 from storage engine

0 11:46:31 InnoDB: Started; log sequence number 0 18025704 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL) Is there a way to fix this behaviour or should I revert to 4.0? Tha

Re: Grants not entirely propagated to slaves?

Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: hi, my mysql is a 4.0.21. After a flush privileges I can see all granted accesses. Thanks for you help! Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Grants not entirely propagated to slaves?

Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- Nico

Re: Revoke all privileges on *.* doesn't work

ld like mysql to kill or forget the existence of that user. Besides, I'm not supposed to mess with a dbms' internal tables, nor to know how/where grants are stored. -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists

Revoke all privileges on *.* doesn't work

-++ | version | 4.0.21-Max-log | +---++ 1 row in set (0.00 sec) what am I doing wrong? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to limit log space usage?

g_size=2500 set-variable = relay_log_space_limit=5000 max_relay_log_size seems to be respected, but the problem remains: after having the data inserted in the DB those logs are useless. Can't Mysql automatically purge logs when they are not needed anymore? Thanks, -- Nico Sabbi - Officine Digi

Mysql 4.1 and MyODBC don't like each other

dead/unmaintained project? Another problem: building the sources of Mysql*.src.rpm never builds Mysql-shared-compat, that is often indispensable. Can someone please update the specfile? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives:

Re: Replication stopping

igidiyor.com & http://www.dalar.net > > - Original Message - > From: "Nico Sabbi" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, July 12, 2004 6:08 PM > Subject: Replication stopping > > Hi, it happened again just now, this

Replication stopping

Hi, often my slave suddenly stops, reporting these the logs: 040712 12:19:00 Slave I/O thread exiting, read up to log 'db-bin.3323', position 197564621 040712 12:19:10 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 19756462

meaning of master_log_pos setting?

Hi, I have this entry in my logfile: 040524 12:30:06 Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master wh en reading data from binary log 040524 12:30:06 Slave I/O thread exiting, read up to log 'db-bin.3320', positio

Re: How to dump data in db.table syntax?

Alle Wednesday 12 May 2004 21:51, hai scritto: > Hi! > > On May 12, Nico Sabbi wrote: > > Alle Wednesday 12 May 2004 14:12, hai scritto: > > > Hi! > > > > > > On May 12, Nico Sabbi wrote: > > > > Alle Wednesday 12 May

Re: How to dump data in db.table syntax?

Alle Wednesday 12 May 2004 16:14, Egor Egorov ha scritto: > Nico Sabbi <[EMAIL PROTECTED]> wrote: > > Alle Wednesday 12 May 2004 14:12, hai scritto: > >> > > > sorry for replying to myself, but I verified that adding > >> > > > repl

Re: How to dump data in db.table syntax?

Alle Wednesday 12 May 2004 14:12, hai scritto: > Hi! > > On May 12, Nico Sabbi wrote: > > Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: > > > Hi! > > > > > > > sorry for replying to myself, but I verified that adding > > > >

Re: How to dump data in db.table syntax?

Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: > Hi! > > On May 11, Nico Sabbi wrote: > > Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: > > > Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: > > > > Nico Sabbi <[EMAIL PROTEC

[PATCH] for mysqldump: full insert statements (db.table) with -c

Hi, with this patch if you use mysqldump with -c the db name will precede the table name. Hopefully this will solve the replication problem I described earlier. ico --- mysqldump.c.orig 2004-02-10 19:15:59.0 +0100 +++ mysqldump.c 2004-05-11 17:33:16.407884792 +0200

Re: How to dump data in db.table syntax?

Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: > Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: > > Nico Sabbi <[EMAIL PROTECTED]> wrote: > > > Hi, > > > as the title says mysqldump 4.0.18 (and previous versions) doesn't want > > >

Re: How to dump data in db.table syntax?

Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: > Nico Sabbi <[EMAIL PROTECTED]> wrote: > > Hi, > > as the title says mysqldump 4.0.18 (and previous versions) doesn't want > > to dump data in the format > > > > insert into db.table values() >

How to dump data in db.table syntax?

Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? Another question: is there a way to dump all dbs that DON'T match a pattern without res

Re: Replication and Grants nightmare

Alle Friday 30 April 2004 16:44, hai scritto: > At 16:22 +0200 4/30/04, Nico Sabbi wrote: > >Alle Friday 30 April 2004 15:51, hai scritto: > >> >P.S. the manual doesn't say that grants super, reload, replication > >> > client and replication slave

Re: Replication and Grants nightmare

Alle Friday 30 April 2004 15:51, hai scritto: > >P.S. the manual doesn't say that grants super, reload, replication client > > and replication slave can be used only on *.* and not on DB.*. > > Each of those privileges is listed as an administrative privilege here: > > http://dev.mysql.com/doc/mysq

Re: Replication and Grants nightmare

Alle Friday 30 April 2004 15:22, Victoria Reznichenko ha scritto: > Hmm.. > Your queries worked fine for me: > > ANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO > 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; Query OK, 0 rows > affected (0.00 sec) > > mysql> show grant

Replication and Grants nightmare

Hi, I'm running mysql-max 4.0.18 on two servers on separate networks, master is A and slave is B. The problem is that if I don't grant on the master the full range of privileges to the slave the replication doesn't even start. Even worse is the fact that grants seems to be totally managed at ra

autoextend in 3.23.51 rpm not accepted

Hi, I just installed mysql-max 3.23.51, hoping to use innodb autoextend feature, but mysql dies when run with the following row in my.cnf: innodb_data_file_path = ibdata1:512M:autoextend;ibdata2:256M:autoextend; complaining of an unrecognized option. Thanks, Nico

Replication and transaction questions

HI, I have two questions: 1) is there a way to tell my mysql master not to log failed executions, such as creation of existing tables, drop of inexistant databases etc? Each of these operations lock the slave, and I believe that it' conceptually wrong logging them, since they never took pla

Serious performance problems when using InnoDB

Hi, I'm using a db with few tables, one of which is reported below: CREATE TABLE `keywords` ( `keyword` varchar(128) NOT NULL default '', `codice` varchar(16) NOT NULL default '', `timestamp` int(11) NOT NULL default '0', `soundekw` varchar(32) NOT NULL default '', PRIMARY KEY (`key

Innodb replication and Database renaming question

Hi, I'm still using Mysql 3.23.46 because, as far as I remember, in .47 was reintroduced the limit of 500 chars max in Innodb primary keys (limit that would break my application). Is it still present ? Is it present in Mysql 4 ? I also need to know if Innodb is reliable in replication mode