In my old post there is a bug when run in MySQL 5.1.30 and old, because the status variable Queries was added in MySQL 5.1.31. So i change to choose between Queries and Questions status variables, and I think the Queries represent more accurate result.
http://forge.mysql.com/tools/tool.php?id=217
By the way:
# Queries
The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. This variable was added in MySQL 5.1.31.
# Questions
The number of statements executed by the server. As of MySQL 5.1.31, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable.
Sponsored by Hosting.com, provider of San Fransisco colocation services
Thursday, October 01, 2009
Monday, September 28, 2009
Palestinians storm foiled by Jews of the Far East after violent confrontations on the eve of Eid «forgiveness»
Translated via Google Translate from here
Palestinians storm foiled by Jews of the Far East after violent confrontations on the eve of Eid «forgiveness»
9 injured members of the Israeli police... And 40 Palestinians
Palestinian flying victory sign to a group of Israeli police officers after clashes broke out yesterday in the Al-Aqsa Mosque between Palestinians and Jewish extremists (a. B)
Ramallah: the struggle of customer Oman: Mohamed Da'mp
Frustrated Palestinian demonstrators gathered yesterday in the Al-Aqsa Mosque, the attempts by extremist Jewish settlers stormed the mosque and pray the following calls launched by extremist Jewish groups to pray at al-Aqsa Day «Kippur» (Atonement) Jewish, which falls on Thursday.
Hundreds of Palestinians clashed with the settlers and the Israeli police inside and outside the yards maximum, and these clashes were renewed several times, after the entry of dozens of settlers to the squares of the maximum am, and led to clashes injuring about 40 Palestinians and 9 of the Israeli policemen were wounded and bruised. The first violent clashes broke out yesterday morning in the al-Aqsa mosque among the faithful who made pilgrimages to the mosque early after calls for protection, and the Israeli police and Jewish extremists, who stormed the mosque after the closure of gates.
Extremists deliberately camouflage, clothing Bartdaihm tourists, but ordered them uncovered, Vhagmehm Palestinians with stones and shoes and chairs.
And defended the extremists and the police returned fire, resulting in the injury of two serious injuries of 13 patients. Acbakat and renewed again before the noon prayers at the door of the Council and the door of humility, among hundreds of Palestinians who tried to enter the Aqsa Mosque, Israeli police prevented them, which allowed those who are above 50 years old to enter alone, and prevented others and separated by bullets and tear gas.
Clashes started again on a third time after noon prayers, when the Lions Gate and the Damascus Gate, reportedly angry demonstrators hurled empty bottles at Israeli police and stone. A large number of children and women, bruises, and cases of suffocation, after this confrontation.
The number of wounded Palestinians to between 40 injured by rubber bullets or who has difficulty in breathing or by the use of batons, while 9 of the injured policemen with stones. In the meantime, Israeli police arrested 10 Palestinian demonstrators. Israeli authorities have deployed large forces in the holy city, and at the gates of the maximum, and turned the old city to a military barracks. The commander of the Jerusalem Police Brigade, General Aharon Franco, said that there was no intention to change the status quo on the Temple Mount, saying «it is the right of Jews to pray in the plaza, like the safety of Muslims who performed their religious rites during the holy month of Ramadan on the Temple Mount without interference ».
And carrying the police chief points of the launch of Islamic remarks have led to the tense situation, and said that talk of Al-Aqsa Mosque in danger of not unfounded.
Franco «The police will strike with an iron fist all of a potential breach of public order in the vicinity of the holy places in Jerusalem».
Israeli police were placed, in the northern cities of Israel, where Arabs, alert, fearing that the demonstrations against the storming of Aqsa, said the commander of the North in the police, Shimon Koren, will be the deployment of enhanced police and especially in the areas of Acre, Nazareth and Wadi Ara, warning that «Police will not tolerate any attempt to disrupt public order».
On the eve of Yom Kippur, the last year, the youths attacked in the town of Acre, an Arab man drove his car into the predominantly Jewish neighborhood in contravention of the ritual that day. Triggering clashes during which several houses and shops of major damage.
And assaulted Israeli police, yesterday, the Chairman of the Board of Awqaf, Sheikh Abdul Azim Salhab, as he entered the Al Aqsa Mosque, from the Lions Gate, also prevented both from Sheikh Ikrima Sabri, head of the Supreme Islamic Council, enter the maximum, and Hatem Abdel Qader, Minister of Jerusalem Affairs former from entering the Old City, and hindered the work of medical personnel, and evacuate the injured from inside the mosque.
And raised the maximum storm wave of anger and the official Palestinian people, Hamas said it would not stand idly by, and its armed wing, vowed to respond to storm Al-Aqsa, and demanded by leading the launch of a third intifada.
The Fatah movement, has requested the home fans continue to address the Jewish extremists, and urged the world to shoulder its responsibilities. For its part, Islamic Jihad said that the decision of the aggression against the Al-Aqsa has to do with the decision of the central one is an Israeli escalation in the region.
And carrying Sheikh Mohammed Hussein, the Grand Mufti of Jerusalem and the Palestinian regions, and preacher of Al-Aqsa Mosque, Israeli occupation authorities responsibility for the consequences of intrusions as a sponsor of extremist groups.
He accused the minister of Awqaf and Religious Affairs, Mahmoud al-Habash, the Israeli police to exercise «state terrorism», saying that this behavior is part of an Israeli attempt to undermine international efforts to achieve peace. The militant group staged last night, big rallies in Gaza in solidarity with the maximum. The events and the mind, incursions former Israeli Prime Minister Ariel Sharon to Al Aqsa mosque on September 27 (September) 2000, which raised the sentiments of Arabs and Muslims, and led to the outbreak of the uprising known Aqsa intifada that lasted for several years.
The Israeli authorities imposed yesterday, including a security cordon on the West Bank will continue until Thursday evening, the expiry date of the Jewish fast of Yom Kippur.
During this period did not allow Palestinians to enter Israel only in humanitarian cases and medical special, as close as the King Hussein Bridge to the movement of passengers and trucks, the Palestinians will not be able to travel anywhere, or to return to the West, even today.
Furthermore, the Jordanian government strongly condemned, and expressed their dismay and condemnation of the Israeli occupation forces stormed Al-Aqsa Mosque, the launching of tear gas and rubber bullets against unarmed worshipers.
The spokesman for the Jordanian Government and the Minister of State for Media Affairs and Communications Nabil Sharif, said that Jordan rejects any prejudice by the Israeli occupying forces in Jerusalem and the sanctity of Al Aqsa Mosque, which contained the provisions of international law, norms and conventions and the relevant resolutions, which protects houses of worship and congregations of any violation.
And denounced al-Sharif said in a statement issued yesterday, repeated intrusions by Jewish groups and Israeli forces to the Al-Aqsa Mosque, pointing out that this provocative action that would increase tension, and the potential to lead to further acts of violence that threaten security and stability in the region.
He was surprised at the timing of this break-Sharif and the threat of incitement to violence at a time when the focus of all international efforts towards the resumption of negotiations aimed at finding a solution to the Palestinian-Israeli conflict and the establishment of an independent Palestinian state with its capital in Jerusalem.
Al-Sherif confirmed the role of Jordan and the duty to preserve the holy sites in Jerusalem and protect them from any violation, emphasizing that the government will make all efforts and ways they deem appropriate to ensure protection of these sanctuaries.
Meanwhile, Foreign Ministry summoned the Chargé d'affaires of Jordan on the Israeli in Amman, and requested the transfer of protest and condemn the Government of Jordan on the strong Israeli occupation forces stormed Al-Aqsa Mosque and the dismay and surprise at this blatant attack on the worshipers, and the timing of separation.
Palestinians storm foiled by Jews of the Far East after violent confrontations on the eve of Eid «forgiveness»
9 injured members of the Israeli police... And 40 Palestinians
Palestinian flying victory sign to a group of Israeli police officers after clashes broke out yesterday in the Al-Aqsa Mosque between Palestinians and Jewish extremists (a. B)
Ramallah: the struggle of customer Oman: Mohamed Da'mp
Frustrated Palestinian demonstrators gathered yesterday in the Al-Aqsa Mosque, the attempts by extremist Jewish settlers stormed the mosque and pray the following calls launched by extremist Jewish groups to pray at al-Aqsa Day «Kippur» (Atonement) Jewish, which falls on Thursday.
Hundreds of Palestinians clashed with the settlers and the Israeli police inside and outside the yards maximum, and these clashes were renewed several times, after the entry of dozens of settlers to the squares of the maximum am, and led to clashes injuring about 40 Palestinians and 9 of the Israeli policemen were wounded and bruised. The first violent clashes broke out yesterday morning in the al-Aqsa mosque among the faithful who made pilgrimages to the mosque early after calls for protection, and the Israeli police and Jewish extremists, who stormed the mosque after the closure of gates.
Extremists deliberately camouflage, clothing Bartdaihm tourists, but ordered them uncovered, Vhagmehm Palestinians with stones and shoes and chairs.
And defended the extremists and the police returned fire, resulting in the injury of two serious injuries of 13 patients. Acbakat and renewed again before the noon prayers at the door of the Council and the door of humility, among hundreds of Palestinians who tried to enter the Aqsa Mosque, Israeli police prevented them, which allowed those who are above 50 years old to enter alone, and prevented others and separated by bullets and tear gas.
Clashes started again on a third time after noon prayers, when the Lions Gate and the Damascus Gate, reportedly angry demonstrators hurled empty bottles at Israeli police and stone. A large number of children and women, bruises, and cases of suffocation, after this confrontation.
The number of wounded Palestinians to between 40 injured by rubber bullets or who has difficulty in breathing or by the use of batons, while 9 of the injured policemen with stones. In the meantime, Israeli police arrested 10 Palestinian demonstrators. Israeli authorities have deployed large forces in the holy city, and at the gates of the maximum, and turned the old city to a military barracks. The commander of the Jerusalem Police Brigade, General Aharon Franco, said that there was no intention to change the status quo on the Temple Mount, saying «it is the right of Jews to pray in the plaza, like the safety of Muslims who performed their religious rites during the holy month of Ramadan on the Temple Mount without interference ».
And carrying the police chief points of the launch of Islamic remarks have led to the tense situation, and said that talk of Al-Aqsa Mosque in danger of not unfounded.
Franco «The police will strike with an iron fist all of a potential breach of public order in the vicinity of the holy places in Jerusalem».
Israeli police were placed, in the northern cities of Israel, where Arabs, alert, fearing that the demonstrations against the storming of Aqsa, said the commander of the North in the police, Shimon Koren, will be the deployment of enhanced police and especially in the areas of Acre, Nazareth and Wadi Ara, warning that «Police will not tolerate any attempt to disrupt public order».
On the eve of Yom Kippur, the last year, the youths attacked in the town of Acre, an Arab man drove his car into the predominantly Jewish neighborhood in contravention of the ritual that day. Triggering clashes during which several houses and shops of major damage.
And assaulted Israeli police, yesterday, the Chairman of the Board of Awqaf, Sheikh Abdul Azim Salhab, as he entered the Al Aqsa Mosque, from the Lions Gate, also prevented both from Sheikh Ikrima Sabri, head of the Supreme Islamic Council, enter the maximum, and Hatem Abdel Qader, Minister of Jerusalem Affairs former from entering the Old City, and hindered the work of medical personnel, and evacuate the injured from inside the mosque.
And raised the maximum storm wave of anger and the official Palestinian people, Hamas said it would not stand idly by, and its armed wing, vowed to respond to storm Al-Aqsa, and demanded by leading the launch of a third intifada.
The Fatah movement, has requested the home fans continue to address the Jewish extremists, and urged the world to shoulder its responsibilities. For its part, Islamic Jihad said that the decision of the aggression against the Al-Aqsa has to do with the decision of the central one is an Israeli escalation in the region.
And carrying Sheikh Mohammed Hussein, the Grand Mufti of Jerusalem and the Palestinian regions, and preacher of Al-Aqsa Mosque, Israeli occupation authorities responsibility for the consequences of intrusions as a sponsor of extremist groups.
He accused the minister of Awqaf and Religious Affairs, Mahmoud al-Habash, the Israeli police to exercise «state terrorism», saying that this behavior is part of an Israeli attempt to undermine international efforts to achieve peace. The militant group staged last night, big rallies in Gaza in solidarity with the maximum. The events and the mind, incursions former Israeli Prime Minister Ariel Sharon to Al Aqsa mosque on September 27 (September) 2000, which raised the sentiments of Arabs and Muslims, and led to the outbreak of the uprising known Aqsa intifada that lasted for several years.
The Israeli authorities imposed yesterday, including a security cordon on the West Bank will continue until Thursday evening, the expiry date of the Jewish fast of Yom Kippur.
During this period did not allow Palestinians to enter Israel only in humanitarian cases and medical special, as close as the King Hussein Bridge to the movement of passengers and trucks, the Palestinians will not be able to travel anywhere, or to return to the West, even today.
Furthermore, the Jordanian government strongly condemned, and expressed their dismay and condemnation of the Israeli occupation forces stormed Al-Aqsa Mosque, the launching of tear gas and rubber bullets against unarmed worshipers.
The spokesman for the Jordanian Government and the Minister of State for Media Affairs and Communications Nabil Sharif, said that Jordan rejects any prejudice by the Israeli occupying forces in Jerusalem and the sanctity of Al Aqsa Mosque, which contained the provisions of international law, norms and conventions and the relevant resolutions, which protects houses of worship and congregations of any violation.
And denounced al-Sharif said in a statement issued yesterday, repeated intrusions by Jewish groups and Israeli forces to the Al-Aqsa Mosque, pointing out that this provocative action that would increase tension, and the potential to lead to further acts of violence that threaten security and stability in the region.
He was surprised at the timing of this break-Sharif and the threat of incitement to violence at a time when the focus of all international efforts towards the resumption of negotiations aimed at finding a solution to the Palestinian-Israeli conflict and the establishment of an independent Palestinian state with its capital in Jerusalem.
Al-Sherif confirmed the role of Jordan and the duty to preserve the holy sites in Jerusalem and protect them from any violation, emphasizing that the government will make all efforts and ways they deem appropriate to ensure protection of these sanctuaries.
Meanwhile, Foreign Ministry summoned the Chargé d'affaires of Jordan on the Israeli in Amman, and requested the transfer of protest and condemn the Government of Jordan on the strong Israeli occupation forces stormed Al-Aqsa Mosque and the dismay and surprise at this blatant attack on the worshipers, and the timing of separation.
Why does Google have two L's today?
Why does Google have two Ls today? If you happen to be also one of the many internet users that are wondering why Google decided to have their logo, or as they call it, “doodle” spelt with 2 l’s, then you’ve come to the right place.
Actually the 2L’s are number 11 as it is Google’s 11th birthday, after being founded in September 1998. It will be celebrated in each time zone when it officially become September 27.
Oddly enough, Google celebrated their birthday last year on September 2nd, but now it seems like they’ve changed their birthday date a bit to September 27th.
Google did not announce officially why they would chose to celebrate their birthday on a different day.
Actually the 2L’s are number 11 as it is Google’s 11th birthday, after being founded in September 1998. It will be celebrated in each time zone when it officially become September 27.
Oddly enough, Google celebrated their birthday last year on September 2nd, but now it seems like they’ve changed their birthday date a bit to September 27th.
Google did not announce officially why they would chose to celebrate their birthday on a different day.
Wednesday, August 26, 2009
Yahoo! Acquires Maktoob The Largest Arab Portal!
Yesterday Yahoo! announced to Acquires Maktoob.
Maktoob.com is a leading Web community portal offering online communications tools and information services that empower Arab Internet users. With a diverse audience covering the whole Arabicspeaking region in the Middle East and North Africa (MENA), Maktoob combines a wide array of quality aggregated content with the latest Web 2.0 services to engage communities of users throughout one of the fastest growing emerging new media markets in the world.
Since its founding in 2000, when it first introduced free Arabic/English webmail, Maktoob has been a leading provider of communications and community tools such as chat, discussion forums and e-cards, in addition to a host of content-focused information channels covering the latest breaking news, business and finance, sports, entertainment, women’s-related topics, and much more. Today, Maktoob also offers the very latest in Arabic-language Web 2.0 services, including photo and video sharing, blogging, and social networking.
Maktoob have certificate from ABCe.
But what about maktoob current employee?!
http://business.maktoob.com/yahoomaktoob/
http://maktoob.yahoo.com
http://maktoob.com
Maktoob.com is a leading Web community portal offering online communications tools and information services that empower Arab Internet users. With a diverse audience covering the whole Arabicspeaking region in the Middle East and North Africa (MENA), Maktoob combines a wide array of quality aggregated content with the latest Web 2.0 services to engage communities of users throughout one of the fastest growing emerging new media markets in the world.
Since its founding in 2000, when it first introduced free Arabic/English webmail, Maktoob has been a leading provider of communications and community tools such as chat, discussion forums and e-cards, in addition to a host of content-focused information channels covering the latest breaking news, business and finance, sports, entertainment, women’s-related topics, and much more. Today, Maktoob also offers the very latest in Arabic-language Web 2.0 services, including photo and video sharing, blogging, and social networking.
Maktoob have certificate from ABCe.
Official press release from Maktoob Business:
“RELEASE: Yahoo! Maktoob acquisition
Aug 25, 2009
Yahoo! to Extend Reach to Millions of Consumers in the Arab World; Signs Definitive Agreement to Acquire Maktoob.com
Combination of local expertise and global scale to provide best online experience for consumers in the Arab world and platform for advertisers in the region
SUNNYVALE, Calif. & DUBAI, Aug 25, 2009 — Yahoo! Inc. (Nasdaq:YHOO) today announced it has entered into a definitive agreement to acquire Maktoob.com, the leading online community in the Arab world, with more than 16.5 million unique users.
“This acquisition will accelerate Yahoo!’s strategy of expanding in high-growth emerging markets where we believe Yahoo! has unparalleled opportunity to become the destination of choice for consumers,” said Yahoo! chief executive officer Carol Bartz. “Access to information and communications tools can positively impact people’s lives in many ways, and with the acquisition of Maktoob.com and our investment in the region, the Arab world will soon get a Yahoo! experience in Arabic with relevant local language content, programming and services.”
Internet users in the region will benefit from the combination of Yahoo!’s popular products and services with Maktoob’s compelling local content, which today reaches one in three people online throughout the Arab world. This acquisition will extend Yahoo!’s current offerings by adding capabilities to deliver relevant Arabic-language content and services, as well as Arabic versions of Yahoo!’s popular Yahoo! Messenger and Yahoo! Mail services. Maktoob.com is accessed by users in countries that include UAE, Jordan, Kuwait, Egypt and Saudi Arabia.
“Yahoo! and Maktoob are natural partners and this combination should help energize the Internet market in the region as a whole. We are excited about Yahoo! building a stronger presence in the Middle East and bringing its compelling suite of services to Arab users in Arabic,” said Samih Toukan, Founder of Maktoob.
While Internet usage in the Middle East has grown more than tenfold since 2000, most markets are still in the early stages of adoption. According to the World Bank, there are more than 320 million Arabic speakers worldwide, while less than one per cent of all online content is in Arabic.
With Yahoo! and Maktoob.com’s combined audience and platform, advertisers will have access to the reach and sophisticated targeting capabilities they need to effectively engage with the region’s online consumers. Spending on online advertising is expected to grow by 35 – 40 percent this year in the region, according to Madar Research.
“Internet users in the Arab world will have access to Yahoo!’s vast content portfolio, as well as world-class communications products, which will be available in Arabic for the first time. In addition, advertisers will be able to leverage the vast reach of the newly combined audiences to effectively market to consumers across the region,” said Ahmed Nassef, general manager of Maktoob.com.
Maktoob.com was founded in 2000 by Samih Toukan and Hussam Khoury as the world’s first free Arabic/English Web-based email service, and since then has grown to be the leading Arab online community in the region.
“Yahoo is acquiring Maktoob.com for the strong brand and audience it has built over the last nine years and the passionate team they have assembled, which we believe is the strongest in the region,” said Keith Nilsson, senior vice president, Emerging Markets, Yahoo! “We see great growth potential in both audience and advertising in the Arab world and combining with Maktoob.com will allow us to quickly build our presence there with high quality products. This is a big win for publishers, advertisers, and consumers in the region.”
This acquisition is part of Yahoo!’s larger strategy to grow its business throughout the world’s emerging markets by connecting consumers with the content and services that matter most to them in their local language. The company’s Emerging Markets business group, headquartered in Singapore, is responsible for Yahoo!’s fastest growing markets such as South East Asia, India, Latin America, Africa, and the Middle East. Yahoo! has a strong track record of delivering great Internet experiences and helping fuel Internet adoption through partnerships with local developers and content providers.
Following the acquisition, Maktoob.com will become a wholly-owned subsidiary of Yahoo!. Ahmed Nassef, the current general manager of Maktoob.com, will continue to lead the Maktoob.com teams and will report to Keith Nilsson. It is expected that the transaction will be completed in the fourth quarter of 2009.
Upon completion of the deal, the remaining Maktoob Group companies – including Souq.com, cashU.com, Araby.com, and Tahadi.com – will operate under a new entity called the Jabbar Internet Group, managed by Samih Toukan. Yahoo! and the Jabbar Internet Group will continue to have a strong commercial relationship going forward, which will include the promotion of Jabbar companies on the Maktoob.com portal.
Financial terms were not disclosed.”
But what about maktoob current employee?!
http://business.maktoob.com/yahoomaktoob/
http://maktoob.yahoo.com
http://maktoob.com
Thursday, July 30, 2009
Find Query Per certain Seconds
Do you need to find qps for peak hours not avg qps through mysql life.
The MySQL 5.1 offers new GLOBAL_STATUS information schema tables. These can be used to report certain performance metrics, such as the number of queries processed per certain seconds, NOT overall avg queries per second, Its good to know how much qps in peak hours.
http://forge.mysql.com/tools/tool.php?id=217
The MySQL 5.1 offers new GLOBAL_STATUS information schema tables. These can be used to report certain performance metrics, such as the number of queries processed per certain seconds, NOT overall avg queries per second, Its good to know how much qps in peak hours.
http://forge.mysql.com/tools/tool.php?id=217
Thursday, July 09, 2009
Threads with "freeing items", "Sending data" and "Locked" never finish
In one of the servers we have an issue that happens to one of the servers that some items
that have the status of "freeing items" and "Sending data" are just stuck there, causing a
lot of locks on the server, and the load of the server drops to almost 0.
The server then wouldn't restart, and the only solution is to kill the mysqld process, and
fix the crashed tables that result from the kill.
How to repeat:
There is no specific knowledge of when does this happens or why, but it happens like once
every 3 days.
Solution:
turn query cache off.
to follow up see: http://bugs.mysql.com/bug.php?id=45544
that have the status of "freeing items" and "Sending data" are just stuck there, causing a
lot of locks on the server, and the load of the server drops to almost 0.
The server then wouldn't restart, and the only solution is to kill the mysqld process, and
fix the crashed tables that result from the kill.
How to repeat:
There is no specific knowledge of when does this happens or why, but it happens like once
every 3 days.
Solution:
turn query cache off.
to follow up see: http://bugs.mysql.com/bug.php?id=45544
Tuesday, June 16, 2009
InnoDB tablespace, single Vs. multiple, and InnoDB defragment
The ibdata file is too big 10GB, and actually we've only about 2GB (data+index) in innodb storage engine.
How we can defragment this file and reduce it?
How is this happened?
By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (innodb_file_per_table=OFF) single tablespace.
In this configuration the file will too big, especially when u need to test something in innodb engines and create a lot of innodb tables.
another problem if you truncate or delete all or some data the ibdata file will not decrease size, and when optimize the table the data part of this table will optimize but still there are gaps between tables in tablespace.
In my case:
ibdata file in 10GB and the total sum of (data_length+index_length) of all innodb tables not exceed 2GB!!!!!!!!!!
OS and I/O manipulate with 10GB file but we need only 2GB in worst case!!!!!
If we set innodb_file_per_table=ON then alter all innodb tables, mysql will generate ibdata for each table with its size, its good!!!, but the shared tablespace still exist (10GB) and cannot delete it (it’s necessary even innodb_file_per_table enable or disable) BAD!!!!!!
The Solution:
1- Get all innodb tables in your database:
Select table_name, table_schema from information_schema.tables where engine = 'innodb';
Convert all to myisam by using: alter table table_name engine myisam;
2- Then shutdown mysql.
Till now ibdata file same size 10GB
3- Rename or move to another directory ibdata and ib_log files.
Check this global variables innodb_data_home_dir and innodb_log_group_home_dir to know where are there.
4- Add innodb_file_per_table to cnf file.
5- Then start mysql
mysql will create ibdata file with default size 10MB, and ib_logfile0 and ib_logfile1 (logs).
6- Return back tables to innodb by using: alter table table_name engine innodb;
mysql will create file for each table in database directory.
See this scenario:
My.cnf
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_file_per_table
note:
this [user@server~]$ in shell command
and this mysql> in mysql command
in sequence order
mysql> create table musers_test like musers;
Query OK, 0 rows affected (0.02 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:28 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (1 min 12.43 sec)
Records: 500000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:31 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 26.64 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:34 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (2 min 58.44 sec)
Records: 500000 Duplicates: 0 Warnings: 0
/* notice the execution time is double previous insert (from 1 min 12.43 sec to 2 min 58.44 sec) */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 416M Jun 16 06:38 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test;
Query OK, 0 rows affected (0.00 sec)
/* decrease file size because innodb_file_per_table=ON */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:43 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 2.82 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:47 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test engine innodb;
Query OK, 0 rows affected (5.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:48 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 5000;
Query OK, 5000 rows affected, 1 warning (1.18 sec)
Records: 5000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test limit 4000;
Query OK, 4000 rows affected, 1 warning (0.52 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test engine innodb;
Query OK, 1000 rows affected (0.37 sec)
Records: 1000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 9.0M Jun 16 06:50 /var/lib/mysql/test_db/musers_test.ibd
also you can run shel command from mysql as mysql> \! ls -lh /var/lib/mysql/test_db/musers_test.ibd
Regards,
Mohammad Lahlouh
:)
How we can defragment this file and reduce it?
How is this happened?
By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (innodb_file_per_table=OFF) single tablespace.
In this configuration the file will too big, especially when u need to test something in innodb engines and create a lot of innodb tables.
another problem if you truncate or delete all or some data the ibdata file will not decrease size, and when optimize the table the data part of this table will optimize but still there are gaps between tables in tablespace.
In my case:
ibdata file in 10GB and the total sum of (data_length+index_length) of all innodb tables not exceed 2GB!!!!!!!!!!
OS and I/O manipulate with 10GB file but we need only 2GB in worst case!!!!!
If we set innodb_file_per_table=ON then alter all innodb tables, mysql will generate ibdata for each table with its size, its good!!!, but the shared tablespace still exist (10GB) and cannot delete it (it’s necessary even innodb_file_per_table enable or disable) BAD!!!!!!
The Solution:
1- Get all innodb tables in your database:
Select table_name, table_schema from information_schema.tables where engine = 'innodb';
Convert all to myisam by using: alter table table_name engine myisam;
2- Then shutdown mysql.
Till now ibdata file same size 10GB
3- Rename or move to another directory ibdata and ib_log files.
Check this global variables innodb_data_home_dir and innodb_log_group_home_dir to know where are there.
4- Add innodb_file_per_table to cnf file.
5- Then start mysql
mysql will create ibdata file with default size 10MB, and ib_logfile0 and ib_logfile1 (logs).
6- Return back tables to innodb by using: alter table table_name engine innodb;
mysql will create file for each table in database directory.
See this scenario:
My.cnf
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_file_per_table
note:
this [user@server~]$ in shell command
and this mysql> in mysql command
in sequence order
mysql> create table musers_test like musers;
Query OK, 0 rows affected (0.02 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:28 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (1 min 12.43 sec)
Records: 500000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:31 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 26.64 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:34 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (2 min 58.44 sec)
Records: 500000 Duplicates: 0 Warnings: 0
/* notice the execution time is double previous insert (from 1 min 12.43 sec to 2 min 58.44 sec) */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 416M Jun 16 06:38 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test;
Query OK, 0 rows affected (0.00 sec)
/* decrease file size because innodb_file_per_table=ON */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:43 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 2.82 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:47 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test engine innodb;
Query OK, 0 rows affected (5.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:48 /var/lib/mysql/test_db/musers_test.ibd
mysql> insert into musers_test select * from musers limit 5000;
Query OK, 5000 rows affected, 1 warning (1.18 sec)
Records: 5000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd
mysql> delete from musers_test limit 4000;
Query OK, 4000 rows affected, 1 warning (0.52 sec)
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd
mysql> alter table musers_test engine innodb;
Query OK, 1000 rows affected (0.37 sec)
Records: 1000 Duplicates: 0 Warnings: 0
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 9.0M Jun 16 06:50 /var/lib/mysql/test_db/musers_test.ibd
also you can run shel command from mysql as mysql> \! ls -lh /var/lib/mysql/test_db/musers_test.ibd
Regards,
Mohammad Lahlouh
:)
Thursday, June 04, 2009
vBulletin, session table is InnoDB
In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.
So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)
innodb_data_home_dir = /dev/shm/mysql/ #this path in memory partition
innodb_log_group_home_dir = /dev/shm/mysql/
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
innodb_thread_concurrency = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0
thanks
any comment?
UPDATE:
In this configuration when restart MySQL in error log will see:
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
So may be a bug with tmpfs and O_DIRECT!!!
see http://bugs.mysql.com/45671
So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)
innodb_data_home_dir = /dev/shm/mysql/ #this path in memory partition
innodb_log_group_home_dir = /dev/shm/mysql/
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
innodb_thread_concurrency = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0
thanks
any comment?
UPDATE:
In this configuration when restart MySQL in error log will see:
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
So may be a bug with tmpfs and O_DIRECT!!!
see http://bugs.mysql.com/45671
Monday, April 20, 2009
Oracle to Buy Sun !!!
Sun and Oracle today announced a definitive agreement for Oracle to acquire Sun for $9.50 per share in cash. The Sun Board of Directors has unanimously approved the transaction. It is anticipated to close this summer.
What will happened in MySQL, JAVA.
I think its enough.
http://www.sun.com/aboutsun/pr/2009-04/sunflash.20090420.1.xml
What will happened in MySQL, JAVA.
I think its enough.
http://www.sun.com/aboutsun/pr/2009-04/sunflash.20090420.1.xml
Wednesday, April 15, 2009
Warning Aborted connection, log_warnings and wait_timeout
In my server error log i see
090415 10:55:57 [Warning] Aborted connection 481 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 10:56:16 [Warning] Aborted connection 582 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 11:05:13 [Warning] Aborted connection 2693 to db: 'db' user: 'root' host: 'localhost' (Got timeout reading communication packets)
every thread connected to mysql and sleep more than wait_timeout mysql will close it.
the previous warning depend on log_warnings level, by default log_warnings = 1, if its =2 all the closed connection will written in mysql error log.
I think we don't need this warning.;)
090415 10:55:57 [Warning] Aborted connection 481 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 10:56:16 [Warning] Aborted connection 582 to db: 'db' user: 'user' host: 'localhost' (Got timeout reading communication packets)
090415 11:05:13 [Warning] Aborted connection 2693 to db: 'db' user: 'root' host: 'localhost' (Got timeout reading communication packets)
every thread connected to mysql and sleep more than wait_timeout mysql will close it.
the previous warning depend on log_warnings level, by default log_warnings = 1, if its =2 all the closed connection will written in mysql error log.
I think we don't need this warning.;)
--log-warnings[=level], -W [level]
Option Sets Variable Yes, log-warnings
Variable Name log_warnings
Variable Scope Both
Dynamic Variable Yes
Disabled by skip-log-warnings
Value Set Type numeric
Default 1
Print out warnings such as Aborted connection... to the error log. Enabling this option is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). This option is enabled (1) by default, and the default level value if omitted is 1. To disable this option, use --log-warnings=0. If the value is greater than 1, aborted connections are written to the error log. See Section B.1.2.11, “Communication Errors and Aborted Connections”.
If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.
wait_timeout
The number of seconds the server waits for activity on a non-interactive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
Sunday, April 12, 2009
mysqlsla amazing tool
mysqlsla is interesting tool to analyze slow log query, aggregate same query in one and generate unique sql statement withCount, (max, min, avg) execute time, lock time, Rows sent, Rows examined for each unique one.
you can use it to review indexes and drop unused index, and create another.
Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M
____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users
Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;
Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;
_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users
Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;
Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;
_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users
Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;
Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;
http://hackmysql.com/mysqlsla
you can use it to review indexes and drop unused index, and create another.
Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M
____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users
Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;
Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;
_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users
Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;
Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;
_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users
Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;
Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;
http://hackmysql.com/mysqlsla
Wednesday, April 08, 2009
A Brief Introduction to MySQL Performance Tuning
Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.
Server Variables
For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.
For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.
Other variables that are worth investigating for performance tuning are:
query_cache_size - This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.
innodb_log_file_size - Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.
sort_buffer_size - Another one that shouldn’t be set too large. Peter Zaitsev did some testing a while back showing that increasing sort_buffer_size can in fact reduce the speed of the query.
Server Hardware
There are a few solid recommendations for improving the performance of MySQL by upgrading your hardware:
- Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM. At this point in time, InnoDB does have issues scaling past 8 cores, so you don’t need to go out of your way to have lots of processors.
- Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.
- If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can seperate your data files, OS files and log files onto different physical disks.
Query Tuning
Finally, though probably the most important, we look at tuning queries. In particular, we make sure that they’re using indexes, and they’re running quickly. To do so, turn on the Slow Query Log for a day, with log_queries_not_using_indexes enabled as well. Run the resulting log through mysqldumpslow, which will produce a summary of the log. This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.
Upgrading MySQL with minimal downtime through Replication
Problem
With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:
- Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
- mysql_upgrade: A much faster method, but it can still be slow for very large data sets.
I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.
Preparation
First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:
- If you haven’t already, enable Binary Logging on S1. We will need it to act as a replication Master.
- Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
- If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
- If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
- For other backup methods, you will probably need to get the Binary Log position manually:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
(Perform backup now...)
mysql> UNLOCK TABLES;
Once you have a backup with the corresponding Binary Log position, you can setup S2:
- Install MySQL 5.1 on S2.
- Restore the backup from S1 to S2.
- Create the Slave user on S1.
- Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
- Enable Binary Logging on S2. We’ll need this during the upgrade process.
- Setup S2 as a Slave of S1:
- If you used mysqldump for the backup, you will need to run the following query:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';
- For any other method, you’ll need to specify the Binary Log position as well:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;
- If you used mysqldump for the backup, you will need to run the following query:
- Start the Slave on S2:
mysql> START SLAVE;
The major pre-upgrade work is now complete.
Upgrade
Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.
mysql> STOP SLAVE;
mysql> SHOW MASTER STATUS;
Once the backup is complete, restart S2 and let it catch up with S1 again.
When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.
- Stop the Slave process on S2:
mysql> STOP SLAVE; - Stop S1.
- Upgrade S1 to MySQL 5.1.
- Move the S1 data files to a backup location.
- Move the backup from S2 into S1’s data directory.
- Start S2.
- Setup S2 as a Slave to S1, same as when we made S1 a Slave of S2.
- Let S2 catch up with S1. When it has caught up, stop your application, and make sure S2 is still caught up with S1.
- Switch your application back to using S1.
Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='';
Conclusion
You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.
Subscribe to:
Posts (Atom)