Re: MySQL optimization for testing purpose

On 04/03/2018 06:15 PM, Sebastien FLAESCH wrote: On 04/03/2018 05:59 PM, Sebastien FLAESCH wrote: Hi all, Looking for some configuration tips to speed execution of our non-regression tests with MySQL. We do pure functional testing, data persistence is not critical. Our tests are not using a

Re: MySQL optimization for testing purpose

On 04/03/2018 05:59 PM, Sebastien FLAESCH wrote: Hi all, Looking for some configuration tips to speed execution of our non-regression tests with MySQL. We do pure functional testing, data persistence is not critical. Our tests are not using a lot of data, but we create and drop a lot of test

MySQL optimization for testing purpose

Hi all, Looking for some configuration tips to speed execution of our non-regression tests with MySQL. We do pure functional testing, data persistence is not critical. Our tests are not using a lot of data, but we create and drop a lot of test tables. We have concurrency tests (processes loc

Re: inconsistent optimization

Hi Jim, On 8/20/2014 11:04 AM, Jim wrote: Without going into specific details on queries... Using mysql 5.1 as provided with CentOS6, I've noticed some queries providing what I can best explain as inconsistent optimization. The database can be quieted to just controlled queries and at

Re: inconsistent optimization

innodb On 8/20/2014 1:22 PM, Martin Gainty wrote: Jim/Jaime What engine are you implementing?/ Qual mecanismo de MySQL que você está implementando? Saludos desde Sud America Martín Date: Wed, 20 Aug 2014 13:54:46 -0300 Subject: Re: inconsistent optimization From: edua...@gerencianet.com.br

RE: inconsistent optimization

Jim/Jaime What engine are you implementing?/ Qual mecanismo de MySQL que você está implementando? Saludos desde Sud America Martín > Date: Wed, 20 Aug 2014 13:54:46 -0300 > Subject: Re: inconsistent optimization > From: edua...@gerencianet.com.br > To: j...@lowcarbfriends.com

Re: inconsistent optimization

> Without going into specific details on queries... > > Using mysql 5.1 as provided with CentOS6, I've noticed some queries > providing what I can best explain as inconsistent optimization. The > database can be quieted to just controlled queries and at times the same > query

inconsistent optimization

Without going into specific details on queries... Using mysql 5.1 as provided with CentOS6, I've noticed some queries providing what I can best explain as inconsistent optimization. The database can be quieted to just controlled queries and at times the same query will return very qu

RE: LIKE sql optimization

Done. Thand you very much! Zhigang _ From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] Sent: Wednesday, February 12, 2014 5:30 PM To: Morgan Tocker; Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization On 12/02/2014 13:16, Morgan Tocker wrote: Hi

Re: LIKE sql optimization

On 12/02/2014 13:16, Morgan Tocker wrote: > Hi Zhigang, > > On Feb 11, 2014, at 8:48 PM, Zhigang Zhang wrote: > >> I want to know the reason, in my opinion, to scan the smaller index data has >> better performance than to scan the whole table data. > I think I understand the question - you are as

RE: LIKE sql optimization

Thank you very much! Zhigang -Original Message- From: Morgan Tocker [mailto:morgan.toc...@oracle.com] Sent: Wednesday, February 12, 2014 10:16 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization Hi Zhigang, On Feb 11, 2014, at 8:48 PM

Re: LIKE sql optimization

Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang wrote: > I want to know the reason, in my opinion, to scan the smaller index data has > better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching

RE: LIKE sql optimization

I checked a myisam table index, the index is a copy of the whole field. Zhigang -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 12, 2014 10:02 AM To: mysql@lists.mysql.com Subject: Re: LIKE sql optimization because a index

Re: LIKE sql optimization

smaller index data >> > has >> > better performance than to scan the whole table data. >> > >> > >> > >> > >> > >> > zhigang >> > >> > >> > >> > _ >> > >> > From: Mathieu Desharnais [mai

Re: LIKE sql optimization

ailto:mdesharn...@diffusion.cc] > > Sent: Wednesday, February 12, 2014 9:41 AM > > To: Zhigang Zhang; mysql@lists.mysql.com > > Subject: Re: LIKE sql optimization > > > > > > > > Sql database doesn't use index in like statement if it starts with % .

Re: LIKE sql optimization

data has > better performance than to scan the whole table data. > > From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] > Sent: Wednesday, February 12, 2014 9:41 AM > To: Zhigang Zhang; mysql@lists.mysql.com > Subject: Re: LIKE sql optimization > > Sql database doe

