MySQL server performance problems: high CPU use / slow website

MySQL & MariaDB Other MySQL® Questions
1

We’re experiencing problems with the performance of our website/MySQL server. There are times when MySQL Server uses a lot of CPU, and the site is very slow, or will not load at all.
Usually this seems to resolve itself after a while, but sometimes it takes a long time, up until a couple of hours. Sometimes it helps to restart MySQL Server, sometimes not.
It seems like MySQL Server ‘clogs up’ over time, until eventually it needs to be restarted to properly function again.

We are running a news site with about 20,000 pageviews a day. Both MySQL and the IIS are running on the same machine. Sometimes we also use the server for sending out our newsletter (about 9,000 subscribers)

Server specifications:
Intel Xeon 2.8 GHz processor
2 GB RAM
Windows 2003
IIS 6.0
MySQL 5.0.26
ASP 3.0

The content of the site consists mostly of news articles, banners and photo reports.
The queries performed mostly are SELECT queries, but we also use tracking software (MetaTraffic), which writes at least one (and sometimes several) entries in the database for every pageview. This is probably a heavy load for the server, but I have done some research and no other users seem to be having performance problems because of MetaTraffic, on the contrary, it is complimented for it’s efficiency.

For most tables we use the InnoDB storage engine, except for MetaTraffic and the photoalbum tables I use MyISAM, because the software created these tables in MyISAM by default (even though InnoDB is the default storage engine on my server).

So far I’ve tried my best to optimize the server variables, the queries and the ASP code.
Here are some of the server vars:

max_allowed_packet = 1M
sort_buffer_size = 1M
max_connections=200
query_cache_size = 2M
query_cache_limit = 1M
query-cache-type = 2
table_cache = 80
thread_cache_size=8
key_buffer_size = 100M
read_buffer_size=1M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=64M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=700M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=700M
innodb_log_buffer_size=8M
innodb_thread_concurrency=0

Those are the most relevant when it comes to performance I think, or am I forgetting something?

So any help on this would be greatly appreciated. I could probably optimize the queries / code a bit more but I doubt that will be a huge improvement. I was also told that the problem may be on OS level: MySQL Server and IIS might get in the way of each other, so one of them would start using virtual memory. I would have to check where in the OS the I/O takes place, and this would show the bottlenecks.
But I don’t know all that much about IIS and Windows Servers, so I have no idea to resolve that. Anyone who can confirm this is probably the problem and can point me in the right direction of resolving it?
Or could there be other causes?

Tomorrow I’ll post the status vars during peak time.

2

What you are describing sounds just like you have a query in you application that performs a cross join due to forgotten join condition.
Or that you have a query that is used often that performs a heavy table scan.

How large in MB is actually your database?
Very important if we are going to be able to answer you any questions.

Why have you set the innodb_thread_concurrency to 0?
I recommend that you leave it as default (which is 8 ) unless you really really know what you are doing.

Run SHOW PROCESSLIST in mysql when your server bogs down to find out what mysql is doing at that moment.
That way you have a chance to pinpoint which queries that take a lot of CPU.

3

I’ve already tried to optimize most of the queries, but like I said, they could probably be optimized a bit more. I don’t really think they are ‘heavy’, but who knows. I put them at the end of the post, maybe you can find some flaws…
There are definitely some ‘heavy’ queries when you view the stats in MetaTraffic, but we don’t view those too often (maybe a couple of times a day at the most)

  1. InnoDB tablespace = 133 mb
    Database folder (with MyISAM tables) = 113 mb
    The backup file of the database (mysqldump) is 235 mb.
    Most of the data is in the MetaTraffic tables.

  2. I was trying to find ways to optimize the server, and I read an article on this website that said that if you have 1-2 CPU’s, you can often do well with innodb_thread_concurrency set to 0.
    Should I set it back to default?

  3. Thanks, I’ll try that.

Here are the ‘big’ queries that are loaded every time someone views the index page. The date constant that is in some of the queries is an ASP variable for the current date. And sorry, most of the field and table names are in Dutch…

