#mysql
18 October 2007
Total 25 pages. You are browsing page 4/25.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last
05:14 <****> Is it common practice to specify types such as IN type_id SMALLINT UNSIGNED for a parameter of a Store Procedure?
05:23 <****> guys, how do i check which users able to login to sql server
05:23 <****> i need to access to it
05:23 <****> MySQL Server I hope?
05:24 <****> Hi. I have a MySQL 5.0 server (64-bit) that processes about 180 updates per second. I noticed in 'top' that only 1 CPU core is being used by MySQL to perform the insert/update/delete commands. Is there any way I can get it to utilize all the cores rather than just one?
05:24 <****> slapfaceware: log into mysql database
05:24 <****> and look at the User table
05:24 <****> with root?
05:25 <****> that should work
05:25 <****> gfrank: how many parallel threads are running?
05:25 <****> I have thread_concurrency set to 16
05:25 <****> But past that, I have made no changes.
05:25 <****> gfrank: check the processlist (show processlist;)
05:26 <****> Yeah, one thread
05:26 <****> I should note: this is a slave
05:26 <****> It's syncing with the master.
05:26 <****> gfrank: oh well... replication only uses one thread.
05:26 <****> Any way to increase that?
05:26 <****> 55,000 seconds behind master going very slow is troublesome.
05:26 <****> gfrank: the relay log is executed one statement after another. No way to change that (it's difficult)
05:27 <****> hmm
05:27 <****> you have to increase write performance
05:27 <****> This is a 12x250GB SATA-2 16MB Buffer NCQ in RAID10
05:27 <****> sounds sane.
05:28 <****> but it can't utilize it really because it is single threaded. The key is in your application.
05:28 <****> innodb or myisam?
05:28 <****> MyISAM
05:28 <****> InnoDB was not appropriate for our application.
05:29 <****> why that?
05:29 <****> Using InnoDB: Database grew 38GB/day, RAM requirements were at least 16GB and was increasing 1GB per month in average requirements, MySQL took 45 minutes to restart (reading the ibdata file)
05:29 <****> wow
05:30 <****> large tables I think
05:30 <****> Using MyISAM: Database grows 3GB/day, RAM requirements are 3.8GB and holding steady. MySQL takes 15 seconds to restart.
05:30 <****> More like lots of tiny tables.
05:30 <****> if only there weren't the concurrency problems
05:31 <****> Yeah
05:31 <****> gfrank: did you do a lot of alter tables? was it file per table?
05:31 <****> mike: setup a new slave.
05:31 <****> ?
05:31 <****> The old slave was synced, so we disabled MySQL, rsync'd the data over to the new slave, and brought it up.
05:31 <****> did you use redundant or compact row format?
05:31 <****> Now the new slave is syncing with the master.
05:31 <****> gfrank: nm
05:32 <****> in InnoDB
05:32 <****> I wasn't working on this project when that was setup.
05:32 <****> So I don't know.
05:32 <****> (it's quite common that InnoDB data is a lot larger than myisam, but over 10 times larger)
05:32 <****> Yeah.
05:33 <****> At 30GB/day growth, let alone RAM requirements, InnoDB was not a good choice.
05:33 <****> yeah
05:33 <****> sounds like a strange setup
05:33 <****> the tablespace grows 30G a day?
05:33 <****> That's what I thought when I came on.
05:34 <****> kim: 30GB of new inserts and data added per day
05:34 <****> gfrank: if you do an alter table, it will grow the tablespace to hold both copies, then not shrink back
05:34 <****> gfrank: (innodb specific)
05:34 <****> We didn't run altertables with InnoDB
05:34 <****> still, 38 per day is too much.
05:34 <****> The only alter tables we ran was when we converted to MyISAM.
05:35 <****> anyone have experience with C/prepared statements?
05:35 <****> gfrank: or like select from insert into and then drop
05:35 <****> Yeah, didn't do that.
05:36 <****> fuse: sorta
05:36 <****> Quick question: Server CPU setup: Dual Xeon E5345. Now, if you had a 12xU320 SCSI and a 12xSATA2, would you expect replication to sync the data faster to the SCSI box over the SATA box?
05:37 <****> depends on the disks
05:37 <****> mikegriffin: well...
05:37 <****> Seagate 10k RPM Cheetah versus Seagate 7200RPM Barracuda
05:38 <****> the 10k rpm have much better seek time
05:38 <****> but myisam only 3GB per day?
05:38 <****> Yes, kim.
05:38 <****> gfrank: are they on hardware raid or software are they attached to same card?
05:38 <****> so every single write request will be faster. If you are out of sync like 50k seconds that won't help you.
05:38 <****> mikegriffin: i have a prepared statement and i am trying to insert integers into the database, my buffers are the correct types and i assign the pointer for the int to the bind buffer, however, the values often are zero when they get to the database
05:38 <****> Hardware RAID
05:38 <****> mikegriffin: i have printf's that verify the data is correct before it hits mysql
05:39 <****> I suppose this means I'm SOL
05:39 <****> fuse: zero or null?
05:39 <****> how do I query a database when I am doing a javascript onclick event? I know how do query a database, but I am using coldfusion, and javascript to do this
05:39 <****> gfrank: yeah
05:39 <****> ShawnRisk: see ajax
05:39 <****> isnt DELETE FROM table WHERE id IN (1,2,3) legal?
05:39 <****> okay
05:39 <****> gfrank: innodb is expected to be larger but not 10 times
05:39 <****> mikegriffin: the columns are given the NOT NULL option
05:40 <****> so zero
05:40 <****> Yeah, we're trying to determine if SATA disks will meet our needs before we jump ship and go colocated.
05:40 <****> gfrank: do you have any transaction that did not end
05:40 <****> fireangel: depends on the legislation you're in.
05:40 <****> Would rather not buy 48 SATA disks to find that they can't meet our needs.
05:40 <****> standard sql ;)
05:40 <****> gfrank: how often is your replication thread locked?
05:40 <****> By that you mean reading data from the master?
05:41 <****> no
05:41 <****> the thread that applies the binlog
05:41 <****> mikegriffin: just to let you know the ajax room is dead always
05:41 <****> fuse: are you inserting directly or using anything like concat?
05:41 <****> Nils: Mind speaking in private chat?
05:41 <****> I think no one minds if we go on in public?
05:41 <****> I suppose
05:42 <****> mikegriffin: the query is a stored procedure, inserting directly
Total 25 pages. You are browsing page 4/25.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last