Re: LIKE sql optimization

t; _ > > From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] > Sent: Wednesday, February 12, 2014 9:41 AM > To: Zhigang Zhang; mysql@lists.mysql.com > Subject: Re: LIKE sql optimization > > > > Sql database doesn't use index in like statement if it start

RE: LIKE sql optimization

@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2

Re: LIKE sql optimization

Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang : > For example: > > > > Select

Re: LIKE sql optimization

Am 12.02.2014 02:23, schrieb Zhigang Zhang: > For example: > > Select * from T where col like ‘%abcd’; > > The table T is myisam table and we created a index on col. > > As we known, this like sql does not use the index created on col, it confuse > me, why? > > I think in mysiam engine, the i

LIKE sql optimization

For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to op

RE: Query Optimization

r 16, 2012 12:36 AM > To: mysql@lists.mysql.com > Subject: Query Optimization > > Hi All, > > Consider a scenario, I have table XYZ which contains value follow BLUE RED > GREEN NULL > > following are queries we can use get this values > > 1. SELECT * FROM XYX WH

Re: Query Optimization

RED > GREEN > NULL > > following are queries we can use get this values > > 1. SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); > 2. SELECT * FROM XYZ WHERE VAL IS NOT NULL > 3. SELECT * FROM XYZ WHERE VAL = 'BLUE' OR VAL='R

RE: forcing mysql to use batched key access (BKA) optimization for joins

, 2012 5:20 PM To: mysql@lists.mysql.com Subject: Re: forcing mysql to use batched key access (BKA) optimization for joins >>>> 2012/04/10 15:58 -0400, Stephen Tu >>>> select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctba

Re: forcing mysql to use batched key access (BKA) optimization,for joins

Hi Stephen, 2012/04/10 15:58 -0400, Stephen Tu >| id | select_type | table| type | possible_keys | key >| key_len | ref | rows| >Extra | >++-+--++-

Re: forcing mysql to use batched key access (BKA) optimization for joins

2012/04/10 15:58 -0400, Stephen Tu select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderke

Re: query optimization

Your outer query "select cpe_mac,max(r3_dt) from rad_r3cap", is doing a full table scan, you might want to check on this and use a "WHERE" condition to use indexed column On Fri, Sep 23, 2011 at 12:14 AM, supr_star wrote: > > > I have a table with 24 million rows, I need to figure out how to op

query optimization

 I have a table with 24 million rows, I need to figure out how to optimize a query.  It has to do with mac addresses and radius packets - I want to see the # of connections and the min/max date. So I basically want all this data:   select cpe_mac,count(*) c,min(r3_dt) mindt,max(r3_dt) maxdt, m

Re: Query Optimization

Ah I see. Well thanks for your assistance! -Brandon On 09/08/2011 05:21 PM, Mihail Manolov wrote: From the manual: "The default behavior for UNION is that duplicate rows are removed from the result." On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote: Mihail, Thanks so much! I modified yo

Re: Query Optimization

>From the manual: "The default behavior for UNION is that duplicate rows are >removed from the result." On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote: > Mihail, > > Thanks so much! I modified your example to include the proper ORDER BY and > LIMIT clauses and this, so far, is running super

Re: Query Optimization

Mihail, Thanks so much! I modified your example to include the proper ORDER BY and LIMIT clauses and this, so far, is running super fast (0.0007 seconds). Question, if a record's open_dt is between the range AND the close_dt is between the range as well, will the UNION output the record twic

Re: Query Optimization

Andrew, Generally there is only 1 user performing the complicated SELECT query at a time, however the background process that fills the table is constantly doing a fast SELECT (0.3 seconds) and a subsequent UPDATE. Basically whenever a connection is closed on the firewall, the bg process

Re: Query Optimization

How about: SELECT sc.open_dt, sc.close_dt, sc.protocol, INET_NTOA(sc.src_address) AS src_address, sc.src_port, INET_NTOA(sc.dst_address) AS dst_address, sc.dst_port, sc.sent, sc.rcvd, spm.desc AS src_port_desc,

Re: Query Optimization

Partitioning isn't a bad idea for this however I'm still thinking about your dataset size and possible hardware limitations. It's not likely going to fit into relevant buffers/memory so you're going to be on disk more then you want. You're probably creating temporary tables like crazy and I would b

Re: Query Optimization