Get the top news articles:
SELECT SQL_CACHE n.nieuwsbericht_id, DATE(n.datum_tijd_public) AS datum_public, DATE(n.datum_tijd_public) AS datum, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id, d.datum
FROM nieuwsbericht n, categorie c, nieuwsbericht_has_sector nhs, datum_index d
WHERE DATE(n.datum_tijd_public) = d.datum
AND n.gepubliceerd = 1
AND n.hoofd_positie <> ‘NULL’
AND n.nieuwsbericht_id = nhs.nieuwsbericht_id
AND nhs.gerelateerd = 1
AND n.categorie_id = c.categorie_id
ORDER BY n.hoofd_positie

Get any job advertisements:
SELECT n.nieuwsbericht_id, n.datum_tijd_public, n.titel, n.ondertitel, n.bericht AS bericht, n.bericht_afkappen, n.link, c.omschrijving AS categorie, d.datum
FROM nieuwsbericht n, categorie c, datum_index d
WHERE DATE(n.datum_tijd_public) = DATE(d.datum)
AND c.omschrijving = ‘vacature’
AND n.gepubliceerd = 1
AND n.categorie_id = c.categorie_id

Get banners that are displayed between the top news articles
SELECT SQL_CACHE b.banner_id, b.naam, b.locatie, b.expanding, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners
FROM pagina p, pagina_has_banner phb, banner b
WHERE p.url = ‘index.asp’
AND p.pagina_id = phb.pagina_id
AND ‘2007-4-12’ >= phb.datum_start
AND ‘2007-4-12’ < phb.datum_eind
AND phb.banner_id = b.banner_id
ORDER BY phb.positie
LIMIT 5

Get banners that are randomly displayed in the header (perhaps it would be better to do the randomizing in the code instead of the query):
SELECT b.banner_id, b.naam, b.expanding, b.locatie, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners, phb.banner_id*0+RAND() as rnd_id
FROM pagina p, pagina_has_banner phb, banner b
WHERE p.url = ‘topbanners’
AND p.pagina_id = phb.pagina_id
AND CURDATE() >= phb.datum_start
AND CURDATE() < phb.datum_eind
AND phb.banner_id = b.banner_id
ORDER BY rnd_id
LIMIT 4

Get banners that are displayed on the side:
SELECT n.nieuwsbericht_id, n.titel
FROM nieuwsbericht n, nieuwsbericht_has_sector nhs
WHERE n.datum_tijd_public > ‘2007-4-11 00:00:00’
AND n.datum_tijd_public < ‘2007-4-12 00:00:00’
AND n.nieuwsbericht_id = nhs.nieuwsbericht_id
AND nhs.sector_id <> 27
AND nhs.gerelateerd = 1
AND n.gepubliceerd = 1
ORDER BY aantal_clicks DESC
LIMIT 5

Get the yesterday’s top 5 most read news articles (this is also done for the past week and month)
SELECT n.nieuwsbericht_id, n.titel
FROM nieuwsbericht n, nieuwsbericht_has_sector nhs
WHERE n.datum_tijd_public > ‘2007-4-11 00:00:00’
AND n.datum_tijd_public < ‘2007-4-12 00:00:00’
AND n.nieuwsbericht_id = nhs.nieuwsbericht_id
AND nhs.sector_id <> 27
AND nhs.gerelateerd = 1
AND n.gepubliceerd = 1
ORDER BY aantal_clicks
DESC LIMIT 5

The following two queries are repeated for every 27 news sectors, I know this is not the best solution but so far I haven’t figured out a way to do it all in one query.

Get all the news aricles that were published in a particular news sector today:
SELECT SQL_CACHE n.nieuwsbericht_id, n.datum_tijd_public, DATE(n.datum_tijd_public) AS datum, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id
FROM nieuwsbericht n, categorie c, nieuwsbericht_has_sector nhs, sector s, datum_index d
WHERE s.url = ‘bio’
AND s.sector_id = nhs.sector_id
AND nhs.gerelateerd = 1
AND nhs.nieuwsbericht_id = n.nieuwsbericht_id
AND DATE(n.datum_tijd_public) = DATE(d.datum)
AND n.gepubliceerd = 1
AND n.hoofd_positie IS NULL
AND n.categorie_id = c.categorie_id
ORDER BY n.datum_tijd_public DESC

