replication
Hi, I enabled binlog for replication yesterday and i'm getting some problems: -Yesterday, and all last days binlog was off, and i had an only one mysqld process at top processlist. -Today I'm getting several mysql processes, instead of a single one. -I've got three hard disks, one drive for system and innodb, another for all other data, and thirid for binaly replication logs. Yesteday I had no more than 200 httpd processes peak, and today i have more than 400. Server load seems to be fine ( similar to last days ), so i can't understand what has changed, and what's causing this situation. Every mysqld and httpd server are on sepparated 4processors XEON dedicated servers. Main website has 1.000.000 impressions, and 60.000 unique hits per day. Thanks for amy help. Regads. Ivan Lopez. Logosur.
innodb and high server load
hi,ive some two forum tables with abot 700Mb each one, and they was type myisam. I was getting some lock problems and i decided to switch them to innodb, but server load growed from 3 to 20. I followed your steps but i got not any server load improvements. should i back to myisam? or is there any way to solve it? thanks
inconsistent replication?
Hi,
Some hours ago i setup the replication for my cluster and it's the third
time i need to reset the replication and copy the whole database from master
to slave due to errors like this:
051228 17:13:35 [ERROR] Slave: Error 'Duplicate entry '9947776' for key 1'
on query. Default database: 'genteya'. Query: 'INSERT INTO `comentarios`
(id, idcom, nickcom, comentario, reply, ip, fecha) VALUES ('84600',
'264452', 'cipr22valencia', 'graias por los puntitos cielo pero si hay algo
que me gustaria mas que salir en primera paguina es conocerte mas jijiji
aver si te veo por mi *** o me das el tuyo un kiss y toma+++ puntitos ',
'S', '81.202.240.73', '1135811423')', Error_code: 1062
051228 17:13:35 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'mysql-bin.04' position 184226200
I don't know why, but primary keys are inserted in slave with different
(autoincrement) numbers than master, and replication shut down in slave.
CREATE TABLE `comentarios` (
`index` int(25) NOT NULL auto_increment,
`id` int(9) NOT NULL default '0',
`idcom` int(9) NOT NULL default '0',
`nickcom` varchar(15) NOT NULL default '',
`comentario` text NOT NULL,
`reply` char(1) NOT NULL default '',
`ip` varchar(15) NOT NULL default '',
`fecha` bigint(20) NOT NULL default '0',
PRIMARY KEY (`index`),
KEY `idcom` (`idcom`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Any idea?
Thanks.
Ivan Lopez.
Logosur.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
I only saw this changes: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I've rebuild this query according to the new sql join sintax and works fine. Thanks a lot because i don't know how many days were spent to solve this without your help :) Ivan Lopez. Logosur. - Original Message - From: "Peter Brawley" <[EMAIL PROTECTED]> To: "PaginaDeSpud" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, December 27, 2005 10:29 PM Subject: Re: upgrading to mysql 5 />I've upgraded from mysql 4.1 to mysql 5 and some queries >doesn't work. It's not explained on mysql changes incompatibilities... / It is: see the first change item, marked 'incompatible change', at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no longer get away with syntactically loose (SQL2003-incompatible) combinations of commas and JOIN clauses. PB PaginaDeSpud wrote: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading to mysql 5
hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locking issues
i'm using myisam. Is there any tip i should know before to migrate this table to innodb ? Thanks ! - Original Message - From: "Gleb Paharenko" <[EMAIL PROTECTED]> To: Sent: Tuesday, November 29, 2005 12:35 PM Subject: Re: locking issues Hello. What table engine do you use for your tables? InnoDB usually is the best choice if you have lots of concurrent updates and inserts. "PaginaDeSpud" <[EMAIL PROTECTED]> wrote: I'm getting locking issues due to tables very often updated/insert. It's splitted into two tables, one has the more updated data and the another has the more static data. I'm using queries with JOIN, would it be better for performance to use two queries instead of using JOIN? Thanks. Ivan L. -- 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]
locking issues
I'm getting locking issues due to tables very often updated/insert. It's splitted into two tables, one has the more updated data and the another has the more static data. I'm using queries with JOIN, would it be better for performance to use two queries instead of using JOIN? Thanks. Ivan L. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: low speed select
try to make an index with both fields and delete the individual indexes. the cause of why it takes much less time is because mysql cache, if tue query is cached it usually takes 0 seconds, but when something is changed on this table, cache is flushed and the query takes the real time it need. - Original Message - From: "Octavian Rasnita" <[EMAIL PROTECTED]> To: Sent: Monday, November 07, 2005 7:53 PM Subject: low speed select > Hi, > > I have tried: > > mysql> select count(*) from table_name where date='2005-11-07' and id=11; > +--+ > | count(*) | > +--+ > |0 | > +--+ > 1 row in set (46.42 sec) > > As you may see, this query took more than 46 seconds and I don't know why. > I am the single person that was using the database in the moment I've made > that query, and there are no programs that use to lock the tables until > finishing some other queries anyway. > > The table has an index on the data field and another index on the id field, > and usually takes much less time for such a query. > > This database is used in a web site and I see sometimes that it takes a very > long time for displaying some pages and now I know that the problem is the > database. > > The version of MySQL which is installed on the computer I work is: > 4.1.5-gamma-standard-log > > I know it could be a little older, but this could be the only problem? (and > the fact that is a gamma version?) > > Thank you. > > Teddy > > > -- > 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: locked status problem
hi Jigal ;) I've spent months to tune my server, optimizing indexes, code, even i've programmed a cache system on some hot points at my site. I close mysl connections and free resources when i don't need it. You can see at http://cluster2.genteya.com/load.png that my server keeps himself between 2 and 4 server load average over all day, except when mysql start to enqueue the locked queries and have peaks close to 20 load average for only some minutues, after these few minutes, all queries get unlocked,are processed by mysql server and all returns to normally work. This server carries over 800.000 not unique hits per day. When the mysel fall into locked state, i can see enough free RAM and swap is never used. - Original Message - From: "Jigal van Hemert" <[EMAIL PROTECTED]> To: "PaginaDeSpud" <[EMAIL PROTECTED]> Sent: Monday, November 07, 2005 9:17 AM Subject: Re: locked status problem > PaginaDeSpud wrote: > > Hi, > > i've got a problem with my server because some times per day, something occurs and server load average grows until 20 due to mysql. When it occurs, with "show processlist", I can see a lot of queued queries in "locked" state ( more than 100 queued). > > You can see the load average at http://cluster2.genteya.com/load.png ( notice these peaks, 4 o 5 peaks per day). > (...) > > set-variable=max_connections=2000 > > With such high settings for max_connections you may run out of memory > when a lot of connections are made. Your server will start to swap if it > runs out of RAM. Swapping is usually so slow that things get out of > control; the time needed to handle a request is increased dramatically, > which causes the number of requests waiting to be handled to increase > and the number of connections in use to get even higher. > > There's a lot you can do to minimize the load of your db server: > - optimize tables, indexes and queries > - do not use persistent connections in general (there might be > conditions where they may be useful, but usually making a connection is > so fast that this is better than keeping all those connections open) > - optimize your config to make optimal use of available memory > - do not keep connections to your db server open when you don't need > them (e.g. after querying your database your application initiates a > download) > - keep the number of connections in your application to a minimum (avoid > nested queries) > > Regards, Jigal. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
locked status problem
Hi, i've got a problem with my server because some times per day, something occurs and server load average grows until 20 due to mysql. When it occurs, with "show processlist", I can see a lot of queued queries in "locked" state ( more than 100 queued). You can see the load average at http://cluster2.genteya.com/load.png ( notice these peaks, 4 o 5 peaks per day). I know a query get into locked state when the table that it's trying to access is locked, but the queries that are in locked state are related to different tables, so it means that all tables are locked when it occurs. I know too that a table is auto-locked when an update or insert is being done, but when all those queries are queued in locked state i can't see any insert/update that affect to a bunch of rows, my updates/inserts always affects to a single row, then i've no idea of what could cause this situation. System is Dual XEON 2.7Ghz ( 4processors), 2Gb RAM and SCSI HDD. Tables are myisam and above you have the info from mysql config and status. I'm waiting hopeful for some help. Thanks ! Ivan L. my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-innodb datadir=/disco3/mysql/data #datadir=/usr/local/mysql/data set-variable=long_query_time=1 log-slow-queries = /var/log/mysqld_low.log set-variable=max_connections=2000 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 10M #2M read_buffer_size = 8M #2M read_rnd_buffer_size = 15M #8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 50M query_cache_type = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 mysql> show status; +++ | Variable_name | Value | +++ | Aborted_clients| 44 | | Aborted_connects | 6 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 512828130 | | Bytes_sent | 1759373754 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table| 0 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 228518 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_dealloc_sql| 0 | | Com_delete | 64244 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 638593 | | Com_insert_select | 3854 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 46 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 46 | | Com_replace| 41040 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 702124 | | Com_set_option | 140| | Com_show_binlog_events | 0 | | Com_show_binlogs | 1 | | Com_show_charsets | 35 | | Com_show_collations| 35 | | Com_show_column_types | 0 | | Com_show_create_db | 2 | | Com_show_create_table | 4 | | Com_show_databases | 14 | | Com_show_errors| 0 | | Com_show_fields| 24 | | Com_show_grants