Thanks for the idea Derek, however given the following query my EXPLAIN output is identical: SELECT sc.open_dt, sc.close_dt, sc.protocol, INET_NTOA(sc.src_address) AS src_address, sc.src_port, INET_NTOA(sc.dst_address) AS dst_address, sc.ds

Re: Query Optimization

Mihail, I have considered this but have not yet determined how best to go about partitioning the table. I don't think partitioning by dst_address or src_address would help because most of the queries do not filter on IP address (except very specific queries where the end-user is searching the

Re: Query Optimization

Correct me if I'm wrong. You're wanting to get all records that have an open_date or a close_date between two times. If that's correct, you might be able to get an index_merge by doing a query like: WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting time)<=close_dt<=(ending time))

Re: Query Optimization

Andy, The queries take minutes to run. MySQL is 5.1.54 and it's running on Ubuntu server 11.04. Unfortunately the machine only has 2GB of RAM but no other major daemons are running on the machine. We are running RAID 1 (mirroring) with 1TB drives. The tables in question here are all MyISAM

Re: Query Optimization

If you're running version 5.1+ you may wanna take a look at table partitioning options you may have. On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote: > Thanks for the reply Andy. Unfortunately the users will be selecting varying > date ranges and new data is constantly coming in, so I am not

Re: Query Optimization

I don't think I saw any query timings in the emails (maybe I missed them). What version of MySQL are you currently using? What does the explain look like when your remove the limit 10? Is your server tuned for MyISAM or InnoDB? What kind of disk setup is in use? How much memory is in your machine?

Re: Query Optimization

Thanks for the reply Andy. Unfortunately the users will be selecting varying date ranges and new data is constantly coming in, so I am not sure how I could archive/cache the necessary data that would be any more efficient than simply using the database directly. On 09/08/2011 02:16 PM, Andre

Re: Query Optimization

Thinking outside the query, is there any archiving that could happen to make your large tables kinder in the range scan? Andy On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps wrote: > On 09/01/2011 01:32 PM, Brandon Phelps wrote: > >> On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote: >> >>> On 9

Re: Query Optimization

On 09/01/2011 01:32 PM, Brandon Phelps wrote: On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote: On 9/1/2011 09:42, Brandon Phelps wrote: On 09/01/2011 04:59 AM, Jochem van Dieten wrote: > > ... > > WHERE > > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30 00:00:00') > > AND (open

Re: Query Optimization

On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote: On 9/1/2011 09:42, Brandon Phelps wrote: On 09/01/2011 04:59 AM, Jochem van Dieten wrote: > > ... > > WHERE > > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30 00:00:00') > > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '20

Re: Query Optimization

On 9/1/2011 09:42, Brandon Phelps wrote: On 09/01/2011 04:59 AM, Jochem van Dieten wrote: > > ... > > WHERE > > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30 00:00:00') > > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30 12:36:53') > In that case your logic

Re: Query Optimization

On 09/01/2011 04:59 AM, Jochem van Dieten wrote: > > SELECT > >sc.open_dt, > >sc.close_dt, > >sc.protocol, > >INET_NTOA( sc.src_address ) AS src_address, > >sc.src_port, > >INET_NTOA( sc.dst_address ) AS dst_address, > >sc.dst_port, > >

Re: Query Optimization

On Aug 30, 2011 6:46 PM, "Brandon Phelps" wrote: > SELECT >sc.open_dt, >sc.close_dt, >sc.protocol, >INET_NTOA( sc.src_address ) AS src_address, >sc.src_port, >INET_NTOA( sc.dst_address ) AS dst_address, >sc.dst_port, >sc.sent, >

Re: Query Optimization

There are a few things gonig on, but mainly it is the ORs that are killing you. As your require OR to examine two distinct columns, both of equal relevance to the query, MySQL: is left with no choice but to do a full table scan on what might be (at a guess) a very larger table. No amount of inde

Query Optimization

Hello, I have the following query I'd like to optimize a bit: SELECT sc.open_dt, sc.close_dt, sc.protocol, INET_NTOA( sc.src_address ) AS src_address, sc.src_port, INET_NTOA( sc.dst_address ) AS dst_address, sc.dst_port, sc.sent,

Re: Query Optimization

> From: Brandon Phelps > > I am curious if there is any way I can better optimize the below query, as > currently it takes this query around 10 seconds to run but I am sure this > will get slower and slower as the database grows. You need an index on `close_dt`. > SELECT > open_dt, >

Re: Query Optimization