If there wasn’t any news published in this sector today, then display the latest news article:
SELECT SQL_CACHE n.nieuwsbericht_id, n.datum_tijd_public, DATE(n.datum_tijd_public) AS datum, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id
FROM nieuwsbericht n, categorie c, nieuwsbericht_has_sector nhs, sector s
WHERE s.url = ‘koeling’
AND s.sector_id = nhs.sector_id
AND nhs.gerelateerd = 1
AND nhs.nieuwsbericht_id = n.nieuwsbericht_id
AND n.gepubliceerd = 1
AND n.categorie_id = c.categorie_id
ORDER BY n.datum_tijd_public DESC
LIMIT 10

Get the banners that are to be randomly displayed with the sectors.
SELECT b.banner_id, b.naam, b.locatie, b.expanding, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners, phb.banner_id*0+RAND() as rnd_id
FROM pagina p, pagina_has_banner phb, banner b
WHERE p.url = ‘index.asp?sector’
AND p.pagina_id = phb.pagina_id
AND CURDATE() >= phb.datum_start
AND CURDATE() < phb.datum_eind
AND phb.banner_id = b.banner_id
ORDER BY rnd_id

4

Very good post to see your queries.

Well you definately don’t have any problems with IO beeing a bottleneck since your total DB size is much smaller than the RAM that you have given InnoDB buffer.

Here are some quick pointers:
1.
Do you have indexes on all the columns that part of the join conditions?

Join conditions like this is very bad:

DATE(n.datum_tijd_public) = DATE(d.datum)

since the function DATE has to be performed on each row of each table before a comparison can be made.
Thu voiding any usage of index = high cpu usage.

Avoid conditions like this:

someColumn <> NULL

Write them like:

someColumn >= 0

or something like that.
The difference is that if you decide to put an index on that column mysql can’t use it in the first case because you are asking for the rows that don’t match.
While if you say larger than 0 you are asking for rows that match something.
It’s a very important difference.

Which of your tables are large?
Amount of rows/MB?

Try increasing sort_buffer_size a bit.
I don’t think it will do that much good because I think that your main problem is in the joins. But the 1MB seems awfully low.
Try to calculate how large the result sets are before the limit in nr of MB for the different queries and set it to one of the larger ones.

Although that might not be feasible right now.
I suggest that you write your joins using the INNER JOIN syntax instead.
It makes i so much easier to read and reduces the chance that you forget a join expression immensely.

Your query that is repeated 27 times is usually solved by that you instead of having a condition on category you select all categories and then sort it like this:

ORDER BY n.categoryID, n.datum_tijd_public

Then you loop thru the output of this query looking for when the change in categoryID is performed and then you know that you have entered a new category.

Run the queries by themselfs in the mysql frontend and check the execution times to try to find the slow one.

When you found one or more of the queries that seem to take a lot of time use EXPLAIN on it and post

  • the query
  • table design (including indexes, usually easiest SHOW CREAT TABLE yourTable)
  • output from EXPLAIN
    here on the forum and we can help you to tweak it.
5

Thanks a lot for your help. I’ve started working on the queries, but it takes quite a lot of work, because I have to modify the database design a bit (to avoid the DATE(n.datum_tijd_public) = DATE(d.datum) join condition).
I will look at the other things you mentioned after that.

To answer your questions:

  1. Yes I do, all those tables are InnoDB so the indexes were automatically generated.

  2. The only somewhat large tables accessed in these queries are ‘nieuwsbericht’, which holds the news articles, and ‘nieuwsbericht_has_sector’, the relation table for nieuwsbericht and sector. ‘nieuwsbericht’ has 12,000 rows (dump is 25MB), with about 45 added every day, and ‘nieuwsbericht_has_sector’ has 7,500 (dump is 84K).

Btw, here are the status vars during peak time:

