Re: filter slowquerylog on specific user

2010-09-28 Thread Max Bube 
Try maatkit mk-query-digest

mk-query-digest --filter '($event-{user} || ) =~ m/user/' mysql.slow.log



2010/9/28 Stefan Onken supp...@stonki.de

 Hello,

 is there any way to run a a slow query analyse with mysdumpslow only on
 specific mysql user connections? We have multiply application accessing
 the same server and sometimes even the same databases and tables. Now we
 want to analyse only one specific application which uses a specific
 username  for login into the mysql DB.

 The slow query looks like:

 # u...@host: my_user[my_user] @  [192.168.111.111]
 # Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 146
 


 Thanks!
 Stonki

 --
 www.stonki.de
 www.proftpd.de
 www.kbarcode.net
 www.krename.net



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com

Re: Fw: BIN LOG Error when use Begin Trans in Replication.

2010-04-22 Thread Max Bube 
Hi Vikram,

Add binlog_format=row to your my.ini master's conf file



2010/4/21 Vikram A vikkiatb...@yahoo.in

 Hi Hao Ding,

 I attached in my request mail it self. Please find the attachment.
 Thank you

 --
 *From:* hao ding fire9di...@gmail.com
 *To:* Vikram A vikkiatb...@yahoo.in
 *Sent:* Wed, 21 April, 2010 7:25:05 PM
 *Subject:* Re: BIN LOG Error when use Begin Trans in Replication.

 Vikram,
 I don't find my.ini.

 On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote:

 Hi,

 I have installed  mysql 5.1.45 version on windows server 2003[standard
 edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as
 slave. And the default engine in both severs is INNODB.

 I am using visual basic for my front end.  The replication system is
 running quite well.

 When i am using the rs.BeginTrans
 I am facing the following error when i am pointing my master,

 [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not
 possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe
 for binlog mode 'STATEMENT'

 If i point my slave as server[I have done for checking beginTrans]; it is
 running quite well.

 Can I have help from any one. It will be useful to me.

 I am attaching the my.ini for your reference.

 Thank you

 Vikram A



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com






 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com

Re: Innodb and bulk writes

2010-03-19 Thread Max Bube 
Hi Raj, Ananda, the schema is very simple, we don't have any blob or text
column.

I thought the same about the log files, so I tried with diferent sizes but
nothing change.
This is the output of iostat -x 1 when the performance is slow running a
restore


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   0.000.000.000.000.00  100.00

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
avgqu-sz   await  svctm  %util
sda1  0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdb   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdc   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdd   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sde   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
md0   0.00 0.00  0.00  3.92 0.0059.8015.25
0.000.00   0.00   0.00
sdh   0.00 0.00  0.00  0.98 0.0031.3732.00
0.000.00   0.00   0.00
sdi   0.00 0.00  0.00  0.98 0.0023.5324.00
0.000.00   0.00   0.00
sdj   0.00 0.00  0.00  1.96 0.00 4.90 2.50
0.000.00   0.00   0.00
sdk   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   0.000.000.000.000.00  100.00

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
avgqu-sz   await  svctm  %util
sda1  0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdb   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdc   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdd   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sde   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
md0   0.00 0.00  0.00  1.98 0.0017.82 9.00
0.000.00   0.00   0.00
sdh   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00
sdi   0.00 0.00  0.00  0.99 0.0015.8416.00
0.000.00   0.00   0.00
sdj   0.00 0.00  0.00  0.99 0.00 1.98 2.00
0.000.00   0.00   0.00
sdk   0.00 0.00  0.00  0.00 0.00 0.00 0.00
0.000.00   0.00   0.00


2010/3/18 Raj Shekhar spa...@rajshekhar.net

 Max Bube maxbube at gmail.com writes:


  The problem starts when I run bulk writes like an alter table or a
 restore
  from mysqldump, its starts processing more than 5 rows/s but suddenly
  the ratio goes down to 100 rows /sec. and then its stucked at this ratio
  even if I restart MySQL. The only way to get good perfomance again is
  deleting all innodb files (ibdata, iblog files) and restoring the DB
 again.
 
  The DBs are relative small about 70M rows and 10Gb size. I can repeat
 this
  behavior all the time just running 2 restores of the same database.
 
  Another example when its stucked:
 
  I want to delete 1M rows
  delete from table where id IN (select id from )  deletes 100 rows /
  sec
  but if I run 1 Million delete from table where id = xxx deletes 1
 rows
  / sec

 How busy are your disks when you start seeing slowdown in the delete
 process?  Are there blobs or big varchars in the deletes that you are
 doing?  Innodb might be filling up its log files and when you see a
 slow down, it might be flushing the log to the disk.


 One workaround for this is to not delete million rows, but to delete
 in batches of 1000 rows.  My guess would be that if each row is of
 size B, and you delete in a batch size of [innodb_log_file_size (in
 bytes) - 100 MB (in bytes)]/B , you should not see a slowdown.



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com

Innodb and bulk writes

2010-03-18 Thread Max Bube 
Hi list,

Im having problems with bulk writes (restores from mysqldumps, alters,
delete in (select ...)) with innodb. The servers are at amazon EC2 instances
w/ 15G ram and raid0 4disks EBS.
The problem starts when I run bulk writes like an alter table or a restore
from mysqldump, its starts processing more than 5 rows/s but suddenly
the ratio goes down to 100 rows /sec. and then its stucked at this ratio
even if I restart MySQL. The only way to get good perfomance again is
deleting all innodb files (ibdata, iblog files) and restoring the DB again.

The DBs are relative small about 70M rows and 10Gb size. I can repeat this
behavior all the time just running 2 restores of the same database.

Another example when its stucked:

I want to delete 1M rows
delete from table where id IN (select id from )  deletes 100 rows /
sec
but if I run 1 Million delete from table where id = xxx deletes 1 rows
/ sec

The problem is just only with writes on innodb, I can perfectly run
mysqldumps and bulk inserts on MyISAM.

This is happening with all MySQL 5.1.x versions I tested.

Any one have a clue about this issue??

Thanks in advance
Max


#INNODB Settings   #

innodb_file_per_table
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 8
innodb_support_xa = 0
innodb_thread_sleep_delay = 2000
innodb_flush_log_at_trx_commit = 0
innodb_log_file_size = 700M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_max_purge_lag = 10
innodb_max_dirty_pages_pct = 90
innodb_use_purge_thread = 4
innodb_extra_undoslots = 1
innodb_adaptive_checkpoint = estimate
innodb_io_capacity = 500
innodb_read_io_threads = 4
innodb_write_io_threads = 4

and this is a innodb status when was running at low preformance

mysql show engine innodb status\G
*** 1. row ***
  Type: InnoDB
  Name:
Status:
=
100310 13:12:07 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 42 seconds
--
BACKGROUND THREAD
--
srv_master_thread loops: 4409 1_second, 4408 sleeps, 439 10_second, 399
background, 399 flush
srv_master_thread log flush and writes: 4925
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 37498, signal count 37467
Mutex spin waits 115051, rounds 948698, OS waits 24706
RW-shared spins 1458, OS waits 778; RW-excl spins 0, OS waits 11943
Spin rounds per wait: 8.25 mutex, 17.61 RW-shared, 358898.00 RW-excl

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
70 OS file reads, 367420 OS file writes, 113414 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 2.02 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 25499809, node heap has 5416 buffer(s)
98.74 hash searches/s, 1.43 non-hash searches/s
---
LOG
---
Log sequence number 34171430524
Log flushed up to   34171425746
Last checkpoint at  33506703349
Max checkpoint age1187902219
Checkpoint age target 1150780275
Modified age  664727175
Checkpoint age664727175
0 pending log writes, 0 pending chkp writes
37839 log i/o's done, 1.05 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 13205766144; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 292738424 (203998472 + 88739952)
Page hash   12750664
Dictionary cache51124680 (51001072 + 123608)
File system 90728 (82672 + 8056)
Lock system 31876248 (31875512 + 736)
Recovery system 0 (0 + 0)
Threads 407416 (406936 + 480)
Dictionary memory allocated 123608
Buffer pool size786431
Buffer pool size, bytes 12884885504
Free buffers1
Database pages  781014
Old database pages  288283
Modified db pages   36334
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 190597, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 70,

Re: Innodb and bulk writes

2010-03-18 Thread Max Bube 
This is an output from console when its performnace goes dows

Query OK, 65469 rows affected (0.82 sec)
Records: 65469  Duplicates: 0  Warnings: 0

Query OK, 65469 rows affected (0.78 sec)
Records: 65469  Duplicates: 0  Warnings: 0

Query OK, 65469 rows affected (10 min 57.30 sec)
Records: 65469  Duplicates: 0  Warnings: 0

Query OK, 65469 rows affected (10 min 56.58 sec)
Records: 65469  Duplicates: 0  Warnings: 0

and the show full processlist is, I cut the insert its about 65k rows like
you can see above


*** 1. row ***
 Id: 1
   User: event_scheduler
   Host: localhost
 db: NULL
Command: Daemon
   Time: 4730
  State: Waiting on empty queue
   Info: NULL
*** 2. row ***
 Id: 3
   User: root
   Host: localhost
 db: test
Command: Query
   Time: 603
  State: update
  Info: INSERT INTO `challenge` VALUES
(95794260,2,0),(95794261,3,1),(95794262,2,1),(95794263,5,0)

*** 3. row ***
 Id: 7
   User: root
   Host: localhost
 db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist

regards
Max

2010/3/18 Ananda Kumar anan...@gmail.com

 when the writes are happening, please run show full processlist and let
 us know the out put.

 regards
 anandkl

 On Thu, Mar 18, 2010 at 9:09 PM, Max Bube maxb...@gmail.com wrote:

 Hi list,

 Im having problems with bulk writes (restores from mysqldumps, alters,
 delete in (select ...)) with innodb. The servers are at amazon EC2
 instances
 w/ 15G ram and raid0 4disks EBS.
 The problem starts when I run bulk writes like an alter table or a restore
 from mysqldump, its starts processing more than 5 rows/s but suddenly
 the ratio goes down to 100 rows /sec. and then its stucked at this ratio
 even if I restart MySQL. The only way to get good perfomance again is
 deleting all innodb files (ibdata, iblog files) and restoring the DB
 again.

 The DBs are relative small about 70M rows and 10Gb size. I can repeat this
 behavior all the time just running 2 restores of the same database.

 Another example when its stucked:

 I want to delete 1M rows
 delete from table where id IN (select id from )  deletes 100 rows /
 sec
 but if I run 1 Million delete from table where id = xxx deletes 1
 rows
 / sec

 The problem is just only with writes on innodb, I can perfectly run
 mysqldumps and bulk inserts on MyISAM.

 This is happening with all MySQL 5.1.x versions I tested.

 Any one have a clue about this issue??

 Thanks in advance
 Max

 
 #INNODB Settings   #
 
 innodb_file_per_table
 innodb_buffer_pool_size = 10G
 innodb_additional_mem_pool_size = 20M
 innodb_thread_concurrency = 8
 innodb_support_xa = 0
 innodb_thread_sleep_delay = 2000
 innodb_flush_log_at_trx_commit = 0
 innodb_log_file_size = 700M
 innodb_log_buffer_size = 8M
 innodb_lock_wait_timeout = 50
 innodb_max_purge_lag = 10
 innodb_max_dirty_pages_pct = 90
 innodb_use_purge_thread = 4
 innodb_extra_undoslots = 1
 innodb_adaptive_checkpoint = estimate
 innodb_io_capacity = 500
 innodb_read_io_threads = 4
 innodb_write_io_threads = 4

 and this is a innodb status when was running at low preformance

 mysql show engine innodb status\G
 *** 1. row ***
  Type: InnoDB
  Name:
 Status:
 =
 100310 13:12:07 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 42 seconds
 --
 BACKGROUND THREAD
 --
 srv_master_thread loops: 4409 1_second, 4408 sleeps, 439 10_second, 399
 background, 399 flush
 srv_master_thread log flush and writes: 4925
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 37498, signal count 37467
 Mutex spin waits 115051, rounds 948698, OS waits 24706
 RW-shared spins 1458, OS waits 778; RW-excl spins 0, OS waits 11943
 Spin rounds per wait: 8.25 mutex, 17.61 RW-shared, 358898.00 RW-excl
 
 FILE I/O
 
 I/O thread 0 state: waiting for i/o request (insert buffer thread)
 I/O thread 1 state: waiting for i/o request (log thread)
 I/O thread 2 state: waiting for i/o request (read thread)
 I/O thread 3 state: waiting for i/o request (read thread)
 I/O thread 4 state: waiting for i/o request (read thread)
 I/O thread 5 state: waiting for i/o request (read thread)
 I/O thread 6 state: waiting for i/o request (write thread)
 I/O thread 7 state: waiting for i/o request (write thread)
 I/O thread 8 state: waiting for i/o request (write thread)
 I/O thread 9 state: waiting for i/o request (write thread)
 Pending normal aio reads: 0, aio writes: 0,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 70 OS file reads, 367420 OS file writes, 113414 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 2.02 fsyncs/s

Re: Default Date and Time

2009-06-26 Thread Max Bube 
Hi Jason

The DEFAULT value can't be an expression.



2009/6/26 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com

 Hi All,

 I want to create a table that defaults to current_date and current_time.

 I have:

 CREATE TABLE `personalevent`(
`pevent` mediumint(10) NOT NULL,
`eventid` mediumint(10) NOT NULL,
`userid` mediumint(10) NOT NULL,
`username` varchar(10) NOT NULL,
`password` varchar(10) NULL,
`country` varchar(45) NULL,
`zipcode` varchar(5) NULL,
`city` varchar(35) NULL,
`hstate` varchar(45) NULL,
`exclusive` varchar(7) NULL,
`eventtime` time NULL DEFAULT current_time(),
`eventdate` date NULL DEFAULT current_date(),
`eventdura` varchar(35) NULL,
`daysevent` varchar(10) NULL,
`crowd` varchar(25) NULL,
`venue` varchar(50) NULL,
`activitytype` varchar(45) NULL,
`actdetails` varchar(255) NULL,
`encodedby` varchar(100) NULL,
`curmo` varchar(2) NULL,
`pageweb` varchar(50) NULL,
PRIMARY KEY (`pevent`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 But this throws a syntax error. I have tried Now() as well.

 What am I doing wrong?

 Best,

 -Jason


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com

Re: a possible group issue???

2009-06-12 Thread Max Bube 
Try with GROUP_CONCAT(ScriptName)

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat



2009/6/12 bruce bedoug...@earthlink.net

 Hi...

 I have the following...

 mysql INSERT INTO ParseScriptTBL VALUES
 - ('auburnCourse.py',40,1,1),
 - ('auburnFaculty.py',40,2,2),
 - ('uofl.py',2,1,3),
 - ('uky.py',3,1,4),
 - ('ufl.py',4,1,5)
 - ;
 Query OK, 5 rows affected (0.00 sec)
 Records: 5 Duplicates: 0 Warnings: 0
 mysql select * from ParseScriptTBL as p join universityTBL as u on
 u.ID=p.CollegeID where u.ID=40;
 +--+---+--+++
 | ScriptName | CollegeID | pTypeID | ScriptID |  ID |
 +--+---+--+++
 | auburnCourse.py | 40 | 1 | 1 | 40 |
 | auburnFaculty.py | 40 | 2 | 2 | 40 |
 +--+---+--+++
 2 rows in set (0.00 sec)


 i'd like to have a query that gives me both scripts for the college in the
 same row...
 keeping in mind that some colleges will have no scripts, some will have
 only
 one, and some will have both...

 i've tried to do the query, and added a group by CollegeID' with no luck..

 so how can i combine the two rows to get a single row??


 thanks



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com