On 8/10/2011 1:01 PM, Brandon Phelps wrote: Hello all, I am using the query below and variations of it to query a database with a TON of records. Currently the database has around 11 million records but it grows every day and should cap out at around 150 million. I am curious if there is an

Re: Query Optimization

Thanks Singer, this took my query down to 0.0007, perfect! I wasn't aware a single index of multiple columns would work when one of the columns was in the WHERE clause and the other in the ORDER BY clause. Learn something new every day I guess! On 08/10/2011 02:03 PM, Singer X.J. Wang wrote

Query Optimization

Hello all, I am using the query below and variations of it to query a database with a TON of records. Currently the database has around 11 million records but it grows every day and should cap out at around 150 million. I am curious if there is any way I can better optimize the below query,

RE: optimization strategies based on file-level storage

On Fri, June 17, 2011 07:11, Jerry Schwartz wrote: >>-Original Message- > > >>What I was really trying to figure out was why it takes me 4 hours to >>add a new column to my 22-million-row table, and whether a different >>table design can avoid that problem. That reply in the forum says

RE: optimization strategies based on file-level storage

>-Original Message- >What I was really trying to figure out was why it takes me 4 hours to >add a new column to my 22-million-row table, and whether a different >table design can avoid that problem. That reply in the forum says, >"ALTER TABLE ... ADD COLUMN will always copy the entire ta

Re: optimization strategies based on file-level storage

At 10:47 PM 6/16/2011, Johan De Meersman wrote: - Original Message - > From: "Bennett Haselton" > > Do you happen to know the answer to my other problem -- if I have > TEXT and BLOB columns but all my other columns are fixed-length, can > I still get the benefit of faster lookups resul

Re: optimization strategies based on file-level storage

- Original Message - > From: "Bennett Haselton" > > Do you happen to know the answer to my other problem -- if I have > TEXT and BLOB columns but all my other columns are fixed-length, can > I still get the benefit of faster lookups resulting from fixed-length > rows, if each row just c

Re: optimization strategies based on file-level storage

At 11:45 AM 6/14/2011, Johan De Meersman wrote: - Original Message - > From: "Bennett Haselton" > > modifications. (For example, the question I asked earlier about > whether you can declare extra space at the end of each row that is > "reserved for future columns".) That question I c

Re: Data missing after field optimization

e benefit. I stand corrected. Still, as you've noticed, don't change the design of an existing application without thoroughly testing the consequences :-p - Original Message - > From: sono...@fannullone.us > To: mysql@lists.mysql.com > Sent: Tuesday, 14 June, 2011 7:34

Re: optimization strategies based on file-level storage

- Original Message - > From: "Bennett Haselton" > > modifications. (For example, the question I asked earlier about > whether you can declare extra space at the end of each row that is > "reserved for future columns".) That question I can answer: you can't "reserve" space, but if you

Re: Data missing after field optimization

On Jun 7, 2011, at 10:43 PM, Johan De Meersman wrote: > Where did you find the advice about setting columns NOT NULL? It took me awhile, but I just found it again, in case anyone is interested: http://dev.mysql.com/doc/refman/5.0/en/data-size.html 7.8.1. Make Your Data as Small as Pos

RE: optimization strategies based on file-level storage

Carlos, > Date: Tue, 14 Jun 2011 01:44:47 -0700 > To: mysql@lists.mysql.com > From: benn...@peacefire.org > Subject: optimization strategies based on file-level storage > > I'm looking for some tips & tricks documentation that explains how > different data types in

optimization strategies based on file-level storage

make room for the new column (although presumably you would still have to *write* to the place in reach row where the new column had just been defined, to fill it in with its default value). In particular, I'm not looking for a list of optimization tricks, so much as a document that explains how

RE: Data missing after field optimization

>> If that's all you did, you indeed 'removed the default NULL' but did not >specify another default. Hence, if you don't explicitly specify a value in >your >insert statement, the insert can not happen as the server doesn't know what >to >put there and is explicitly disallowed from leaving the

Re: Data missing after field optimization

On Jun 7, 2011, at 10:43 PM, Johan De Meersman wrote: > If that's all you did, you indeed 'removed the default NULL' but did not > specify another default. Hence, if you don't explicitly specify a value in > your insert statement, the insert can not happen as the server doesn't know > what to p

Re: Data missing after field optimization

- Original Message - > From: sono...@fannullone.us > > Yes. That's all I did. If that's all you did, you indeed 'removed the default NULL' but did not specify another default. Hence, if you don't explicitly specify a value in your insert statement, the insert can not happen as the serv