±----------------------------------±-----------+
| Variable_name | Value |
±----------------------------------±-----------+
| Aborted_clients | 370 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 12013 |
| Bytes_received | 138832978 |
| Bytes_sent | 1373214038 |
| Com_admin_commands | 51 |
| Com_alter_db | 0 |
| Com_alter_table | 3 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 1 |
| Com_change_db | 38961 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 1 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 121 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 2 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 26447 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 315647 |
| Com_set_option | 193 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 1 |
| Com_show_collations | 36 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 118 |
| Com_show_databases | 7 |
| Com_show_errors | 0 |
| Com_show_fields | 696 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 16 |
| Com_show_logs | 0 |
| Com_show_master_status | 1 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 2 |
| Com_show_slave_hosts | 1 |
| Com_show_slave_status | 0 |
| Com_show_status | 14 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 16 |
| Com_show_triggers | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 1 |
| Com_update | 24399 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1127 |
| Created_tmp_disk_tables | 80965 |
| Created_tmp_files | 15 |
| Created_tmp_tables | 163008 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 3 |
| Handler_commit | 27540 |
| Handler_delete | 2 |
| Handler_discover | 0 |
| Handler_prepare | 24026 |
| Handler_read_first | 117780 |
| Handler_read_key | 177936010 |
| Handler_read_next | 304230566 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1181562 |
| Handler_read_rnd_next | 1612636300 |
| Handler_rollback | 7030 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 30916 |
| Handler_write | 21733089 |
| Innodb_buffer_pool_pages_data | 4059 |
| Innodb_buffer_pool_pages_dirty | 4 |
| Innodb_buffer_pool_pages_flushed | 27242 |
| Innodb_buffer_pool_pages_free | 40706 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 35 |
| Innodb_buffer_pool_pages_total | 44800 |
| Innodb_buffer_pool_read_ahead_rnd | 7 |
| Innodb_buffer_pool_read_ahead_seq | 2 |
| Innodb_buffer_pool_read_requests | 1105568396 |
| Innodb_buffer_pool_reads | 2336 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 201156 |
| Innodb_data_fsyncs | 24979 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 48254976 |
| Innodb_data_reads | 2822 |
| Innodb_data_writes | 74460 |
| Innodb_data_written | 946127872 |
| Innodb_dblwr_pages_written | 27242 |
| Innodb_dblwr_writes | 4597 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 79929 |
| Innodb_log_writes | 38354 |
| Innodb_os_log_fsyncs | 15382 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 51282944 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 1247 |
| Innodb_pages_read | 2812 |
| Innodb_pages_written | 27242 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 140 |
| Innodb_rows_inserted | 10795 |
| Innodb_rows_read | 506675301 |
| Innodb_rows_updated | 14992 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 89838 |
| Key_blocks_used | 208 |
| Key_read_requests | 1318079 |
| Key_reads | 5718 |
| Key_write_requests | 185586 |
| Key_writes | 9428 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 59 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 68 |
| Open_streams | 0 |
| Open_tables | 72 |
| Opened_tables | 1929 |
| Qcache_free_blocks | 20 |
| Qcache_free_memory | 2011200 |
| Qcache_hits | 46894 |
| Qcache_inserts | 75541 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 240970 |
| Qcache_queries_in_cache | 56 |
| Qcache_total_blocks | 138 |
| Questions | 454606 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 6 |
| Select_range | 34146 |
| Select_range_check | 0 |
| Select_scan | 117121 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 12 |
| Sort_merge_passes | 5 |
| Sort_range | 3134 |
| Sort_rows | 5195843 |
| Sort_scan | 200627 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 878923 |
| Table_locks_waited | 8 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 4 |
| Threads_connected | 11 |
| Threads_created | 157 |
| Threads_running | 2 |
| Uptime | 75383 |
±----------------------------------±-----------+

6

I see that I could probably increase table_cache, but it’s the first time I’ve seen Opened_tables this high, it’s usually at 0.

7
[B]edsuit wrote on Thu, 12 April 2007 00:36[/B]
2. I was trying to find ways to optimize the server, and I read [URL="http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency/#more-41"]an article [/URL] on this website that said that if you have 1-2 CPU's, you can often do well with innodb_thread_concurrency set to 0. Should I set it back to default?
The InnoDB thread concurrency issues described in the related bug report were not resolved until v5.0.37. You say you are running v5.0.20. Suggest you upgrade as there are also many other issues which have been fixed.
8
[B]Quote:[/B]

| Created_tmp_disk_tables | 80965 |
| Created_tmp_tables | 163008 |

From the figures above we can see that about 1/2 of your temporary tables are written to disk. That is a very high figure.

Have you increased the sort_buffer_size yet?

And if you haven’t managed to rewrite your queries with the DATE() = DATE() join condition yet.
You should increase the join_buffer_size so to avoid a disk based table for that join.

