mysql 4.1 server optimization
Hi
I am using Mysql 4.1 on
CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I think it is not using the 2nd
processor)
Memory 4GB
I think that the read from the HDD is 50mb per 1 sec, but I am not sure.
I have very big problem with performance, because there are very bad search
queries, but even worse after I tuned some of the mysql system variables
There are 2 main tables(MyIsam) that are in many to many table relation ship
3 million records up to now, the table has about 35 well typed columns.
tbl_items 0 PRIMARY 1 item_id A 2594514 \N \N
BTREE
tbl_items 1 type_id 1 type_id A 2 \N \N
BTREE
tbl_items 1 type_id 2 item_rights A 4 \N
\N YES BTREE
tbl_items 1 source 1 item_source A 1652\N
\N BTREE
tbl_items 1 date_created1 date_createdA 10174
\N \N BTREE
tbl_items 1 set_id 1 set_id A 96093 \N \N
BTREE
tbl_items 1 set_id 2 date_createdA 152618 \N
\N BTREE
tbl_items 1 simple_search 1 caption \N 1 \N
\N YES FULLTEXT
tbl_items 1 simple_search 2 keywords\N 1
\N \N YES FULLTEXT
6 million but it’s has only 3 columnst (twi int(11) and 1 int(2)
tbl_items_categories0 PRIMARY 1 id A \N \N
\N BTREE
tbl_items_categories0 PRIMARY 2 item_id A \N \N
\N BTREE
tbl_items_categories0 PRIMARY 3 category_id A 5729087
\N \N BTREE
tbl_items_categories1 FK_item_category_idx1 category_id
A 63 \N \N BTREE
tbl_items_categories1 item_id 1 item_id A 5729087 \N
\N BTREE
The queries are:
SELECT SOME FIELDS
FROM tbl_items i WHERE (MATCH(i.caption, i.keywords) AGAINST ('london' IN
BOOLEAN MODE ))
AND (((i.product_id = 4 OR i.product_id2 = 4))) ORDER BY i.date_created DESC
LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND (MATCH(i.caption, i.keywords) AGAINST
('london' IN BOOLEAN MODE ))
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND (MATCH(i.caption, i.keywords) AGAINST
('london' IN BOOLEAN MODE ))
AND (((i.product_id =6 OR i.product_id2 = 6) AND (icpx.category_id = 83 OR
icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =6 OR i.product_id2 = 6) AND (icpx.category_id = 83 OR
icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
I personally don’t like them, because first it can be used unions instead OR.
Also there are many parenthesis ant it will be slow for parsing.
But the worse is that when you are not using keyword(i.e. fulltext search) and
the joining with distinct + order by is killing every thing,
I see in the show processlist copy to tmp that takes from 1-3 minutes to
execute.
I remove the search without keywords for now and tuned some part of the system
variables but copy to tmp appears again.
Also the mysql is 4.1 it cannot use merge indexes, so it uses only fulltext
search index, and the ordering becames very slow, even that is the
Bat thing that causes copy to tmp to appear, and when there is no memory it
writes to disk ? that tmp table. Also
Mysql 4.1 uses 1 index per query per table that is terrible ?.
I am thinking to remake the queries with unions and to set product_id2 to index
with date_created, the same for product_id i.e
Index on iproduct_id, item_datecareted) and the same for the other.
There are some times big slow select, and after it happens write and this
blocks all other selects the appear Locked in the show processlist;
Please advice how to optimize this situation, I read that the tmp ordering can
be optimized with moving the mysql tmp
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? may be my slave is not updated in 'realtime' thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow connecting to MySQL from Windows App
Hi, I am hoping someone here can help me. I have an Ubuntu 8.04 server that has worked FINE for months. The other day, the client rebooted it. They shut it down properly and then later started it up again. (The server was not experiencing any problems when they did it, there were other reasons). Ever since then, when I try to connect to the MySQL server it is running dead slow. As in 20-30 seconds to get a connect. By the way, I just tried it with MySQL Administrator and it has problems connecting as well so it is not the connector. The server is located internally on a gigabit network. Any assistance would be greatly appreciated... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow connecting to MySQL from Windows App
please follow my earlier suggestion and place long_query_time=NumberOfSecs min_examined_row_limit=MinNumberOfRowsToExamine in my.cnf or my.ini start mysqld --log-slow-queries[=file_name] documentation available at http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html Shalom Martin Gainty __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Tue, 5 May 2009 08:43:51 -0400 From: gro...@bcconsultingservices.com To: mysql@lists.mysql.com Subject: Slow connecting to MySQL from Windows App Hi, I am hoping someone here can help me. I have an Ubuntu 8.04 server that has worked FINE for months. The other day, the client rebooted it. They shut it down properly and then later started it up again. (The server was not experiencing any problems when they did it, there were other reasons). Ever since then, when I try to connect to the MySQL server it is running dead slow. As in 20-30 seconds to get a connect. By the way, I just tried it with MySQL Administrator and it has problems connecting as well so it is not the connector. The server is located internally on a gigabit network. Any assistance would be greatly appreciated... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® goes with you. http://windowslive.com/Tutorial/Hotmail/Mobile?ocid=TXT_TAGLM_WL_HM_Tutorial_Mobile1_052009
Creation date
At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com
Re: Creation date
At 10:28 AM -0400 5/5/09, Jerry Schwartz wrote: At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Try show table status or select table_name,create_time from information_schema.tables (information_schema only exists in MySQL = 5.0, methinks). Manual references: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html http://dev.mysql.com/doc/refman/5.0/en/information-schema.html - steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Centersbedb...@ucdavis.edu | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creation date
that is one weird opening statement select * from information_schema.tables where table_name='DATABASE_NAME.TABLENAME'; Martin May the Schwartz be with you -- Spaceballs __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: jschwa...@the-infoshop.com To: mysql@lists.mysql.com Subject: Creation date Date: Tue, 5 May 2009 10:28:28 -0400 At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com _ Hotmail® goes with you. http://windowslive.com/Tutorial/Hotmail/Mobile?ocid=TXT_TAGLM_WL_HM_Tutorial_Mobile1_052009
Re: Creation date
I would have said to look at the creation date of the .frn file, but there is also a field called create date in the show table status command and the 2 dates often differ. Anyone know why they do? On Tue, May 5, 2009 at 9:28 AM, Jerry Schwartz jschwa...@the-infoshop.comwrote: At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? (my slave is not always updated in 'realtime') thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creation date
The create date in show table status is metadata held in the table itself wheras the create data on the .frm file is when that file was created - i.e. if you copy the data files (without preserving attributes) it will have a new creation date on the filesystem but the metadata of the table will not change. Andrew -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: 05 May 2009 16:38 To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Creation date I would have said to look at the creation date of the .frn file, but there is also a field called create date in the show table status command and the 2 dates often differ. Anyone know why they do? On Tue, May 5, 2009 at 9:28 AM, Jerry Schwartz jschwa...@the-infoshop.comwrote: At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
One way to do this would be to issue a 'stop slave;' on the slave you are taking a backup from just before the backup starts. Then issue a 'show slave status\G' to get the master log file and position. You can use this to setup the new slave properly. ok, thanks, I was thinking it'd be a better solution. btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? may be my slave is not updated in 'realtime' thank you in advance MAS! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
MAS! wrote: btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. -- Josh Miller, RHCE/VCP Seattle, WA Linux Solutions Provider Website: http://itsecureadmin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
Are the values of these variables all accessible via the command: show variables? Josh Miller wrote: MAS! wrote: btw, I have to get the Master_Log_File and Read_Master_Log_Pos or Relay_Master_Log_File and Exec_Master_Log_Pos to start the new slave correctly !? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creation date
Thanks, I knew it had to be something simple. Now I can clean out my save this in case you make a mistake tables. -Original Message- From: Steve Edberg [mailto:sbedb...@ucdavis.edu] Sent: Tuesday, May 05, 2009 11:00 AM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Creation date At 10:28 AM -0400 5/5/09, Jerry Schwartz wrote: At the risk of getting spanked for not finding this in the documentation, I'm asking a simple question: Can I tell when a table was created? Try show table status or select table_name,create_time from information_schema.tables (information_schema only exists in MySQL = 5.0, methinks). Manual references: http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html http://dev.mysql.com/doc/refman/5.0/en/information-schema.html - steve -- +--- my people are the people of the dessert, -- -+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Centersbedb...@ucdavis.edu | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork --- -+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backup from a slave
Lawrence Sorrillo wrote: Are the values of these variables all accessible via the command: show variables? If the Master_Log_File and Exec_Master_Log_Pos are not equivalent, you'll want to note the Exec_Master_Log_Pos value as that is the value which determines where in the binary logs you're slave is currently at. These values are accessible via 'show slave status\G'; -- Josh Miller, RHCE/VCP Seattle, WA Linux Solutions Provider Website: http://itsecureadmin.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 4.1 server optimization
Dimitar,
Just looking over the server status, it looks
like you have a high rate of temp table to disk.
Created_tmp_disk_tables 5454
Created_tmp_files 1010
Created_tmp_tables 3
I would try increasing the max_heap_table_size.
Increasing the tmp_table_size alone wouldn't be enough.
tmp_table_size 536870912
max_heap_table_size 104856576
Your key buffer usage looks pretty good.
(1- (220362*1024)/1073741824) * 100) = 79%
key_buffer_size 1073741824
key_cache_age_threshold 300
key_cache_block_size1024
key_cache_division_limit100
Key_blocks_unused 220362
What variables did you tune to make it worse?
Is this on a dedicated db machine?
Also, if you have a read heavy workload, I would
suggest increasing the query cache size and query cache limit.
We saw significant performance improvement when we tuned our query cache.
Kyong
At 12:56 AM 5/5/2009, dimitar nen4ev wrote:
Hi
I am using Mysql 4.1 on
CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I
think it is not using the 2nd processor)
Memory 4GB
I think that the read from the HDD is 50mb per 1 sec, but I am not sure.
I have very big problem with performance,
because there are very bad search queries, but
even worse after I tuned some of the mysql system variables
There are 2 main tables(MyIsam) that are in many to many table relation ship
3 million records up to now, the table has about 35 well typed columns.
tbl_items 0 PRIMARY 1 item_id
A 2594514 \N \N BTREE
tbl_items 1 type_id 1 type_id
A 2 \N \N BTREE
tbl_items 1 type_id
2 item_rights A 4 \N \N YES BTREE
tbl_items 1 source 1
item_source A 1652\N \N BTREE
tbl_items 1 date_created1
date_createdA 10174 \N \N BTREE
tbl_items 1 set_id 1 set_id
A 96093 \N \N BTREE
tbl_items 1 set_id 2
date_createdA 152618 \N \N BTREE
tbl_items 1 simple_search 1
caption \N 1 \N \N YES FULLTEXT
tbl_items 1 simple_search 2
keywords\N 1 \N \N YES FULLTEXT
6 million but itâs has only 3 columnst (twi int(11) and 1 int(2)
tbl_items_categories0 PRIMARY
1 id A \N \N \N BTREE
tbl_items_categories0 PRIMARY
2 item_id A \N \N \N BTREE
tbl_items_categories0 PRIMARY
3 category_id A 5729087 \N \N BTREE
tbl_items_categories1
FK_item_category_idx1 category_id
A 63 \N \N BTREE
tbl_items_categories1 item_id
1 item_id A 5729087 \N \N BTREE
The queries are:
SELECT SOME FIELDS
FROM tbl_items i WHERE (MATCH(i.caption,
i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id = 4 OR i.product_id2 = 4)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND
(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND
(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id =6 OR i.product_id2 = 6) AND
(icpx.category_id = 83 OR icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =6 OR i.product_id2 = 6) AND
(icpx.category_id = 83 OR icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
I personally donât like them, because first it
can be used unions instead OR.
Also there are many parenthesis ant it will be slow for parsing.
But the worse is that when you are not using
keyword(i.e. fulltext search) and the joining
with distinct + order by is killing every thing,
I see in the show processlist copy to tmp that
takes from 1-3 minutes to execute.
I remove the search without keywords for now and
tuned some part of the system
Re: mysql 4.1 server optimization
If you have the slow query log enabled, grab some
slow queries from there and run EXPLAIN.
From there, you can figure out how the optimizer is executing the query.
I found that approach to be very useful in
optimizing individual queries and making indexing decisions.
I can't quite make out what the table structure
is showing but it's generally not a good idea to over-index.
You can have a significant overhead on inserts.
We've seen something similar in production with
InnoDB and my understanding is that you can incur
even greater overhead on MyISAM.
Kyong
At 12:56 AM 5/5/2009, dimitar nen4ev wrote:
Hi
I am using Mysql 4.1 on
CPU 2 Xeon 3.0 Ghz + 2 Virtual (Hyperthreads) (I
think it is not using the 2nd processor)
Memory 4GB
I think that the read from the HDD is 50mb per 1 sec, but I am not sure.
I have very big problem with performance,
because there are very bad search queries, but
even worse after I tuned some of the mysql system variables
There are 2 main tables(MyIsam) that are in many to many table relation ship
3 million records up to now, the table has about 35 well typed columns.
tbl_items 0 PRIMARY 1 item_id
A 2594514 \N \N BTREE
tbl_items 1 type_id 1 type_id
A 2 \N \N BTREE
tbl_items 1 type_id
2 item_rights A 4 \N \N YES BTREE
tbl_items 1 source 1
item_source A 1652\N \N BTREE
tbl_items 1 date_created1
date_createdA 10174 \N \N BTREE
tbl_items 1 set_id 1 set_id
A 96093 \N \N BTREE
tbl_items 1 set_id 2
date_createdA 152618 \N \N BTREE
tbl_items 1 simple_search 1
caption \N 1 \N \N YES FULLTEXT
tbl_items 1 simple_search 2
keywords\N 1 \N \N YES FULLTEXT
6 million but itâs has only 3 columnst (twi int(11) and 1 int(2)
tbl_items_categories0 PRIMARY
1 id A \N \N \N BTREE
tbl_items_categories0 PRIMARY
2 item_id A \N \N \N BTREE
tbl_items_categories0 PRIMARY
3 category_id A 5729087 \N \N BTREE
tbl_items_categories1
FK_item_category_idx1 category_id
A 63 \N \N BTREE
tbl_items_categories1 item_id
1 item_id A 5729087 \N \N BTREE
The queries are:
SELECT SOME FIELDS
FROM tbl_items i WHERE (MATCH(i.caption,
i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id = 4 OR i.product_id2 = 4)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND
(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCTSOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =4 OR i.product_id2 = 4) AND (icpx.category_id = 11)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id AND
(MATCH(i.caption, i.keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND (((i.product_id =6 OR i.product_id2 = 6) AND
(icpx.category_id = 83 OR icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
SELECT DISTINCT SOME FIELDS
FROM tbl_items i, tbl_items_categories icpx
WHERE icpx.item_id = i.item_id
AND (((i.product_id =6 OR i.product_id2 = 6) AND
(icpx.category_id = 83 OR icpx.category_id = 87))
OR ((i.product_id = 5 OR i.product_id2 = 5) AND (icpx.category_id = 24))
OR ((i.product_id = 4 OR i.product_id2 = 4) AND (icpx.category_id = 12)))
ORDER BY i.date_created DESC LIMIT 0, 24;
I personally donât like them, because first it
can be used unions instead OR.
Also there are many parenthesis ant it will be slow for parsing.
But the worse is that when you are not using
keyword(i.e. fulltext search) and the joining
with distinct + order by is killing every thing,
I see in the show processlist copy to tmp that
takes from 1-3 minutes to execute.
I remove the search without keywords for now and
tuned some part of the system variables but copy to tmp appears again.
Also the mysql is 4.1 it cannot use merge
indexes, so it uses only fulltext search index,
and the ordering becames very slow, even that is the
Bat thing that causes copy to tmp to appear, and
when there is no memory it writes to disk ? that tmp table. Also
Re: [PHP] How to deal with identical fields in db
Tom Worster wrote: On 5/5/09 4:42 PM, Richard S. Crawford rscrawf...@mossroot.com wrote: On Tue, May 5, 2009 at 1:34 PM, PJ af.gour...@videotron.ca wrote: I'm coming up with a bit of a quandry: how to enter and retrieve an identical book title with different authors. It is rather unbelievable what contortions one finds as authors :-( like editors, associations and then the unknowns and anon y mouses. I suppose one has to get really creative... don't forget to consider the handling of anthologies :-) Well, that usually comes under editors and I have a couple of options where to enter that info: sub_title or description fields and I can always add a nbsp; for author first_name and Various or whatever for last_name :-) Anyone for tea? yes please, i'd love some. What I've done for this sort of project in the past was create separate tables for authors, books, and author relationships (e.g., author, translator, editor), then linking tables for each of those. You seriously want to do some normalization on this task; otherwise, you end up with a giant table of books, with multiple rows duplicating the title of the book, leading to a huge books table, and nobody wants that. i have a db with 10s of millions of artists, disks, songs etc. i've tried it both ways. and after 5 years working with it i still can't make up my mind which way i prefer it. i keep finding pros and cons to each approach that differ depending on what functionality i'm programming. i will never have a simple answer. so i can't help answer the original question other than to say that, for me, personally, in my opinion, i don't accept the dogma that normal forms are always good for you. they might be. it depends. it's like being dogmatic about specific foods without taking the overall diet and lifestyle into account. despite the simple dogma some may espouse, whether or not a big mac with fries is bad for you depends on many factors. in any case, it's amazing what you can do these days with one huge table and some well chosen indexes. and it's amazing how mind bending it can get when joining 5 data tables using 3 join tables. ain't that the truth ! good luck, phil. Thank you guys, for the input. Never thought so many would help so few(little me). ;-) I'm really just a little shorter than BG at 6'5 :-D Actually, I started out and still am with the db normalized. It all works quite well, it's just frustrating to have to go through all the contortions to check things. I started out with just checking the title, then had to add a check to the sub_title, (already have a check for author) but now have to add another to go with the specific book... oh, well... all a part of the learning process. :-) -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Yes, Triggers... I so rarely use them I forget they exist. On Tue, May 5, 2009 at 10:22 AM, Thomas Pundt mli...@rp-online.de wrote: Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- - Johnny Withers 601.209.4985 joh...@pixelated.net