Re: Data missing after field optimization

> On Jun 6, 2011, at 10:06 PM, Johan De Meersman wrote: > > What exactly do you mean by "removing the NULL default"? Did you set > > your colums NOT NULL? > > Yes. That's all I did. In stead of getting info drop-by-drop, you might want to share the output of SHOW CREATE TABLE...,, but my

Re: Data missing after field optimization

On Jun 6, 2011, at 10:06 PM, Johan De Meersman wrote: > What exactly do you mean by "removing the NULL default"? Did you set your > colums NOT NULL? Yes. That's all I did. Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://list

Re: Data missing after field optimization

- Original Message - > From: sono...@fannullone.us > > description? Why would removing the NULL default cause data to be > lost? What exactly do you mean by "removing the NULL default"? Did you set your colums NOT NULL? -- Bier met grenadyn Is als mosterd by den wyn Sy d

Data missing after field optimization

Hopefully I won't look like too much of a numbskull here but after reading some sites on table optimization, I decided to remove the NULL as default on the fields in my products table. I thought everything went well until I realized that we hadn't received any orders

Re: Super active table optimization

Am 19.02.2011 16:59, schrieb Bryan Cantwell: > I have an innodb table with 200 million rows and growing. more interesting is the db-size! > innodb_buffer_pool_size = 768M roo small, in the best case it is as large as the database or so large as pissoble to avoid swapping > innodb_log_file_size

Re: Super active table optimization

1. Which version of MySQL? Judging from your my.cnf it's not MariaDB 5.2 or Percona Server? They have very good enhancement, particularly in the high concurrency innodb atmosphere. InnoDB is a lot more tunabl;e in those versions. Switching from a stock mysql version is adrop-in replacement, so if t

Super active table optimization

I have an innodb table with 200 million rows and growing. It is a highly active table with tons of inserts and updates at all times. I notice a select query I test on that table is 0.01 seconds or less when all the inserts/updates are OFF. But when I throttle up the writes to the table, the selec

Re: some problem of InnoDB performance optimization

HI Peter: Thanks for your answer. I have understand your answer. Thank you very much. ―― Best regards Timo Seven blog: http://zauc.wordpress.com #请翻墙浏览，或者指定hosts到74.200.243.252### UNIX System Admin 2010/3/23 Peter Zaitsev > Hi, > > Lets see If I can help.

Re: some problem of InnoDB performance optimization

Hi, Lets see If I can help. Overly long queries (transactions > in general) are bad for performance as a lot of unpurged versions > accumulate. > > In this sentence, I don't know the meaning about 'unpureged version > accumulate' > When rows are updated new versions are created. They are later

some problem of InnoDB performance optimization

Hi everyone: I read the presentation about InnodDB performance optimization what Heikki Tuuri written in april23 2007. But now I have some sentences don't know how to understanding. Can you help me? Overly long queries (transactions in general) are bad for performance as a lot of unp

Re: Need help with query optimization

It may only be returning 51 rows but its having to read significantly more. Get rid of the derived table join if possible. Something like SELECT TAP.ID , M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType,

Re: Need help with query optimization

Can you please show us the indexes on both the tables. regards anandkl On Tue, Mar 16, 2010 at 11:47 PM, Jesse wrote: > I have the following query: > > SELECT TAP.ID , M.UID, TAP.FirstName, TAP.MI, > TAP.LastName, TAP.State, > TAP.SchoolName, TAP.StateApproved, TAP.DiplomatAppro

Need help with query optimization

I have the following query: SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID=TAP.CurrentMemberID JOIN Chapters

Complex SQL optimization vs. general-purpose language

Any SQL rewriting gurus know how I might be able to optimize this query? The schema: mysql> show columns from transactionlog; +---+---+--+-+-++ | Field | Type | Null | K

Re: optimization

les, would I? >> >> - Original Message - From: "Keith Murphy" >> To: >> Sent: Tuesday, January 26, 2010 11:06 AM >> Subject: Re: optimization >> >> >> >> ♫ >> I would recommend the same to you about reading High Perf.

Re: optimization

, January 26, 2010 11:06 AM > Subject: Re: optimization > > > > ♫ > I would recommend the same to you about reading High Perf. MySQL as Baron, > et al wrote a great book about performance on MySQL. That being said, it > has > been my experience that in 99% of client cases t

Re: optimization

To: Sent: Tuesday, January 26, 2010 11:06 AM Subject: Re: optimization ♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really

Re: optimization