9

Depends on how much memory you got available on the server.

But if you take one of your queries that you think that returns a lot of rows, run it as a SELECT COUNT(*) and without the LIMIT and ORDER BY clause.
You will know how many rows that are actually sorted.

Then you can make a rough estimate on how many bytes each row is and multiply with the amount of rows and you will get an indication of how large it should be.

But I would suggest setting the sort_buffer_size to at least about 5MB.

10

Well I’ve made some adjustments. The website seems to be faster during peak time, but I guess only time will tell if it can handle greater loads now.

What I’ve done:

  • updated to 5.0.37
  • setting the sort_buffer_size to 5MB
  • rewritten the queries

I’ve managed to write one query to get the news articles for all the 23 different sectors, so that should be a pretty big win.
I haven’t gotten around to checking all the queries’ execution times yet, will do that later.
Any other/more suggestions are welcome of course.

Here are the queries:

Get the top news articles:
SELECT SQL_CACHE n.nieuwsbericht_id, n.datum_public, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id, d.datum
FROM nieuwsbericht n
INNER JOIN nieuwsbericht_has_sector nhs ON (n.nieuwsbericht_id = nhs.nieuwsbericht_id)
INNER JOIN categorie c ON (n.categorie_id = c.categorie_id)
INNER JOIN datum_index d ON (n.datum_public = d.datum)
WHERE n.gepubliceerd = 1
AND n.hoofd_positie >= 0
AND nhs.gerelateerd = 1
ORDER BY n.hoofd_positie

Get any job advertisements:
SELECT SQL_CACHE n.nieuwsbericht_id, n.datum_public, n.titel, n.ondertitel, n.bericht AS bericht, n.bericht_afkappen, n.link, c.omschrijving AS categorie, d.datum
FROM nieuwsbericht n
INNER JOIN categorie c ON (n.categorie_id = c.categorie_id)
INNER JOIN datum_index d ON (n.datum_public = d.datum)
WHERE c.omschrijving = ‘vacature’
AND n.gepubliceerd = 1

Get banners that are displayed between the top news articles:
SELECT SQL_CACHE b.banner_id, b.naam, b.locatie, b.expanding, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners
FROM pagina p
INNER JOIN pagina_has_banner phb ON (phb.pagina_id = p.pagina_id)
INNER JOIN banner b ON (b.banner_id = phb.banner_id )
WHERE p.url = ‘index.asp’
AND ‘2007-4-24’ >= phb.datum_start
AND ‘2007-4-24’ < phb.datum_eind
ORDER BY phb.positie
LIMIT 5

Get banners that are randomly displayed in the header:
SELECT b.banner_id, b.naam, b.expanding, b.locatie, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners, phb.banner_id*0+RAND() as rnd_id
FROM pagina p
INNER JOIN pagina_has_banner phb ON (phb.pagina_id = p.pagina_id)
INNER JOIN banner b ON (b.banner_id = phb.banner_id )
WHERE p.url = ‘site_topbanners.inc.asp’
AND ‘2007-4-24’ >= phb.datum_start
AND ‘2007-4-24’ < phb.datum_eind
ORDER BY rnd_id
LIMIT 4

Get banners that are displayed on the side:
SELECT SQL_CACHE b.banner_id, b.naam, b.locatie, b.expanding, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners
FROM pagina p
INNER JOIN pagina_has_banner phb ON (phb.pagina_id = p.pagina_id)
INNER JOIN banner b ON (b.banner_id = phb.banner_id )
WHERE p.url = ‘site_side.inc.asp’
AND ‘2007-4-24’ >= phb.datum_start
AND ‘2007-4-24’ < phb.datum_eind
ORDER BY phb.positie

Get the yesterday’s top 5 most read news articles (this is also done for the past week and month)
SELECT SQL_CACHE n.nieuwsbericht_id, n.titel, n.link
FROM nieuwsbericht n
INNER JOIN nieuwsbericht_has_sector nhs ON (n.nieuwsbericht_id = nhs.nieuwsbericht_id)
INNER JOIN click c ON (n.nieuwsbericht_id = c.nieuwsbericht_id)
WHERE n.datum_public > ‘2007-4-23’
AND n.datum_public <= ‘2007-4-24’
AND nhs.sector_id < 50
AND nhs.gerelateerd = 1
AND n.gepubliceerd = 1
ORDER BY c.aantal_clicks DESC LIMIT 5