ian lenny (5.0.1). I have >>> databases >>> for drupal, moodle, spamassassin, horde3, and a small database for >>> departmental stuff. >>> >>> The problem is that inserts/updates are sometimes very slow, on the order >>> of >>> a minute. I a

Re: optimization

hing wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql c

Re: optimization

de3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced be

Re: optimization

he problem is that inserts/updates are sometimes very slow, on the order of > a minute. I am hoping somebody can sspot something wrong in my config. > Here's the optimization settings section (for your convenience). The whole > my.cnf is reproduced below that: Are your databases using MyI

optimization

, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: key_buffer = 256M max_allowed_packet = 16M max_connections=2000 myisam_sort_buffer_size

Re: mysql server optimization

; *From:* jingtian.seu...@gmail.com [mailto:jingtian.seu...@gmail.com] *On > Behalf Of *TianJing > *Sent:* 15 December 2009 12:43 > *To:* Daisley, John (Burton) > > *Cc:* mysql@lists.mysql.com > *Subject:* Re: mysql server optimization > > yes,you are right,the longblob is already

RE: sql file system + optimization

: john.dais...@butterflysystems.co.uk === Sent via HP IPAQ mobile device -Original Message- From: Steven Staples Sent: 15 December 2009 14:37 To: mysql@lists.mysql.com Subject: sql file system + optimization Ok... in the file system, my MySQL files are located in /var

sql file system + optimization

Ok... in the file system, my MySQL files are located in /var/lib/mysql/ I have a database (lets say 'test') so it gets its own directory /var/lib/mysql/test/ Now, all the tables go in this folder. (I know, we *should* all know this...) if the database is on another disk, could it increase performa

RE: mysql server optimization

ingtian.seu...@gmail.com] On Behalf Of TianJing Sent: 15 December 2009 12:43 To: Daisley, John (Burton) Cc: mysql@lists.mysql.com Subject: Re: mysql server optimization yes,you are right,the longblob is already move to a separate table fdna,it is about 10Gb/database the fdata is about 30Gb

Re: mysql server optimization

; Inspired Gaming Group Plc > > Direct Dial +44 (0)1283 519244 > Telephone +44 (0)1283 512777 ext 2244 > Mobile +44 (0)7812 451238 > > Email john.dais...@llg.co.uk > > www.inspiredgaminggroup.com > > > -----Original Message- > From: TianJing > Sent: 15 Dec

RE: mysql server optimization

244 Telephone +44 (0)1283 512777 ext 2244 Mobile +44 (0)7812 451238 Email john.dais...@llg.co.uk www.inspiredgaminggroup.com -Original Message- From: TianJing Sent: 15 December 2009 10:28 To: mg_s...@hotmail.com Cc: mysql@lists.mysql.com Subject: Re: mysql server optimization i use MyISA

Re: mysql server optimization

r of factors which causes MySQL to use on disk temporary > tables instead of in memory tables. (If there a BLOB or TEXT columns in the > table for example). > > As a starting point you could (if you have the memory in your box) try > increasing the values for tmp_table_size and max_hea

RE: mysql server optimization

server optimization Dear all, i am nowing having a problem with the mysql server optimization, i have 20 database on a server,each database is about 80Gb,the sql seems very slow,almost > 5s.and the server i/o is so high,when i check the processlist,the 'copying to tmp table' s

Re: Optimization suggestions

. Baroda - 390007 Gujarat, India Personally I'm always ready to learn, although I do not always like being taught From: Gavin Towey To: Sudhir N ; Mysql Sent: Tue, 15 December, 2009 2:16:53 AM Subject: RE: Optimization suggestions Id should probably

mysql server optimization

Dear all, i am nowing having a problem with the mysql server optimization, i have 20 database on a server,each database is about 80Gb,the sql seems very slow,almost > 5s.and the server i/o is so high,when i check the processlist,the 'copying to tmp table' state takes a long time. i

RE: Optimization suggestions

y, December 14, 2009 10:31 AM To: Mysql Subject: Optimization suggestions I have following table structure, I have to use merge storage engine. Please have a look, and provide feedback if theres some thing wrong or if there's space for optimization. /*Table structure for table `messa

Re: Optimization suggestions

k, and provide feedback if theres some thing wrong or if > there's space for optimization. > > > /*Table structure for table `messages2009` */ > > CREATE TABLE `messages2009` ( > `id` varchar(36) NOT NULL default '', > `folderid` varchar(36) NOT NULL defaul

  1   2   3   4   5   6   >