Get all the news articles that were published today (except for the top news articles) to display them per sector:
SELECT SQL_CACHE s.url AS sector, n.nieuwsbericht_id, n.datum_public, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id
FROM sector s
INNER JOIN nieuwsbericht_has_sector nhs ON (s.sector_id = nhs.sector_id)
INNER JOIN nieuwsbericht n ON (nhs.nieuwsbericht_id = n.nieuwsbericht_id)
INNER JOIN categorie c ON (n.categorie_id = c.categorie_id)
INNER JOIN datum_index d ON (n.datum_public = d.datum)
WHERE s.positie >= 0
AND nhs.gerelateerd = 1
AND n.gepubliceerd = 1
AND n.hoofd_positie IS NULL
ORDER BY s.positie ASC, n.datum_public DESC

This query is repeated for every sector in which no news articles have been published. Usually that’s only a couple of sectors. Get the latest news articles:
SELECT SQL_CACHE n.nieuwsbericht_id, n.datum_public, n.titel, n.ondertitel, LEFT(n.bericht,700) AS bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.link, c.omschrijving AS categorie, nhs.sector_id
FROM sector s
INNER JOIN nieuwsbericht_has_sector nhs ON (s.sector_id = nhs.sector_id)
INNER JOIN nieuwsbericht n ON (nhs.nieuwsbericht_id = n.nieuwsbericht_id)
INNER JOIN categorie c ON (n.categorie_id = c.categorie_id)
WHERE s.url = ‘fruit’
AND nhs.gerelateerd = 1
AND n.gepubliceerd = 1
ORDER BY n.datum_public DESC, n.tijd_public DESC
LIMIT 10

Get the banners that are to be randomly displayed with the sectors.
SELECT b.banner_id, b.naam, b.locatie, b.expanding, b.link, b.link_blank, b.hoogte, b.breedte, p.aantal_banners, phb.banner_id*0+RAND() as rnd_id
FROM pagina p
INNER JOIN pagina_has_banner phb ON (phb.pagina_id = p.pagina_id)
INNER JOIN banner b ON (b.banner_id = phb.banner_id )
WHERE p.url = ‘index.asp?sector’
AND ‘2007-4-24’ >= phb.datum_start
AND ‘2007-4-24’ < phb.datum_eind
ORDER BY rnd_id

11

What you need to do is to post the query and the output of the EXPLAIN [your query here]

Because only then we can see what MySQL is actually doing to perform your query. What indexes it chooses to use and in which order it joins the tables.

But I can already now tell you that your

…ORDER BY RAND()

Is going to give you problems when it comes to performance because you are forcing MySQL to perform the join first. And then take the entire temporary table it created for the join and sort all records before it returns your 10 or 4 records (or whatever you LIMIT your query to).

Try to think out a way to decide on only the id’s that you are interrested in and select them distinctly in your join instead.

That way you can create apropriate indexes and avoid the sorting all together.

12
[B]sterin wrote on Tue, 24 April 2007 15:30[/B]
What you need to do is to post the query and the output of the EXPLAIN [your query here]

Because only then we can see what MySQL is actually doing to perform your query. What indexes it chooses to use and in which order it joins the tables.

I’ve put them in a HTML doc and attached it to this post.

[B]sterin wrote on Tue, 24 April 2007 15:30[/B]

But I can already now tell you that your

…ORDER BY RAND()

Is going to give you problems when it comes to performance because you are forcing MySQL to perform the join first. And then take the entire temporary table it created for the join and sort all records before it returns your 10 or 4 records (or whatever you LIMIT your query to).

Try to think out a way to decide on only the id’s that you are interrested in and select them distinctly in your join instead.

That way you can create apropriate indexes and avoid the sorting all together.

I think I’ll just adjust the query so it will retrieve all the banners and then do the randomizing in the ASP code.

13

The website seems to be working fine now, so thanks a lot for your help.

I have another question, about the innodb_buffer_pool_size. Is 700 mb useful for a database that’s about 300 mb in size (and growing about 2 mb per day), or should I just set it as big as is possible for my configuration?